Pareto charts, 8
keyboard accelerators, 28
opening, 28
options, 27
paste operations, previewing, 26
right-click menu access, 28
Paste Special dialog, 27
paste special operations shortcut key, 47
pasting
borders, 27
column widths, 27
formats, 27
conditional formats, 460
new worksheets, 461
rules, 459
formulas, 27
links back to copied ranges, 27
number formatting, 27
Paste Options gallery
keyboard accelerators, 28
opening, 28
options, 27
right-click menu access, 28
Paste Special dialog, 27
PDF data, 589
pictures of original cells, 27
previewing, 26
shortcut key, 47
source formatting, keeping, 27
transposing data, 27
values, 27
PDFs
creating, 589
pasting data back to Excel, 589
PDURATION function, 227
PEARSON function, 235
Percentage icon, 429
PERCENTILE.EXC function, 235
PERCENTILE.INC function, 235
PERCENTRANK.EXC function, 236
PERCENTRANK.INC function, 236
PERMUT function, 236
PERMUTATIONA function, 236
PHI function, 236
phone numbers, formatting, 433-434
PHONETIC function, 118
photos. See pictures
PI function, 113
Picture Styles gallery, 554
Picture Tools Format tab
Artistic Effects fly-out, 554
Compress Pictures, 557
Corrections drop-down, 552
Picture Styles gallery, 554
Send Backward/Send Forward commands, 558
pictures, 537
3D Maps, adding, 515
adding, 547
from computers, 548
multiple at once, 548
aspect ratios, unlocking, 550-552
backgrounds, removing, 555-556
brightness/contrast, adjusting, 552
effects
artistic, 554
presets, 554
file size, reducing, 557
headers/footers, adding, 572-573
original cells, copying/pasting, 27
sizes, customizing, 39
SmartArt, adding, 541
pie charts, 501
Pivot Table command (Insert menu), 6
automatic daily date grouping
overriding, 335
turning off, 335
blank cells, deleting, 331-332
calculations outside the table, adding, 336-337
creating from multiple tables, 6-7
Data Model
benefits, 360
creating, 358
relationships, defining, 359
second table, adding, 359
features, 322
fields
calculations, changing, 337-339
drop zones, adding/deleting, 329
filters
check boxes, 348
clearing, 348
copying for every value in Report Filter field, 356
Date Filters fly-out, 350
Label Filters fly-out, 349-350
Timeline control, 353
inserting/moving rows/cells, 333
manual calculation mode, 332
numeric formatting, applying, 330
PowerPivot
benefits, 361
distinct values, counting, 361
Power View tool, 365
rearranging, 329
recommended, browsing, 323-324
reports, customizing with fields, 326-327
resources, 343
Show Values As
percentage of total calculation, 339
running totals/rankings, 340
slicers, 351
clearing, 353
items, selecting, 353
sorting, 356
tools, disappearing, 332
unpivoting data (Power Query), 283-284
value column blank cells, 332
pivot_table argument (GETPIVOTDATA function), 212
PivotTable Styles gallery, 342-343
PMT function, 227
POISSON.DIST function, 236
positioning page breaks, 570
columns
case conversions, 282
renaming, 281
data
sources, specifying, 280
loading, 287
refreshing, 287
first rows as headers, identifying, 281
reviewing, 286
Power View tool, 365
PowerPivot
3D Maps
layers, combining, 515
navigation, 514
benefits, 361
DAX
distinct values, counting, 361
Power View tool, 365
powers (math), 133
PPMT function, 227
precedents (cells)
direct, displaying, 94
previewing
paste operations, 26
printing
full-screen Print Preview, 566
PRICE function, 227
PRICEDISC function, 227
PRICEMAT function, 227
print areas, setting, 67
Print command (File menu), 563, 574
Print panel, displaying, 563
Print Preview full-screen, 564, 566
Print tab (File menu), displaying, 48
Printer Properties dialog, 564
printers
properties, changing, 576
selecting, 575
printing
active sheets, 575
column/row headings, 577
comments, 578
entire workbooks, 576
error values, replacing, 577
first page number, controlling, 578
gridlines, 577
headers/footers
adding, 571
automatic, adding, 571
custom, 572
different in same worksheets, 573-574
pictures/watermarks, adding, 572-573
scaling, 574
page breaks, 569
automatic versus manual, 570
deleting, 570
moving, 570
Page Setup dialog settings, 576
pages, sizing, 566
paper size, selecting, 566
previewing
full-screen Print Preview, 566
print area, ignoring, 576
printers
properties, changing, 576
selecting, 575
Quick Print icon, adding, 561-562
selections, 576
Excel options, 564
File, Print command, 563
Header & Footer Tools Design tab, 564
Page Break Preview view, 564
Page Layout tab, 563
Page Layout view, 564
Page Setup dialog, 564
Print Preview full screen, 564
Printer Properties dialog, 564
small reports, centering on pages, 577
specific pages, 576
PROB function, 236
PRODUCT, 113
Product slicer, 352
Project Explorer pane (VBE), 391
Proofing category (Excel Options dialog), 37
properties (VBA), 396
Properties pane (VBE), 391
Protect Sheet command (Review menu), 421
Protect Sheet dialog, 421
protecting
Internet files, 39
workbooks, 25
worksheets, 421
PV function, 227
Q
QAT (Quick Access Toolbar), 20
AutoAverage icon, adding, 108
AutoCount icon, adding, 108
AutoFilter icon, 309
commands
deleting, 21
Touch/Mouse Mode, 26
displaying, 20
keyboard accelerators, 43
Quick Print, adding, 561
white space around icons, adding, 26
QUARTILE.EXC function, 236
QUARTILE.INC function, 236
Quick Access Toolbar category (Excel Options dialog), 37
Quick Analysis options shortcut key, 48
Quick Print icon, adding, 561
QUOTIENT function, 113
R
RADIANS function, 241
RANDBETWEEN function, 113, 129
random numbers/data, generating functions, 128-129
RANDBETWEEN, 129
random scenarios, modeling, 370-372
Random Walk Down Wall Street, 370
range argument (functions)
COUNTIF, 134
SUMIF, 135
range_lookup argument (VLOOKUP function), 196
range operator (:), 85
cells, counting, 112
converting to tables, 358
criteria
blank, 215
creating, 215
dates/numbers as, 217
current, selecting, 56
defining as tables, 82
describing with numbers, 205-207
formulas, transposing, 273-276
names
absolute references, simplifying, 265
based on labels/headings, creating, 262-263
existing formulas, applying, 261
formulas, simplifying, 260-261
implicit intersection, 264
navigating worksheets/workbooks, 258-259
referencing, 261
valid/invalid examples, 257
values, assigning, 266
selecting before AutoSum, 107-108
text flow, formatting, 456-457
values based on, finding, 193-194
vertical/horizontal, transposing, 210-211
RANK.AVG function, 237
RANK.EQ function, 237
rankings, displaying, 340
RATE function, 227
read-only workbooks, creating, 25
recalculating formulas, 71
RECEIVED function, 227
Recent File List command (File menu), 24
recent workbooks list, clearing, 24
Recommended Pivot Table dialog, 323
recommended pivot tables, browsing, 323-324
Record Macro dialog, 382
recording macros, 382
mail merge, formatting, 383-384
recovering unsaved workbooks, 24
redo shortcut key, 47
#REF! error, 88
ref_text argument (INDIRECT function), 208
reference argument (OFFSET function), 205
reference functions
referencing
absolute, 265
cells
relative, 72
specified by text strings, returning, 207-208
previous worksheets in formulas, 269-270
ranges
names, 261
worksheet-level names, 260
refreshing
data (Power Query), 287
filters, 308
Region slicer, 352
relationships, 359
relative referencing, 72, 385-386
reliability, 11
remainders, calculating, 131-132
Remove Background icon, 555
renaming columns, 281
repeating
functions, automating. See macros
last tasks, 57
REPLACE function, 118
replace shortcut key, 48
REPLACEB function, 118
replacing text, 171
reports
Scenario Summary, creating, 375
sizing to pages, 566
small, centering on pages, 577
resizing
3D Map panes, 516
filter drop-downs, 308
distant cells, watching, 417-418
filtered
formatting/copying, 314
totaling, 314
formulas, formatting, 92
Scenario Manager, comparing, 373
store image, preparing, 519
return_type argument (functions)
WEEKDAY, 150
WEEKNUM, 151
Review menu commands, Protect Sheet, 421
reviewing Power Query, 286
commands, revealing more, 16-17
customizing
resetting back to original ribbon, 36
resources, 37
sharing, 36
third-party programs, 37
Developer tab, activating, 17
display size, 17
expanding, 20
galleries, 15
icons
selecting with keyboard accelerators, 44-45
minimizing, 20
Page Layout tab, 563
Picture Tools Format tab
Artistic Effects fly-out, 554
Compress Pictures, 557
Corrections drop-down, 552
Picture Styles gallery, 554
Send Backward/Send Forward commands, 558
resetting back to original, 36
shrinking, 20
tabs. See also specific names of tabs
contextual, activating, 18
keyboard accelerators, 44
rolling through, 16
Tell Me What You Want to Do box, 13-14
RibbonX: Customizing the Office 2007 Ribbon (Martin, Puls, Henning), 37
right-click menus, opening, 56
RIGHTB function, 118
rolling through ribbon tabs, 16
Roman numeral conversions, 111, 130
roots (math), 133
ROW function, 181
row_num argument (INDEX function), 202
rows
first as headers, identifying, 281
from-scratch macros
last with data, finding, 402
headings, printing, 577
highlighting
entire, 485
page breaks, deleting, 570
pivot tables, 333
subtotals
deleting, 302
rows argument (OFFSET function), 205
ROWS function, 181
RRI function, 227
RSQ function, 237
RTD function, 181
rules
duplicate/unique values, 479-480
options, 475
text containing values, 480-481
traditional rules, 475
conditional formatting. See conditional formats
copying/pasting formats, 459
running totals, displaying, 340
S
satellite photos (3D Maps), adding, 516
Save As command (File menu), 24
Save category (Excel Options dialog), 37
Save command (File menu), 23
Save File in This Format option, 40
saving
shortcut key, 47
themes, 453
scaling headers/footers, 574
Scenario Values dialog, 374
scenarios
multiple, adding, 375
results, comparing, 373
Scenario Manager dialog options, 373
setting up, 374
summary report, creating, 375
scientific notation, displaying, 438
screen clippings, adding, 557-558
Screenshot command (Insert menu), 557
SEARCHB function, 118
SEC function, 241
SECH function, 241
second argument (TIME function), 146
security
protected mode, 39
trusted document settings, 39-40
trusted locations, customizing, 41
worksheets, protecting, 421
Security dialog, 382
select all shortcut key, 47
Select Case flow control, 405
selecting
current ranges, 56
Filter drop-down items, 305-307
invoice register macro
filename to save, 400
opening file, 400
paper sizes, 566
printers, 575
ranges before AutoSum, 107-108
recommended charts, 492
themes, 451
colors, 452
fonts, 452
Selection pane, 558
selections
extending, 51
next corner, jumping to, 56
Send Backward/Send Forward commands (Picture Tools Format tab), 558
separating text based on delimiters, 421-422
serial_num argument (WEEKNUM function), 151
serial_number argument (WEEKDAY function), 150
SERIESSUM function, 241
conditional formatting rules with formulas, 482
first page numbers for printing, 578
icon sets, 471
printer properties, 576
Excel Options, 564
File, Print command, 563
Header & Footer Tools Design tab, 564
Page Break Preview view, 564
Page Layout tab, 563
Page Layout view, 564
Page Setup dialog, 564
Print Preview full screen, 564
Printer Properties dialog, 564
scenarios, 374
shapes, 537
cell values, displaying, 543
formatting, 545
Share command (File menu), 24
sharing
ribbon customizations, 36
themes, 453
web workbooks, 586
workbooks, 24
SHEET function, 179
SHEETS function, 179
shortcut keys
cells
striking through, 56
current ranges, selecting, 56
data
data set last row, jumping to, 56
date/time stamps, 57
displaying with ToolTips, 57
dollar signs, adding, 57
last task, repeating, 57
legacy commands (menus)
Insert, 62
Window, 66
moving between worksheets, 56
next corner of selections, jumping to, 56
print areas, setting, 67
ranges, defining as tables, 82
right-click menus, opening, 56
selections, extending, 51
slicer items, selecting, 57
Show the Start Screen When This Application Starts command (File menu), 10
Show Values As drop-down
percentage of total, displaying, 339
running totals/rankings, 340
shrinking, ribbon, 20
side by side workbook comparisons, 418-419
SIGN function, 113
simple variables, adding to macros, 397
SIN function, 241
Single Document Interface, 10
SINH function, 241
sizing
3D Maps
columns, 516
panes, 516
filter drop-downs, resizing, 308
headers/footers, 574
images, customizing, 39
paper sizes, selecting, 566
reports for printing, 566
ribbon, 17
SKEW function, 237
SKEW.P function, 237
Slicer Settings dialog, 352
slicers, 351
clearing, 353
touchable, 12
SLN function, 227
SLOPE function, 237
slow motion formulas, 96-97, 419
SMALL function, 237
small reports, centering on pages, 577
SmartArt, 537
common elements, 539
hierarchical, 542
pictures, adding, 541
styles
listing of, 538
social networking accounts, connecting, 24
social security numbers, formatting, 433-434
Solver, 377
installing, 377
premium version, 380
website, 377
Sort dialog
colors, 290
icons, 290
left-to-right, 291
pivot tables, 356
unique sequences with custom lists, 291-292
sorting
collapsed subtotal view, 298-299
color, 290
icons, 290
left-to-right, 291
one-click, 292
troubleshooting, 293
unique sequences with custom lists, 291-292
pivot tables, 356
source formatting, keeping, 27
spacing custom number formats, controlling, 436
Sparkline Tools Design tab, 528-529
sparklines
axis values, controlling, 529-531
data, mapping
not adjacent to original data sets, creating, 524
source data/target ranges, 525
square data sets, 525
missing data, 535
types, 523
Split by Delimiter dialog, 281
splitting
SQRTPI function, 113
square brackets ([ ]), 141
square roots, 133
STANDARDIZE function, 237
start_date argument (functions)
DATEDIF, 151
DAYS, 154
EOMONTH, 154
NETWORKDAYS, 155
start_num argument (functions)
FIND, 170
MID, 166
SEARCH( ), 171
start screen, dismissing permanently, 10
statistical functions
AVEDEV, 228
AVERAGE, 228
AVERAGEA, 228
BETA.DIST, 229
BETA.INV, 229
BINOM.DIST, 229
BINOM.DIST.RANGE, 229
BINOM.INV, 229
CHISQ.DIST, 229
CHISQ.DIST.RT, 229
CHISQ.INV, 230
CHISQ.INV.RT, 230
CHISQ.TEST, 230
CONFIDENCE.NORM, 230
CONFIDENCE.T, 230
CORREL, 230
COVARIANCE.P, 230
COVARIANCE.S, 230
DEVSQ, 230
EXPON.DIST, 231
F.DIST, 231
F.DIST.RT, 231
F.INV, 231
F.INV.RT, 231
F.TEST, 231
FISHER, 231
FISHERINV, 231
FORECAST, 231
FORECAST.ETS, 232
FORECAST.ETS.CONFINT, 232
FORECAST.ETS.SEASONALITY, 232
FORECAST.ETS.STAT, 232
FORECAST.LINEAR, 232
FREQUENCY, 232
GAMMA, 232
GAMMA.DIST, 232
GAMMA.INV, 232
GAMMALN, 232
GAUSS, 232
GEOMEAN, 233
GROWTH, 233
HARMEAN, 233
HYPGEOM.DIST, 233
INTERCEPT, 233
KURT, 233
LARGE, 233
LINEST, 234
listing of, 228
LOGEST, 234
LOGNORM.DIST, 234
LOGNORM.INV, 234
MAX, 234
MAXA, 234
MEDIAN, 234
MIN, 234
MINA, 234
MODE.MULT, 234
MODE.SNGL, 235
NEGBINOM.DIST, 235
NORM.DIST, 235
NORM.INV, 235
NORM.S.DIST, 235
NORM.S.INV, 235
PEARSON, 235
PERCENTILE.EXC, 235
PERCENTILE.INC, 235
PERCENTRANK.EXC, 236
PERCENTRANK.INC, 236
PERMUT, 236
PERMUTATIONA, 236
PHI, 236
POISSON.DIST, 236
PROB, 236
QUARTILE.EXC, 236
QUARTILE.INC, 236
RANK.AVG, 237
RANK.EQ, 237
RSQ, 237
SKEW, 237
SKEW.P, 237
SLOPE, 237
SMALL, 237
STANDARDIZE, 237
STDEV.P, 238
STDEV.S, 238
STDEVA, 238
STDEVPA, 238
STEYX, 238
SUMSQ, 238
SUMX2MY2, 238
SUMX2PY2, 238
SUMXMY2, 238
T.DIST, 238
T.DIST.2T, 238
T.DIST.RT, 238
T.INV, 239
T.INV.2T, 239
T.TEST, 239
TREND, 239
TRIMMEAN, 239
VAR.P, 239
VAR.S, 239
VARA, 239
VARPA, 239
WEIBULL.DIST, 239
Z.TEST, 239
status bar
customizing, 31
numbers, adding, 31
STDEVA function, 238
STDEV.P function, 238
STDEVPA function, 238
STDEV.S function, 238
STEYX function, 238
storing
files, 24
strikethrough style, 487
styles
border, 487
charts, displaying, 493
font color, 487
icon sets, 487
numbers, formatting, 487
SmartArt
listing of, 538
strikethrough, 487
underline, 487
subscription model (Office 365), 10
SUBTOTAL function, 114
multiple levels of totals, 126-127
visible cells, totaling, 127
subtotals
adding, automatically, 296-297
collapsed view, sorting, 298-299
deleting, 302
summary page, displaying, 298
Subtotals command
adding subtotals automatically, 296-297
deleting subtotals, 302
Group and Outline button
collapsed view, sorting, 298-299
copying subtotal rows, 300-301
formatting subtotal rows, 301-302
subtraction operator (-), 85
SUM function, 111
sum_range argument (SUMIF function), 135
SUMSQ function, 238
SUMX2MY2 function, 238
SUMX2PY2 function, 238
SUMXMY2 function, 238
sunburst charts, 8
survey data, collecting, 586-588
SYD function, 227
Symbol dialog, 420
Symbol icon, 420
symbols, adding, 420
synchronous scrolling, 418-419
T
table_array argument (VLOOKUP function), 196
tables
data
random scenarios, modeling, 370-372
data sets, formatting, 6
multiple, joining with Data Model
benefits, 360
data, preparing, 358
pivot table, creating, 358
relationships, defining, 359
second table, adding, 359
pivot. See pivot tables
tabs
Background Removal, 555
contextual, activating, 18
Data, Get & Transform. See Power Query
Developer, activating, 17
Equation Tools Design, 420
File menu, 48
Font (Format Cells dialog), 48
Formulas, 100
Header & Footer Tools Design
automatic, adding, 571
custom headers/footers, 572
print settings, 564
scaling, 574
keyboard accelerators, 44
Page Layout
page breaks, 569
print settings, 563
Picture Tools Format
Artistic Effects fly-out, 554
Compress Pictures, 557
Corrections drop-down, 552
Picture Styles gallery, 554
Send Backward/Send Forward commands, 558
rolling through, 16
Sparkline Tools Design, 528-529
TAN function, 241
TANH function, 241
target sales price formula, 71-72
tasks
crossing off, 56
last, repeating, 57
panes, 17
TBILLEQ function, 227
TBILLPRICE function, 227
TBILLYIELD function, 228
T.DIST function, 238
T.DIST.2T function, 238
T.DIST.RT function, 238
Tell Me What You Want to Do box (ribbon), 13-14
templates, 502
testing
cell values, 189
text case, 173
text
any character, generating, 162-164
case
testing, 173
characters
number of, calculating, 167
replacing, 171
custom number formats, controlling, 436
date conversions, 147
filters, 312
flow in ranges, formatting, 456-457
fonts
bold/italic/underline, 440-441
size, formatting, 439
typefaces, changing, 440
formatting
mini toolbar, 29
functions
ASC, 116
BAHTTEXT, 116
CLEAN, 117
DOLLAR, 117
FINDB, 117
FIXED, 117
JIS, 117
LEFTB, 117
LENB, 117
listing of, 116
MIDB, 118
NUMBERVALUE, 118
PHONETIC, 118
REPLACE, 118
REPLACEB, 118
RIGHTB, 118
SEARCHB, 118
YEN, 119
leading/trailing spaces, deleting, 160-162
numbers, formatting as, 173-175
separating based on delimiters, 421-422
specified values formatting rule, 480-481
symbols, adding, 420
wrapping in cells, 455
text argument (functions)
LEFT, 165
LEN, 167
LOWER, 159
MID, 166
PROPER, 160
REPT, 172
RIGHT, 166
SUBSTITUTE, 171
UPPER, 160
Text That Contains rule, 480-481
text1 argument (EXACT function), 173
text2 argument (EXACT function), 173
applying, 453
colorful, 5
components, 451
creating, 452
colors, 452
fonts, 452
effects, 452
Office, 450
saving, 453
selecting, 451
sharing, 453
third-party ribbon customization programs, 37
thousands separators, 431
three-icon sets, 470
Timeline control, 353
times
calculating, 146
converting text times to real times, 148-149
custom number formats, 437-438
functions
DAYS, 154
listing of, 114
negative, 41
stamps, 57
TIMEVALUE function, 115, 148-149
T.INV function, 239
T.INV.2T function, 239
toolbars
formula bar, 30
mini, 29
Quick Access. See QAT
tools
Insert Equation, 11
pivot table, disappearing, 332
Power View, 365
multiple scenarios, adding, 375
results, comparing, 373
Scenario Manager dialog options, 373
scenarios, setting up, 374
summary report, creating, 375
Solver, 377
installing, 377
premium version, 380
website, 377
random scenarios, modeling, 370-372
two-variable tables, creating, 368-370
Tools menu, legacy commands, 63-64
ToolTips
Excel Options dialog, 38
Format Painter, 15
functions, 103
shortcut keys, displaying, 57
Top 10 Filter dialog, 350
top/bottom rules (conditional formatting), 474-475
totaling visible cells, 127
touch mode
slicers, 12
white space around icons, adding, 26
Touch/Mouse Mode command (QAT), 26
tours (3D Maps), 518
tracing
dependents, 96
trailing spaces, deleting, 160-162
transforming data
Flash Fill, 288
coaching, 288
dates, 289
mathematical transformation, 288
troubleshooting, 289
Power Query. See Power Query
TRANSPOSE function, 181, 210-211
transposing
data, 27
TreeMaps, 8
TREND function, 239
trigonometry functions, 240
TRIMMEAN function, 239
troubleshooting
AutoSum errors in macros, 399
disappearing pivot table tools, 332
Flash Fill, 289
formulas
direct precedent cells, displaying, 94
errors, 88
partial evaluations, 97
slow motion evaluations, 96-97
tracing dependents, 96
Watch Window screen, 96
sorting data, 293
TRUE function, 178
TRUNC function, 114
Trust Center, 37
trusted documents settings, 39-40
trusted document settings, 39-40
trusted locations, customizing, 41
T.TEST function, 239
Tufte, Edward, 523
Tukey, John, 499
turning on/off
automatic daily date grouping, 335
filter drop-downs, 304
two-variable what-if tables, creating, 368-370
TYPE function, 179
U
unary minus operator (-), 85-86
underline shortcut key, 48
underline style, 487
undo shortcut key, 47
UNICODE function, 119, 164-165
union operator (,), 85
unique values, identifying, 479-480
unit argument (DATEDIF function), 151
unit values (DATEDIF function), 151
unpivoting data (Power Query), 283-284
unsaved workbooks
recovering, 24
worksheet links, creating, 252
Update Links dialog, 254
V
valid names, 257
value argument (TEXT function), 174
#VALUE! error, 88
Value Field Settings dialog, 330, 339
Value Filters fly-out, 346-347
value1 argument (LOOKUP function), 192
value2 argument (LOOKUP function), 192
values
above/below average, formatting, 481
based on ranges, finding, 193-194
blank cells in value columns, 332
cells
shapes, displaying, 543
testing, 189
copying/pasting, 27
distinct, counting, 361
duplicate, identifying, 479-480
errors, replacing when printing, 577
formulas, compared, 70
honing in on with Goal Seek, 376-377
matching position, finding, 198-199
names, assigning, 266
pivot tables, displaying
percentage of total, 339
running totals/rankings, 340
scenarios, adding, 374
sparkline axis, controlling, 529-531
unique
zero, displaying, 40
VARA function, 239
variables (macros)
object, 397
simple, 397
VAR.P function, 239
VARPA function, 239
VAR.S function, 239
VBA (Visual Basic for Applications), 392
collections, 393
Help, 395
macros. See macros
methods, 393
objects, 393
properties, 396
recorded code, analyzing, 396-397
variables
object, 397
simple, 397
visible cells only, selecting, 414-415
macros, editing, 391
referencing previous worksheets in formulas, 269-270
VDB function, 228
vertical lookup. See VLOOKUP function
vertical ranges, transposing, 210-211
videos (3D Maps), 518
View menu commands
Window, View Side by Side, 418
views. See also displaying
Backstage
closing, 24
opening, 23
collapsed subtotal, sorting, 298-299
Page Layout, 32
Normal view, compared, 576-577
print settings, 564
Power View tool, 365
Print Preview full-screen, 564, 566
switching between, 32
VisiCalc, 398
Visual Basic Editor. See VBE
Visual Basic for Applications. See VBA
visualization features, 463-464
data bars
creating, 465
examples, 464
multiple colors, 490
pivot tables, creating, 489-490
subsets of cells, displaying, 468
mixing icons, 473
setting up, 471
VLOOKUP function, 181, 193-197
arguments, 196
simplifying with names, 265
values based on ranges, finding, 193-194
VP of Engineering org charts, 542
VP of Manufacturing org charts, 542
VP of Sales org charts, 542
W
warnings, trusted document, 39-40
Watch Window dialog, 417
Watch Window screen, 96
Web functions, 223
web workbooks
sharing, 586
WEBSERVICE function, 223
websites
Able2Extract review, 589
Axel font family, 452
Excel MVP Andy Pope, 37
WEEKDAY function, 116, 149-150
WEIBULL.DIST function, 239
random scenarios, modeling, 370-372
two-variable tables, creating, 368-370
white space (icons), adding, 26
widget production model (Solver), 377-380
width argument (OFFSET function), 205
widths (columns)
copying pasting, 27
win/loss charts, 523
Window, View Side by Side command (View menu), 418
Window menu legacy commands, 66
Windows Ctrl shortcut keys, 47-48
within_text argument (functions)
FIND, 170
SEARCH, 171
workbooks
closed, linked workbooks, 253
closing, 24
comparing side by side, 418-419
displaying two on two monitors, 10
external, linking to worksheets, 251
information, displaying, 23-25
marking as final, 25
missing linked, 253
new, creating, 23
number of recent visible, setting, 40
OneDrive
accessing, 580
PDFs
creating, 589
pasting data back to Excel, 589
printing, 576
recent workbooks list, clearing, 24
sharing, 24
unsaved
links, 252
recovering, 24
web
sharing, 586
windows, closing, 48
WORKDAY function, 116, 154-156
WORKDAY.INTL function, 116, 156-157
workdays, calculating, 154-157
worksheets
active, printing, 575
displaying side by side, 245-247
formatting
bold/italic/underline, 440-441
copying formats across worksheets, 461
copying/pasting formats, 459-460
currency, 432
dates/times, 432
font size, 439
font typefaces, 440
fractions, 433
individual characters, 453-454
numbers. See formatting, numbers
pasting conditional formats, 460
themes. See themes
traditional icons, 427
wrapping text in cells, 455
zip codes/phone numbers/social security numbers, 433-434
formulas, documenting, 204-205
closed workbooks, 253
creating with Alternate Drag-and-Drop menu, 249
creating with formulas, 251
creating with Links tab, 252-253
creating with mouse, 250
creating with Paste Options menu, 246-248
external workbooks, 251
missing workbooks, 253
unsaved workbooks, 252
Update Links dialog, suppressing, 254
names
benefits, 259
defining, 259
referencing, 260
returning, 191
navigating
icons, 29
moving between, 56
PDFs
creating, 589
pasting data back to Excel, 589
protecting, 421
referencing previous in formulas, 269-270
zooming in/out, 31
wrapping text (cells), 455
X
XIRR function, 228
XNPV function, 228
XOR function, 178
Y
y date format, 139
year argument (DATE function), 145
Year slicer, 353
YEARFRAC function, 116
YEN function, 119
YIELD function, 228
YIELDDISC function, 228
YIELDMAT function, 228
yy date format, 139
yyy date format, 139
yyyy date format, 139
Z
zero values, displaying, 40
zip codes, formatting, 433-434
Zoom dialog, 31
Zoom In/Out buttons, 31
zooming in/out
worksheets, 31
Z.TEST function, 239