Index
Symbols
###### result, 88
+ (addition operator), 85
& (concatenation operator), 85, 90-85, 158
/ (division/fraction) operator, 85
$ (dollar signs)
adding, 57
after entering formulas, 76
functionality, 69
= (equal to operator), 85
^ (exponents operator), 85
> (greater than operator), 85
>= (greater than or equal to operator), 85
<space> (intersection operator), 85, 263-264
< (less than operator), 85
<= (less than or equal to operator), 85
* (multiplication operator), 85
<> (not equal to operator), 85
() (overriding order of operations operator), 85
( ) (parentheses)
: (range operator), 85
[ ] (square brackets), 141
- (subtraction operator), 85
- (unary minus operator), 85-86
, (union operator), 85
3D Map command (Insert menu), 512
3D Maps, 8
animating, 508
category colors, 505
columns
colors, 516
size, 516
corn acreage by state example, 503
custom retail store example, 519-521
custom maps, creating, 519-521
store image, preparing, 519
geography fields, 512
population, 514
layers, combining, 515
lines between points, animating, 517
navigation, 514
panes, resizing, 516
photos, adding to points, 515
satellite photos, adding, 516
tours, creating, 518
whole earth, displaying, 516
1904 date system, 41
A
a1 argument (INDIRECT function), 208
Able2Extract review website, 589
absolute references, 72-73, 265
absolute value function, 111
Access Key mode (Excel 2003), 59
Account command (File menu), 24
ACCRINT function, 224
ACCRINTM function, 224
ACOS function, 240
ACOSH function, 240
ACOT function, 240
ACOTH function, 240
activating
contextual ribbon tabs, 18
Developer tab, 17
active worksheets, printing, 575
Add Scenario dialog, 374
Add Watch dialog, 96
adding
3D Maps
color, 505
photos, 515
satellite photos, 516
AutoAverage function icon, 108
AutoCount function icon, 108
Data Model data, 6
dollar signs ($), 57
formula comments, 190
headers/footers, 571
automatically, 571
custom, 572
interface color, 5
macro simple variables, 397
multiple scenarios, 375
object variables to macros, 397
page breaks
automatic versus manual, 570
pictures, 547
from computers, 548
multiple at once, 548
SmartArt, 541
pivot tables
rows, 333
Quick Print icon, 561
ribbon
status bar numbers, 31
symbols, 420
text boxes, 546
white space around icons, 26
WordArt, 545
Add-Ins category (Excel Options dialog), 37
addition operator (+), 85
ADDRESS function, 180
Advanced category (Excel Options dialog), 37
advanced filter
applying, 315
Advanced Filter command, 314
Advanced Filter dialog, 314-315
AGGREGATE function, 111, 121-124
arguments
listing of, 121
options, 121
COUNT versus COUNTA, 124
filtered data set visible items calculations, 123
syntax, 121
Alternate Drag-and-Drop menu, 249
AMORDEGRC function, 224
AMORLINC function, 224
ampersand operator (&), 85, 90-91, 158
formulas
partial, 97
recorded code with VBA, 396-397
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
answers, finding. See analysis
Apply Names dialog, 261
Arabic numerals, converting to Roman, 113
AREAS function, 180
arguments (functions)
AGGREGATE
listing of, 121
options, 121
COUNTIF, 134
DATEDIF, 151
DAYS, 154
entering, 100
EOMONTH, 154
EXACT, 173
FIND, 170
HYPERLINK, 209
INDEX, 202
INDIRECT, 208
LOOKUP, 192
MATCH, 198
MID, 166
OFFSET, 205
REPT, 172
RIGHT, 166
SUBSTITUTE, 171
SUMIF, 135
TIME, 146
ToolTips, 103
VLOOKUP, 196
WEEKNUM, 151
array argument (INDEX function), 202
arrow key formula entry method, 78-80
artistic effects (pictures), 554
Artistic Effects fly-out, 554
ASC function, 116
ASIN function, 240
ASINH function, 240
ATAN function, 240
ATAN2 function, 240
ATANH function, 240
auditing
formulas
partial evaluations, 97
slow motion evaluations, 96-97
tracing dependents, 96
Watch Window screen, 96
AutoAverage function icon, adding, 108
autocompleting functions, 101
autocorrect options, 42
AutoCount function icon, adding, 108
AutoFilter
filtering by selection, 309
automatic
daily date grouping
overriding, 335
turning off, 335
formula results, formatting, 92
headers/footers, 571
page breaks, 570
repetitive functions. See macros
AutoSum function, 105
errors, fixing, 399
icon, 105
AVEDEV function, 228
AVERAGE function, 228
AVERAGEA function, 228
AVERAGEIF function, 135
AVERAGEIFS function, 136
averaging with conditions, 135
Axel font family, 452
axis values (sparklines), controlling, 529-531
AZ buttons, 292
B
Background Removal tab, 555
Backstage view
closing, 24
opening, 23
BAHTTEXT function, 116
bar chart. See data bars
bar of pie charts, 501
BASE function, 242
Benninga, Professor Simon, 371
BESSELI function, 242
BESSELJ function, 242
BESSELK function, 242
BESSELY function, 242
BETA.DIST function, 229
BETA.INV function, 229
BIN2DEC function, 242
BIN2HEX function, 242
BIN2OCT function, 242
BINOM.DIST function, 229
BINOM.DIST.RANGE function, 229
BINOM.INV function, 229
BITAND function, 242
BITLSHIFT function, 242
BITOR function, 242
BITRSHIFT function, 242
BITXOR function, 242
blank pivot tables, creating, 324-325
bold shortcut key, 48
border style, 487
borders
copying/pasting, 27
box and whisker charts, 8, 499
brightness (pictures), 552
C
calculating
case
columns, converting, 282
testing, 173
CEILING MATH function, 112
cells
comments
printing, 578
counting, 112
data from past 30 days, finding, 484
databases, returning, 219
fill style, 487
formatting
based on content, 481
styles, 487
formulas
color, adding, 94
direct precedents, displaying, 94
tracing dependents, 96
watching, 96
headings
names based on, creating, 262-263
printing, 577
highlighting
duplicate/unique values, 479-480
Highlight Cell Rules menu, 475-478
options, 475
text containing values, 480-481
traditional rules, 475
in-cell bar charts. See data bars
individual characters, formatting, 453-454
information, returning, 191
names
absolute references, simplifying, 265
based on labels/headings, creating, 262-263
creating, 256
implicit intersection, 264
navigating worksheets/workbooks, 258-259
valid/invalid examples, 257
values, assigning, 266
pivot tables
moving/inserting, 333
value column blanks, 332
ranges. See ranges
referencing
relative, 72
specified by text strings, returning, 207-208
strikethrough, applying, 56
text
number of characters, calculating, 167
wrapping, 455
within three days of today, finding, 482
values
shapes, displaying, 543
testing, 189
zero, displaying, 40
visible
totaling, 127
CFO org charts, 542
Change Chart Type dialog, 501
Change Map Type dialog, 519
combo, 495
data bars. See data bars
existing pie charts to bar of pie charts, changing, 501
extraneous data, deleting, 494
hierarchy, 496
line, 523
missing data, not plotting, 190-191
new types, 8
organization, 542
Pareto, 8
pie, 501
positioning, 493
recommended, selecting, 492
settings, customizing, 494
sparklines
axis values, controlling, 529-531
missing data, 535
not adjacent to original data sets, creating, 524
source data/target ranges, 525
square data sets, 525
types, 523
styles, displaying, 493
sunburst, 8
templates, 502
TreeMaps, 8
check box filters (pivot tables), 348
CHISQ.DIST function, 229
CHISQ.DIST.RT function, 229
CHISQ.INV function, 230
CHISQ.INV.RT function, 230
CHISQ.TEST function, 230
Choose a SmartArt Graphic dialog, 539
CLEAN function, 117
cleaning data. See Flash Fill
clearing. See also deleting
conditional formats, 487
slicers, 353
Clipboard, copying/pasting
borders, 27
column widths, 27
formats, 27
formulas, 27
links back to copied ranges, 27
number formatting, 27
Paste Options gallery options, 27
Paste Special dialog, 27
pictures of original cells, 27
previewing, 26
source formatting, keeping, 27
transposing data, 27
values, 27
Close command (File menu), 24
closing
Backstage view, 24
workbooks, 24
linking, 253
windows shortcut key, 48
cloud
OneDrive, 11
accessing workbooks, 580
survey data, collecting, 586-588
reliability, 11
Code pane (VBE), 391
col_index_num argument (VLOOKUP function), 196
collapsed subtotal view, sorting, 298-299
collections (VBA), 393
colors
3D maps, 505
color scale style, 487
columns, 516
custom number formats, 437
data bars, 490
filtering, 310
formula cells, 94
gridlines, customizing, 41
interface, 5
sorting data, 290
cols argument (OFFSET function), 205
COLUMN function, 180
column_num argument (INDEX function), 202
3D Maps
colors, 516
sizing, 516
blank cells, 332
case conversions, 282
date, customizing, 41
filters, identifying, 307
headings, printing, 577
page breaks, deleting, 570
Power Query
renaming, 281
widths
copying/pasting, 27
COLUMNS function, 180
COMBIN function, 112
COMBINA function, 112
customer report example, 415
visible cells only, selecting, 414-415
combining
filters, 307
combo charts, 495
Comma icon, 429
commands
Data, Data Tools, Convert Text to Column, 421
Edit menu, legacy shortcut keys, 60-61
File menu
Account, 24
Close, 24
Export, 24
Export, Create PDF/XPS, 589
Info, 23
Info, Check for Issues, Inspect Document, 26
legacy command shortcut keys, 59-67
New, 23
Open, 23
Options, General Show the Start Screen When This Application Starts, 10
Recent File List, 24
Save, 23
Save As, 24
Share, 24
Formulas menu, Evaluate Formula, 419
Home menu, Format as Table, 6
in, 23
Insert menu
3D Map, 512
Equations, Ink Equation, 11
Online Pictures, 548
Pivot Table, 6
Screenshot, 557
legacy shortcut keys
Insert menu, 62
print area, setting, 67
Window menu, 66
out, 23
QAT
deleting, 21
Touch/Mouse Mode, 26
Review menu, Protect Sheet, 421
ribbon
View menu
legacy command shortcut key, 61-62
Window, View Side by Side, 418
comments
formulas, adding, 190
printing, 578
common Windows Ctrl shortcut keys, 47-48
compact layout (pivot tables), 328-329
comparing workbooks side by side, 418-419
COMPLEX function, 242
Compress Pictures dialog, 557
CONCATENATE function, 117, 158
concatenation operator (&), 85, 90-91, 158
cells based on content, 481
clearing, 487
copying, 488
formulas
cells within three days of today, finding, 482
data from past 30 days, finding, 484
data from specific days of the week, highlighting, 485
entire rows, highlighting, 485
every other row, highlighting, 485-486
Highlight Cells Rules, 475-478
options, 475
traditional, 475
multiple, 486
pasting, 460
quick formatting, 481
values above/below average, 481
conditions
checking if met, 184
custom number formats, 437
CONFIDENCE.NORM function, 230
CONFIDENCE.T function, 230
configuring. See setting up
connecting worksheets, 245
Alternate Drag-and-Drop menu, 249
closed workbooks, 253
external workbooks, 251
formulas, 251
missing linked workbooks, 253
mouse, 250
unsaved workbooks, 252
Update Links dialog, suppressing, 254
Consolidate dialog, 318
contextual ribbon tabs, activating, 18
contrast (pictures), 552
CONVERT function, 242
Convert Text to Columns Wizard dialog, 421
copying
borders, 27
column widths, 27
conditional formats, 488
filtered results, 314
new worksheets, 461
pasting conditional, 460
rules, 459
cutting formulas, compared, 91-92
double-clicking fill handles, 82
dragging fill handles, 81
relative referencing, 72
links back to copied ranges, 27
number formatting, 27
Paste Options gallery options, 27
Paste Special dialog, 27
pictures of original cells, 27
pivot tables for every value in Report Filter field, 356
shortcut key, 47
source formatting, keeping, 27
transposing data, 27
values, 27
Corrections drop-down (Picture Tools Format tab), 552
CORREL function, 230
COS function, 240
COSH function, 240
COT function, 240
COTH function, 240
COUNT function, 124
COUNTA function, 124
COUNTIF function, 112, 133-134
COUNTIFS function, 136
COUPDAYBS function, 224
COUPDAYS function, 224
COUPDAYSNC function, 224
COUPNCD function, 224
COUPNUM function, 224
COUPPCD function, 224
COVARIANCE function, 230
COVARIANCE.S function, 230
Create Chart dialog, 502
Create Pivot Table dialog, 324, 358
Create Relationship dialog, 359
Create Sparklines dialog, 526
Create Table dialog, 82
criteria argument (functions)
COUNTIF, 134
DSUM, 215
SUMIF, 135
criteria ranges
blank, 215
creating, 215
dates/numbers as, 217
CSC function, 240
CSCH function, 240
Ctrl+Enter keyboard shortcut, 80-81
Ctrl+T keyboard shortcut, 82
CUMIPMT function, 224
CUMPRINC function, 225
currency
decimal points, 41
formatting, 432
Currency icon, 429
current date/time, calculating, 142-143
Custom Lists dialog, 292
Custom Map Options dialog, 520
customer report combination macro, 409-411
macro, 415
visible cells only, selecting, 414-415
Customize Ribbon category (Excel Options dialog), 37
customizing
autocorrect options, 42
automatic inserts of decimal points, 41
categories, 37
chart settings, 494
date columns, 41
default file locations, 40
filters, 313
green triangle indicators, 41
gridline colors, 41
headers/footers, 572
image sizes, 39
interface elements, 40
lists, 40
negative time, 41
new features, 38
number of recent workbooks visible, 40
opening options, 37
pivot tables
rearranging, 329
printer properties, 576
protected mode, 39
ribbon
resetting back to original ribbon, 36
resources, 37
sharing, 36
third-party programs, 37
Save File in This Format, 40
status bar, 31
ToolTips, 38
trusted document settings, 39-40
trusted locations, 41
zero values, 40
cutting
shortcut key, 47
D
d date format, 139
dashboards, 365
data
geography fields, 512
population, 514
analyzing. See analysis
charts
deleting, 494
cleaning (Flash Fill), 288
coaching, 288
dates, 289
mathematical transformations, 288
troubleshooting, 289
Data Model
adding, 6
preparations, 358
graphing. See charts
PDF, pasting, 589
pivoting on 3-D maps. See 3D Maps
Power Query
loading, 287
refreshing, 287
reviewing, 286
source, specifying, 280
selecting with shortcut keys, 49-50
color, 290
icons, 290
left-to-right, 291
one-click, 292
troubleshooting, 293
unique sequences with custom lists, 291-292
sparklines, mapping
not adjacent to original data sets, 524
source data/target ranges, 525
square data sets, 525
specific days of the week, highlighting, 485
random scenarios, modeling, 370-372
Data Analysis Expression. See DAX
data bars, 487
creating, 465
examples, 464
multiple colors, 490
pivot tables, creating, 489-490
subsets of cells, displaying, 468
data entry shortcut keys, 51-55
Data menu commands
Data Tools, Convert Text to Column, 421
Data Model (pivot tables), 358
benefits, 360
data
adding, 6
preparing, 358
multiple tables, creating, 6-7
relationships, defining, 359
second table, adding, 359
data sets
formatting as tables, 6
last row, jumping to, 56
pivot table requirements, 322-323
Data tab Get & Transform. See Power Query
Data Table dialog, 219
Data Tools, Convert Text to Column command (Data menu), 421
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
database argument (DSUM function), 215
databases
functions
listing of, 182
records, summing. See DSUM function
single cells, returning, 219
subtotals
collapsed view, sorting, 298-299
deleting, 302
summary page, displaying, 298
data_field argument (GETPIVOTDATA function), 212
date columns, customizing, 41
Date Filters fly-out, 347, 350
DATEDIF function, 114, 151-153
arguments, 151
unit values, 151
dates
automatic grouping
overriding, 335
turning off, 335
comparing with conditional formatting, 478-479
converting text dates to serial numbers, 147
custom number formats, 437-438
database criteria, 217
end of month, calculating, 154
Flash Fill, 289
functions
DATEDIF, 114
DAYS, 114
DAYS360, 114
EDATE, 115
listing of, 114
NETWORKDAYS.INTL, 115, 156-157
YEARFRAC, 116
grouping
international workdays, calculating, 156-157
pivot table filters, 350
problems, 138
stamps, 57
workdays, calculating, 154-157
DAVERAGE function, 182
DAX (Data Analysis Expression)
distinct values, counting, 361
day argument (DATE function), 145
DAYS360 function, 114
DB function, 225
DCOUNT function, 182
DCOUNTA function, 182
dd date format, 139
DDB function, 225
ddd date format, 139
dddd date format, 139
DEC2BIN function, 242
DEC2HEX function, 242
DEC2OCT function, 242
DECIMAL function, 243
decimal places (custom number format), 436
decimal points, 41
Decrease Decimal icon, 429
default fonts, changing, 454-455
DEGREES function, 240
chart data, 494
leading/trailing spaces, 160-162
page breaks, 570
QAT commands, 21
recent workbooks list, 24
subtotals, 302
delimiters (text), separating, 421-422
DELTA function, 243
Developer tab, 17
DEVSQ function, 230
dialogs
Add Scenario, 374
Add Watch, 96
Apply names, 261
Change Chart Type, 501
Change Map Type, 519
Choose a SmartArt Graphic, 539
Compress Pictures, 557
Consolidate, 318
Convert Text to Columns Wizard, 421
Create Chart, 502
Create Relationship, 359
Create Sparklines, 526
Create Table, 82
Custom Lists, 292
Custom Map Options, 520
Data Table, 219
Edit Formatting Rule, 466
Excel Options
autocorrect options, 42
automatic inserts of decimal points, 41
categories, 37
custom lists, 40
date columns, 41
default file locations, 40
green triangle indicators, 41
gridline colors, 41
image sizes, 39
interface elements, hiding, 40
negative time, 41
new features, 38
number of recent workbooks visible, 40
opening, 37
print settings, 564
protected mode, 39
QAT, customizing, 22
Save File in This Format, 40
ToolTips, 38
trusted document settings, 39-40
trusted locations, 41
zero values, 40
custom number formats, 141
Font tab, 48
numbers, 431
Format Comment, 458
Format Shape, 545
Function Arguments, 103
Hidden and Empty Cell Settings, 535
Insert Chart, 492
Insert Function, 102
Insert Pictures, 548
Insert Shapes, 545
Label Filter, 349
launchers, 16
legacy keyboard accelerators, 46
Merge Scenarios, 375
More Sort Options, 356
New Formatting Rule
formatting cells based on content, 481
values above/below average, 481
New Name, 266
Paste Special, 27
Printer Properties, 564
Protect Sheet, 421
Recommended Pivot Table, 323
Record Macro, 382
Scenario Manager, 373
Scenario Values, 374
Slicer Settings, 352
Sort
colors, 290
icons, 290
left-to-right, 291
pivot tables, 356
unique sequences with custom lists, 291-292
Split by Delimiter, 281
Symbol, 420
Top 10 Filter, 350
Update Links, 254
Value Field Settings, 330, 339
Watch Window, 223
Zoom, 31
Different First Page header, 574
direct precedent cells, displaying, 94
DISC function, 225
dismissing start screen permanently, 10
cell values in shapes, 543
chart styles, 493
collapsed subtotal view, sorting, 298-299
data bars for subsets of cells, 468
direct precedent cells, 94
File menu Backstage view, 23
fractions, 433
number of recent workbooks visible, 40
paste operations preview, 26
pivot tables
percentage of total, 339
running totals/rankings, 340
Print Preview full-screen, 566
Print tab, 48
QAT, 20
ribbon size, 17
scientific notation, 438
shortcut keys with ToolTips, 57
summary page with subtotals, 298
two workbooks on two monitors, 10
view modes, switching between, 32
whole earth (3D Maps), 516
worksheets side by side, 245-247
zero values, 40
distinct values, counting, 361
#DIV/0! error, 88
division operator (/), 85
DMAX function, 182
DMIN function, 182
Document Inspector
hidden content, finding, 25
limitations, 25
running, 26
DOLLAR function, 117
dollar signs ($)
adding, 57
after entering formulas, 76
functionality, 69
DOLLARDE function, 225
DOLLARFR function, 225
double factorials function, 112
double-clicking fill handles, 82
DPRODUCT function, 182
dragging fill handles, 81
drop-downs
Corrections, 552
resizing, 308
turning on/off, 304
list keyboard accelerators, 46
Show Values As
percentage of total, 339
running totals/rankings, 340
drop zones (pivot tables), customizing, 329
DSTDEV function, 182
DSTDEVP function, 182
criteria ranges
blank, 215
creating, 215
dates/numbers as, 217
duplicates
DURATION function, 225
DVAR function, 182
DVARP function, 182
Dwight to Diapers autocorrect option, 42
E
EDATE function, 115
Edit Formatting Rule dialog, 466
Edit menu legacy commands shortcut keys, 60-61
editing
chart settings, 494
equations, 420
macros, 391
object variables, adding, 397
simple variables, adding, 397
EFFECT function, 225
effects
pictures
artistic, 554
elapsed time, calculating, 151-154
ENCODEURL function, 223
end of month, calculating, 154
end_date argument (functions)
DATEDIF, 151
DAYS, 154
NETWORKDAYS, 155
engineering functions
BASE, 242
BESSELI, 242
BESSELJ, 242
BESSELK, 242
BESSELY, 242
BIN2DEC, 242
BIN2HEX, 242
BIN2OCT, 242
BITAND, 242
BITLSHIFT, 242
BITOR, 242
BITRSHIFT, 242
BITXOR, 242
COMPLEX, 242
CONVERT, 242
DEC2BIN, 242
DEC2HEX, 242
DEC2OCT, 242
DECIMAL, 243
DELTA, 243
ERF, 243
ERFC, 243
GESTEP, 243
HEX2BIN, 243
HEX2DEC, 243
HEX2OCT, 243
IMABS, 243
IMAGINARY, 243
IMARGUMENT, 243
IMCONJUGATE, 243
IMCOS, 243
IMCOSH, 243
IMCOT, 243
IMCSC, 243
IMCSCH, 243
IMDIV, 243
IMEXP, 243
IMLN, 243
IMLOG2, 244
IMLOG10, 244
IMPOWER, 244
IMPRODUCT, 244
IMREAL, 244
IMSEC, 244
IMSECH, 244
IMSIN, 244
IMSINH, 244
IMSQRT, 244
IMSUB, 244
IMSUM, 244
IMTAN, 244
listing of, 244
OCT2BIN, 244
OCT2DEC, 244
OCT2HEX, 244
equal to operator (=), 85
equal to rule (highlighting cells), 477-478
Equation, Insert New Equation command (Insert menu), 420
Equation Tools Design tab, 420
equations
editing, 420
handwriting, 11
ERF function, 243
ERFC function, 243
ERROR.TYPE function, 178
errors
checking, functions
IFNA function, 188
dates, 138
######, 88
#DIV/0!, 88
finding, 89
#N/A!, 88
#REF!, 88
#VALUE!, 88
Power Query, correcting, 283-284
replacing when printing, 577
Evaluate Formula command (Formulas menu), 419
Evaluate Formula dialog, 96, 419
evaluating. See analyzing
EVEN function, 112
Excel
2003
Access Key mode, 59
print area, setting, 67
2013
cloud storage, 11
important features, 10
subscription model, 10
MVP Andy Pope website, 37
Online
benefits, 579
survey data, collecting, 586-588
web workbooks
sharing, 586
workbooks, accessing from anywhere, 580
autocorrect options, 42
automatic inserts of decimal points, 41
categories, 37
custom lists, 40
date columns, 41
default file locations, 40
green triangle indicators, 41
gridline colors, 41
image sizes, 39
interface elements, hiding, 40
negative time, 41
new features, 38
number of recent workbooks visible, 40
opening, 37
print settings, 564
protected mode, 39
QAT, customizing, 22
Save File in This Format, 40
ToolTips, 38
trusted document settings, 39-40
trusted locations, 41
zero values, 40
EXP function, 112
expanding
formula bar, 30
ribbon, 20
EXPON.DIST function, 231
exponents operator (^), 85
Export, Create PDF/XPS command (File menu), 589
Export command (File menu), 24
exporting files, 24
extending selections, 51
external workbooks worksheet links, creating, 251
extremes, highlighting, 468-470
F
F4 key dollar sign entry, 75-76
after entering formulas, 76
FACT function, 112
FACTDOUBLE function, 112
factorials function, 112
FALSE function, 178
F.DIST function, 231
F.DIST.RT function, 231
field argument (DSUM function), 215
field1 argument (GETPIVOTDATA function), 213
field2 argument (GETPIVOTDATA function), 213
fields
creating with DAX, 362
date, filtering, 353
distinct values, counting, 361
geography, 512
multiple, subtotaling, 302-303
pivot tables
calculations, changing, 337-339
drop zones, adding/deleting, 329
numeric formatting, applying, 330
Backstage view
closing, 24
opening, 23
commands
Account, 24
Close, 24
Export, 24
Export, Create PDF/XPS, 589
Info, 23
Info, Check for Issues, Inspect Document, 26
New, 23
Open, 23
Options, General, Show the Start Screen When This Application Starts, 10
Recent File List, 24
Save, 23
Save As, 24
Share, 24
Document Inspector, running, 26
legacy commands shortcut keys, 59-67
recent workbooks list, clearing, 24
Print tab, displaying, 48
unsaved workbooks, recovering, 24
workbook information, displaying, 24-25
files
default locations, setting, 40
exporting, 24
opening, 23
protected mode, 39
storing, 24
trusted documents settings, 39-40
fill handles
double-clicking, 82
dragging, 81
Filter by Selection icon, 308-309
filters
advanced, 314
Advanced Filter dialog, 314-315
applying, 315
AutoFilter
filtering by selection, 309
clearing, 307
colors, 310
columns with filters, identifying, 307
combining, 307
custom, 313
drop-downs
resizing, 308
turning on/off, 304
Filter by Selection icon, 308-309
icons, 310
numbers, 312
pivot tables
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
slicers. See slicers
Timeline control, 353
refreshing, 308
results
formatting/copying, 314
totaling, 314
slicers, 351
clearing, 353
items, selecting, 353
text, 312
FILTERXML function, 223
financial functions
ACCRINT, 224
ACCRINTM, 224
AMORDEGRC, 224
AMORLINC, 224
COUPDAYBS, 224
COUPDAYS, 224
COUPDAYSNC, 224
COUPNCD, 224
COUPNUM, 224
COUPPCD, 224
CUMIPMT, 224
CUMPRINC, 225
DB, 225
DDB, 225
DISC, 225
DOLLARDE, 225
DOLLARFR, 225
DURATION, 225
EFFECT, 225
FV, 225
FVSCHEDULE, 225
INTRATE, 225
IPMT, 225
IRR, 226
ISPMT, 226
listing of, 224
MDURATION, 226
MIRR, 226
NOMINAL, 226
NPER, 226
NPV, 226
ODDFPRICE, 226
ODDFYIELD, 226
ODDLPRICE, 226
ODDLYIELD, 227
PDURATION, 227
PMT, 227
PPMT, 227
PRICE, 227
PRICEDISC, 227
PRICEMAT, 227
PV, 227
RATE, 227
RECEIVED, 227
RRI, 227
SLN, 227
SYD, 227
TBILLEQ, 227
TBILLPRICE, 227
TBILLYIELD, 228
VDB, 228
XIRR, 228
XNPV, 228
YIELD, 228
YIELDDISC, 228
YIELDMAT, 228
financial waterfall charts, 500
find shortcut key, 48
FINDB function, 117
finding. See also lookup functions
cells
with data from past 30 days, 484
within three days of today, 482
characters in text cells, 168-171
formula errors, 89
functions, 101
hidden content, 25
last records (macros), 390
values
values matching position, 198-199
find_text argument (functions)
FIND, 170
SEARCH, 171
F.INV function, 231
F.INV.RT function, 231
First page Headers, 574
FISHER function, 231
FISHERINV function, 231
five-icon sets, 471
FIXED function, 117
Flash Fill, 288
coaching, 288
dates, 289
mathematical transformations, 288
shortcut key, 48
troubleshooting, 289
FLOOR.MATH function, 112
Select Case, 405
Font tab (Format Cells dialog), 48
fonts
bold/italic/underline, 440-441
color style, 487
size, formatting, 439
style, 487
typefaces, changing, 440
footers
adding, 571
automatic, 571
custom, 572
different in same worksheet, 573-574
pictures/watermarks, adding, 572-573
scaling, 574
FORECAST function, 231
FORECAST.ETS function, 232
FORECAST.ETS.CONFINT function, 232
FORECAST.ETS.SEASONALITY function, 232
FORECAST.ETS.STAT function, 232
FORECAST.LINEAR function, 232
forecasting, 9
Format as Table command (Home menu), 6
Format as Table shortcut key, 48
custom number formats, 141
Font tab shortcut key, 48
numbers, 431
Format Comment dialog, 458
Format menu legacy commands shortcut keys, 62-63
Format Shape dialog, 545
format_text argument (TEXT function), 175
formatting. See also data visualizations
cells
based on content, 481
styles, 487
conditional. See conditional formats
copying/pasting formats, 27
conditional formats, 460
new worksheets, 461
rules, 459
currency, 432
data sets as tables, 6
filtered results, 314
fonts
size, 439
typefaces, 440
formula results, automatically, 92
fractions, 433
individual characters, 453-454
invoice registers (macro), 389
file to open, selecting, 400
filename for saving, selecting, 400
last record, finding, 390
variable number of rows, handling, 389
running, 388
numbers
applying, 330
conditions/colors, 437
decimal places, 436
Format Cells dialog, 431
four zones, 435
scientific notation, 438
text/spacing, controlling, 436
thousands separators, 431
shapes, 545
social security numbers, 433-434
text
mini toolbar, 29
values above/below average, 481
worksheets
bold/italic/underline, 440-441
font size, 439
font typefaces, 440
numbers with Home tab, 429-430
themes. See themes
traditional icons, 427
wrapping text in cells, 455
formula bar, expanding, 30
formulas
absolute references, 265
advanced filter criteria, 315-316
auditing
tracing dependents, 96
AutoComplete, 101
AutoSum errors in macros, fixing, 399
calculated fields, adding to pivot tables, 341-342
calculating in slow motion, 419
cell referencing
relative, 72
cells
highlighting, 94
comments, adding, 190
cells within three days of today, finding, 482
data from past 30 days, finding, 484
data from specific days of the week, highlighting, 485
entire rows, highlighting, 485
every other row, highlighting, 485-486
setting up, 482
writing formulas, 482
cutting formulas, compared, 91-92
double-clicking fill handles, 82
dragging fill handles, 81
direct precedent cells, displaying, 94
mouse method, 78
######, 88
#DIV/0!, 88
finding, 89
#N/A!, 88
#REF!, 88
#VALUE!, 88
after entering formulas, 76
formatting results automatically, 92
formula bar, expanding, 30
implicit intersection, 264
linking back to copied ranges, 27
named ranges
applying to existing formulas, 261
names, assigning, 266
operators
addition and multiplication example, 87
mathematical, 85
multiple sets of parentheses, stacking, 87-88
order of operations, 86
unary minus example, 86
partial evaluations, 97
recalculating, 71
referencing previous worksheets, 269-270
slow motion evaluations, 96-97
starting, 71
two-way lookups with names, 263-264
values, compared, 70
VLOOKUP, 265
Watch Window screen, 96
worksheets
links, creating, 251
Formulas category (Excel Options dialog), 37
Formulas menu, Evaluate Formula command, 419
Formulas tab, 100
FORMULATEXT function, 180, 204-205
four-icon sets, 471
four zones (custom number formats), 435
fraction operator (/), 85
fractions, formatting, 433
frequency distribution, creating, 497-498
FREQUENCY function, 232
friendly_name argument (HYPERLINK function), 209
from-scratch macros
combining loops with final rows, 404
Select Case, 405
last row with data, finding, 402
looping through all rows, 402-403
running, 406
Frontline Systems, 377
F.TEST function, 231
Function Arguments dialog, 103
functions
arguments, entering, 100
AutoAverage icon, adding, 108
autocompleting, 101
AutoCount icon, adding, 108
AutoSum, 105
icon, 105
listing of, 182
DAYS360, 114
EDATE, 115
listing of, 114
NETWORKDAYS.INTL, 115, 156-157
YEARFRAC, 116
engineering
BASE, 242
BESSELI, 242
BESSELJ, 242
BESSELK, 242
BESSELY, 242
BIN2DEC, 242
BIN2HEX, 242
BIN2OCT, 242
BITAND, 242
BITLSHIFT, 242
BITOR, 242
BITRSHIFT, 242
BITXOR, 242
COMPLEX, 242
CONVERT, 242
DEC2BIN, 242
DEC2HEX, 242
DEC2OCT, 242
DECIMAL, 243
DELTA, 243
ERF, 243
ERFC, 243
GESTEP, 243
HEX2BIN, 243
HEX2DEC, 243
HEX2OCT, 243
IMABS, 243
IMAGINARY, 243
IMARGUMENT, 243
IMCONJUGATE, 243
IMCOS, 243
IMCOSH, 243
IMCOT, 243
IMCSC, 243
IMCSCH, 243
IMDIV, 243
IMEXP, 243
IMLN, 243
IMLOG2, 244
IMLOG10, 244
IMPOWER, 244
IMPRODUCT, 244
IMREAL, 244
IMSEC, 244
IMSECH, 244
IMSIN, 244
IMSINH, 244
IMSQRT, 244
IMSUB, 244
IMSUM, 244
IMTAN, 244
listing of, 242
OCT2BIN, 244
OCT2DEC, 244
OCT2HEX, 244
financial
ACCRINT, 224
ACCRINTM, 224
AMORDEGRC, 224
AMORLINC, 224
COUPDAYBS, 224
COUPDAYS, 224
COUPDAYSNC, 224
COUPNCD, 224
COUPNUM, 224
COUPPCD, 224
CUMIPMT, 224
CUMPRINC, 225
DB, 225
DDB, 225
DISC, 225
DOLLARDE, 225
DOLLARFR, 225
DURATION, 225
EFFECT, 225
FV, 225
FVSCHEDULE, 225
INTRATE, 225
IPMT, 225
IRR, 226
ISPMT, 226
listing of, 224
MDURATION, 226
MIRR, 226
NOMINAL, 226
NPER, 226
NPV, 226
ODDFPRICE, 226
ODDFYIELD, 226
ODDLPRICE, 226
ODDLYIELD, 227
PDURATION, 227
PMT, 227
PPMT, 227
PRICE, 227
PRICEDISC, 227
PRICEMAT, 227
PV, 227
RATE, 227
RECEIVED, 227
RRI, 227
SLN, 227
SYD, 227
TBILLEQ, 227
TBILLPRICE, 227
TBILLYIELD, 228
VDB, 228
XIRR, 228
XNPV, 228
YIELD, 228
YIELDDISC, 228
YIELDMAT, 228
finding, 101
Formulas tab, 100
help
Function Arguments dialog, 103
on-grid ToolTips, 103
information
CELL, 191
IS, 189
listing of, 178
N, 190
Insert Function dialog, 102
logical
AND, 184
IFNA, 188
listing of, 177
NOT, 186
OR, 184
lookup
CHOOSE, 192
listing of, 179
LOOKUP, 203
math
AGGREGATE. See AGGREGATE function
AVERAGEIF, 135
AVERAGEIFS, 136
CEILING MATH, 112
COMBIN, 112
COMBINA, 112
COUNTIFS, 136
EVEN, 112
EXP, 112
FACT, 112
FACTDOUBLE, 112
FLOOR.MATH, 112
INT, 113
listing of, 111
MULTINOMIAL, 113
ODD, 113
PI, 113
PRODUCT, 113
QUOTIENT, 113
roots, 133
SIGN, 113
SQRTPI, 113
SUMPRODUCT, 114
TRUNC, 114
matrix, 241
parentheses, 99
reference
statistical
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
GAMM.INV, 232
GAMMLN, 232
GAUSS, 232
GEOMEAN, 233
GROWTH, 233
HARMEAN, 233
HYPGEOM.DIST, 233
INTERCEPT, 233
KURT, 233
LARGE, 233
LINEST, 234
listing of, 234
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
SUMQ, 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
text
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
trigonometry, 240
Web, 223
FV function, 224
FVSCHEDULE function, 224
Fylstra, Dan, 377
G
galleries, 15
Info
marking workbooks as final, 25
workbook information, displaying, 24-25
Paste Options
keyboard accelerators, 28
opening, 28
options after performing paste operations, 27
paste operations, previewing, 26
right-click menu access, 28
Picture Styles, 554
GAMMA function, 232
GAMMA.DIST function, 232
GAMMA.INV function, 232
GAMMALN function, 232
GAUSS function, 232
General category (Excel Options dialog), 37
geocoding results (3D Maps), 512-513
geographical components. See 3D Maps
geography fields, 512
GEOMEAN function, 233
GESTEP function, 243
Get & Transform. See Power Query
GETPIVOTDATA function, 180, 211-213
Go To dialog shortcut key, 48
Grand Total row, 297
graphing data. See charts
greater than operator (>), 85
greater than or equal to operator (>=), 85
greater than rule (highlighting cells), 476-477
greatest common divisor (GCD function), 112, 131
green triangle indicators, 41
gridlines
colors, customizing, 41
printing, 577
Group and Outline button
collapsed subtotal view, sorting, 298-299
subtotal rows
summary page with subtotals, 298
group editing (Excel Online), 580-584
grouping
daily dates automatically
overriding, 335
turning off, 335
dates
GROWTH function, 233
H
handwriting equations, 11
HARMEAN function, 233
Header & Footer Tools Design tab
automatic, adding, 571
custom headers/footers, 572
print settings, 564
scaling, 574
headers
adding, 571
automatic, 571
custom, 572
different in same worksheet, 573-574
first rows of data as, identifying, 281
pictures/watermarks, adding, 572-573
scaling, 574
headings
AutoSum, 106
cell names based on, creating, 262-263
printing, 577
heat maps, 505
height argument (OFFSET function), 205
heights (rows), formatting, 443-444
help
functions
Function Arguments dialog, 103
on-grid ToolTips, 103
VBA, 395
Help menu legacy commands, 59
HEX2BIN function, 243
HEX2DEC function, 243
HEX2OCT function, 243
Hidden and Empty Cell Settings dialog, 535
hidden content, finding, 25
hiding
interface elements, 40
hierarchical SmartArt, 542
hierarchy charts, 496
highlighting
duplicate/unique values, 479-480
Highlight Cell Rules menu, 475-478
less than/equal to rules, 477-478
options, 475
text containing values, 480-481
traditional rules, 475
data from specific days of the week, 485
formula cells, 94
rows
entire, 485
HLOOKUP function, 180
holidays argument (NETWORKDAYS function), 156
Home menu commands, Format as Table, 6
horizontal ranges, transposing, 210-211
hour argument (TIME function), 146
HYPERLINK function, 180, 209-210
HYPGEOM.DIST function, 233
I
mixing icons, 473
setting up, 471
style, 487
icons
AutoAverage function, adding, 108
AutoCount function, adding, 108
AutoFilter, 309
AutoSum, 105
Comma, 429
Currency, 429
Decrease Decimal, 429
filtering, 310
formatting, 427
icon sets, mixing, 473
Increase Decimal, 429
Insert Worksheet, 29
navigation, 514
paintbrush, 493
Percentage, 429
Quick Print, adding, 561
Remove Background, 555
ribbon
selecting with keyboard accelerators, 44-45
sorting data, 290
Symbol, 420
white space around, adding, 26
worksheet navigation, 29
Zoom In/Out buttons, 31
If-End If flow control, 404-405
IFERROR function, 178, 187-188
illustrating workbooks
pictures, 537
adding, 547
artistic effects, 554
backgrounds, removing, 555-556
brightness/contrast, adjusting, 552
from computers, adding, 548
effects, 554
file size, reducing, 557
multiple at once, adding, 548
screen clippings, adding, 557-558
shapes, 537
cell values, displaying, 543
formatting, 545
SmartArt, 537
changing existing styles, 540-541
common elements, 539
hierarchical, 542
pictures, adding, 541
styles, 538
IMABS function, 243
images. See pictures
IMAGINARY function, 243
IMARGUMENT function, 243
IMCONJUGATE function, 243
IMCOS function, 243
IMCOSH function, 243
IMCOT function, 243
IMCSC function, 243
IMCSCH function, 243
IMDIV function, 243
IMEXP function, 243
IMLN function, 243
IMLOG2 function, 244
IMLOG10 function, 244
implicit intersection, 264
IMPOWER function, 244
IMPRODUCT function, 244
IMREAL function, 244
IMSEC function, 244
IMSECH function, 244
IMSIN function, 244
IMSINH function, 244
IMSQRT function, 244
IMSUB function, 244
IMSUM function, 244
IMTAN function, 244
in commands, 23
in-cell bar charts. See data bars
Increase Decimal icon, 429
arguments, 202
data left of key fields, finding, 200-201
tables, filling, 202
index_num argument (LOOKUP function), 192
INDIRECT function, 180, 207-208
Info command (File menu), 23
INFO function, 178
Info gallery
information, displaying, 24-25
marking as final, 25
information functions
CELL, 191
IS, 189
listing of, 178
N, 190
Ink Equation command (Insert menu), 11
Insert Chart dialog, 492
Insert Equation tool, 11
Insert Function dialog, 102
Insert menu commands
3D Map, 512
Equations, Ink Equation, 11
legacy shortcut keys, 62
Online Pictures, 548
Pivot Table, 6
Screenshot, 557
Insert Pictures dialog, 548
Insert Shapes dialog, 545
Insert Worksheet icon, 29
Inspect Document command (File menu), 26
instance_num argument (SUBSTITUTE function), 171
INT function, 113
interactive dashboards, 365
INTERCEPT function, 233
interface
color, adding, 5
Document Inspector
hidden content, finding, 25
limitations, 25
running, 26
elements, hiding, 40
File menu. See File menu
formula bar, expanding, 30
Insert Worksheet icon, 29
mini toolbar, 29
Paste Options gallery. See Paste Options gallery
QAT
commands, deleting, 21
displaying, 20
ribbon. See ribbon
status bar, 31
views, switching, 32
white space around icons, adding, 26
worksheets navigation icons, 29
zooming in/out, 31
international workdays, calculating, 156-157
Internet searches, 13
intersection operator (<space>), 85, 263-264
INTRATE function, 225
invalid names, 257
invoice register macro, 389
file to open, selecting, 400
filename for saving, selecting, 400
last record, finding, 390
variable number of rows, handling, 389
IPMT function, 225
IRR function, 226
IS function, 189
ISBLANK function, 179
ISERR function, 179
ISERROR function, 179
ISEVEN function, 179
ISFORMULA function, 179, 188-189
ISLOGICAL function, 179
ISNA function, 179
ISNONTEXT function, 179
ISNUMBER function, 179
ISODD function, 179
ISPMT function, 226
ISREF function, 179
ISTEXT function, 179
italic shortcut key, 55
item1 argument (GETPIVOTDATA function), 213
item2 argument (GETPIVOTDATA function), 213
J
JIS function, 117
joining
multiple tables with Data Model
benefits, 360
data, preparing, 358
pivot table, creating, 358
relationships, defining, 359
second table, adding, 359
K
accessing, 43
backing up one menu level, 46
confusion, 46
drop-down lists, 46
Excel 2003, 58
gallery options, selecting, 45-46
legacy dialogs, 46
Paste Options gallery, 28
ribbon
tabs, 44
keyboard shortcuts
current ranges, selecting, 56
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 area, setting, 67
ranges, defining as tables, 82
right-click menus, opening, 56
selections, extending, 51
slicer items, selecting, 57
striking through cells, 56
KeyTips. See keyboard accelerators
KURT function, 233
L
Label Filter dialog, 349
Label Filters fly-out, 346, 349-350
labels
cell names based on, creating, 262-263
Language category (Excel Options dialog), 37
LARGE function, 233
last tasks, repeating, 57
layers (3D Maps), combining, 515
layout
headers/footers
adding, 571
automatic, adding, 571
custom, 572
different in same worksheet, 573-574
pictures/watermarks, adding, 572-573
scaling, 574
page breaks, 569
automatic versus manual, 570
deleting, 570
moving, 570
leading spaces, deleting, 160-162
least common multiple function, 113
least common multiples, 131
LEFTB function, 117
left-to-right sorts, 291
legacy command shortcut keys (menus)
File, 60
Insert, 62
Window, 66
legacy dialogs, 46
LENB function, 117
less than operator (<), 85
less than or equal to operator (<=), 85
less than rule (highlighting cells), 477-478
line charts, 523
LINEST function, 234
link_location argument (HYPERLINK function), 209
links
copied ranges, 27
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 linked workbooks, 253
unsaved workbooks, 252
Update Links dialog, suppressing, 254
lists
crossing off tasks, 56
custom
options, 40
matching
LN function, 241
loading data (Power Query), 287
locations
default file, 40
trusted, customizing, 41
LOG function, 241
LOG10 function, 241
LOGEST function, 234
logical functions
AND, 184
IFNA, 188
listing of, 177
NOT, 186
LOGNORM.DIST function, 234
LOGNORM.INV function, 234
lookup_array argument (MATCH function), 198
CHOOSE, 192
arguments, 202
data left of key fields, finding, 200-201
tables, filing, 202
listing of, 179
LOOKUP, 203
arguments, 198
INDEX function, combining, 201-202
arguments, 196
values based on ranges, finding, 193-194
lookup_value argument (functions)
MATCH, 198
VLOOKUP, 196
lookups, two-way with names, 263-264
M
m date format, 139
macro recorder
AutoSum errors, fixing, 399
combination customer report, 409-411
macro, 415
visible cells only, selecting, 414-415
editing, 391
from-scratch, creating
combining loops with final rows, 404
last row with data, finding, 402
looping through all rows, 402-403
running, 406
invoice register, 389
file to open, selecting, 400
filename for saving, selecting, 400
last record, finding, 390
variable number of rows, 389
last records, finding, 390
macro recorder
mail merge, formatting, 383-384
object variables, adding, 397
recording, 382
running, 388
simple variables, adding, 397
variable number of rows, handling, 389
running, 388
manual calculation mode, 332
mapping
3D Maps, 8
animating, 508
category colors, 505
columns size/color, 516
corn acreage by state example, 503
layers, combining, 515
lines between points, animating, 517
panes, resizing, 516
photos, adding, 515
retail store custom example, 519-521
satellite photos, adding, 516
tours, 518
whole earth, displaying, 516
data to sparklines
not adjacent to original data sets, creating, 524
source data/target ranges, 525
square data sets, 525
heat maps, 505
marking workbooks as final, 25
arguments, 198
INDEX function, combining
data left of key fields, finding, 201
tables, filling, 202
matching lists
match_type argument (MATCH function), 198
math equations, editing, 420
arguments, 121
COUNT versus COUNTA, 124
filtered data set visible items calculations, 123
options argument, 121
syntax, 121
AVERAGEIF, 135
AVERAGEIFS, 136
CEILING MATH, 112
COMBIN, 112
COMBINA, 112
COUNTIFS, 136
EVEN, 112
EXP, 112
FACT, 112
FACTDOUBLE, 112
FLOOR.MATH, 112
INT, 113
listing of, 111
MULTINOMIAL, 113
ODD, 113
PI, 113
PRODUCT, 113
QUOTIENT, 113
RANDBETWEEN, 113
roots, 133
SIGN, 113
SQRTPI, 113
SUBTOTAL, 114
multiple levels of totals, 126-127
visible cells, totaling, 127
SUM, 114
SUMPRODUCT, 114
trigonometry, listing of, 240
TRUNC, 114
visible cells, totaling, 127
mathematical operators, 85
matrix functions, 241
MAX function, 234
MAXA function, 234
MDETERM function, 241
MDURATION function, 226
MEDIAN function, 234
menus
Alternate Drag-and-Drop, 249
backing up one level, 46
Data, legacy command shortcut keys, 64-66
Date Filters fly-out, 347, 350
Edit, legacy commands shortcut key, 60-61
File. See File menu
Format, legacy commands shortcut key, 63-63
Help, legacy shortcut keys, 66-67
Highlight Cells Rules, 475-478
A Date Occurring rule, 478-479
Duplicate Values rule, 479-480
less than/equal to rules, 477-478
options, 475
Text That Contains rule, 480-481
traditional rules, 475
Insert, legacy commands shortcut keys, 62
Label Filters fly-out, 346, 349-350
right-click, opening, 56
Value Filters fly-out, 346-347
View, legacy command shortcut keys, 61-62
Window, legacy command shortcut keys, 66
Merge Scenarios dialog, 375
merging
scenarios, 375
methods (VBA), 393
MIDB function, 118
MIN function, 234
MINA function, 234
mini toolbar, 29
minute argument (TIME function), 146
MINVERSE function, 241
MIRR function, 226
mm date format, 139
mmm date format, 139
mmmm date format, 139
mmmmm date format, 139
MMULT function, 241
MODE.MULT function, 234
MODE.SNGL function, 235
monitors, multiple, 10
month argument (DATE function), 145
months argument (EOMONTH function), 154
More Sort Options dialog, 356
mouse
formulas, entering, 78
worksheet links, creating, 250
moving rows, 333
MULTINOMIAL function, 113
multiplication operator (*), 85
MUNIT function, 241
N
#N/A! error, 88
names
absolute references, simplifying, 265
assigning with Name dialog, 257-258
based on labels/headings, creating, 262-263
cells, assigning, 256
columns, renaming, 281
formulas, assigning, 266
implicit intersection, 264
navigating worksheets/workbooks, 258-259
ranges
existing formulas, applying, 261
formulas, simplifying, 260-261
referencing, 261
valid/invalid examples, 257
values, assigning, 266
worksheets
benefits, 259
defining, 259
referencing, 260
returning, 191
navigation
3D Maps, 514
data set last row, jumping to, 56
next corner of selections, 56
worksheets
icons, 29
shortcut key, 56
negative time, 41
NEGBINOM.DIST function, 235
NETWORKDAYS function, 115, 154-156
NETWORKDAYS.INTL function, 115, 156-157
New command (File menu), 23
new features
3D Maps, 8
color interface, 5
Excel 2013
cloud storage, 11
start screen, dismissing permanently, 10
subscription model, 10
two workbooks, displaying on two monitors, 10
Excel Options dialog, 38
forecasting, 9
handwriting equations, 11
Internet searches, 13
new chart types, 8
Power Query, 7
Tell Me What You Want to Do box, 13-14
touchable slicers, 12
New Formatting Rule dialog
formatting cells based on content, 481
values above/below average, 481
New Name dialog, 266
new_text argument (SUBSTITUTE function), 171
NOMINAL function, 226
NORM.DIST function, 235
NORM.INV function, 235
NORM.S.DIST function, 235
NORM.S.INV function, 235
not equal to operator (<>), 85
NPER function, 226
NPV function, 226
num_chars argument (functions)
LEFT, 166
MID, 166
RIGHT, 166
numbers. See also math functions
adding with status bar, 31
data visualizations. See data visualizations
database criteria, 217
filters, 312
Flash Fill, 288
formatting
applying, 330
conditions/colors, 437
copying/pasting, 27
currency, 432
decimal places, 436
Format Cells dialog, 431
four zones, 435
fractions, 433
scientific notation, 438
styles, 487
text/spacing, controlling, 436
thousands separators, 431
zip codes/phone numbers/social security numbers, 433-434
headings, 106
number_times argument (REPT function), 172
NUMBERVALUE function, 118
O
object variables, adding to macros, 397
objects (VBA), 393
OCT2BIN function, 244
OCT2DEC function, 244
OCT2HEX function, 244
ODD function, 113
ODDFPRICE function, 226
ODDFYIELD function, 226
ODDLPRICE function, 226
ODDLYIELD function, 227
Office 365, 10
Office theme, 450
old_text argument (SUBSTITUTE function), 171
one-click sorting, 292
OneDrive, 11
survey data, collecting, 586-588
workbooks
accessing from anywhere, 580
Online Pictures command (Insert menu), 548
Open command (File menu), 23
open shortcut key, 48
opening
Excel Options dialog, 37
files, 23
Paste Options gallery, 28
right-click menus, 56
operators
mathematical, 85
order of operations, 86
addition and multiplication example, 87
multiple parentheses, stacking, 87-88
unary minus example, 86
options. See Excel Options dialog
options argument (AGGREGATE function), 121
Options command (File menu), 24, 37
order of operations, 86
addition and multiplication example, 87
multiple parentheses, stacking, 87-88
unary minus example, 86
organization charts, 542
out commands, 23
overriding order of operations operator (()), 85
P
page breaks, 569
automatic versus manual, 570
deleting, 570
moving, 570
Page Layout tab
page breaks, 569
print settings, 563
Page Layout view, 32
Normal view, compared, 576-577
print settings, 564
page numbers, controlling, 578
paintbrush icon, 493
paper sizes, selecting, 566
parentheses ( )