Index
Symbols
3D formulas
building with mouse, 330
spearing through worksheets, 328-330
3D Maps
animating over time, 607
color categories in, 605
columns
changing colors, 615
resizing, 615
custom maps, 619
image preparation, 619
Flat Map option, 616
layers, combining, 615
panes, resizing, 616
photos, adding to points, 614
points, animating line between, 617
rotating, 614
satellite photos in, 616
tipping, 614
tours, creating, 618
troubleshooting, 613
types of
column charts, 604
heat maps, 604
shaded area maps, 603
3D models, 17-18, 639, 665-666
32-bit Excel, 447
64-bit Excel, 447
1904 date system, 85
& (ampersand) operator, joining text, 141-142
* (asterisk) wildcard character, 188
#CALC! error message, 139
#DIV/0! error message, 138
=+ (equal plus), in formulas, 129
###### error message, 139
#FIELD! error message, 139
#N/A! error message, 138
#NULL! error message, 139
? (question mark) wildcard character, 188
#REF! error message, 138
#SPILL! error message, 40, 139
[ ] (square brackets), in date/time formatting, 194
#VALUE! error message, 138
A
A1 style, R1C1 style versus, 484-485
absolute recording of macros, 467-470
avoiding with names, 326
in links to external workbooks, 310
toggling with relative references, 103
accessibility options, 26-28, 82
accessing
data with Power Query M functions, 738-743
slicers with keyboard accelerators, 91
VBA help, 482
Account command (Backstage), 67
ACCRINT() function, 284
ACCRINTM() function, 284
ACOS() function, 298
ACOSH() function, 298
ACOT() function, 299
ACOTH() function, 299
activating. See viewing
active cell
scrolling to, 509
viewing, 102
adding. See also inserting
calculated fields to pivot tables, 411-412
calculations outside pivot table, 405-406
cells in status bar, 73
columns in Power Query, 350
commands
to Quick Access Toolbar, 63-64
comments to formulas, 246
fields to pivot tables, 396-397
images to SmartArt, 644
leading zeros to numbers, 228-229
multiple scenarios, 458
numbers
with SUBTOTAL() function, 178-179
photos to 3D Map points, 614
adding machine setting, enabling, 86
Add-Ins settings (Excel Options dialog box), 80
addition and multiplication example (order of operations), 137
addresses versus streets in 3D Maps, 613
ADDRESS() function, 233
adjusting pictures, 654. See also changing; editing
artistic effects, 659
Picture Styles gallery, 657-659
reducing file size, 661
transparency, 653
Advanced Filter command, 385-386
ranges of values in, 387
advanced filters in macros, 499-500
advanced options for conditional formatting, 577-578
Advanced settings (Excel Options dialog box), 80
AGGREGATE() function, 163, 172-175
aligning cells, 530
Alt Text, 26
Alternate Drag-and-Drop menu, creating links with, 307-309
AMORDEGRC() function, 284
AMORLINC() function, 285
ampersand (&) operator, joining text, 141-142
AND() function, 231, 238, 275-276
animating in 3D Maps
map lines, 617
over time, 607
appending worksheets in Power Query, 352-355
applying
chart styles with paintbrush icon, 588-589
themes, 547
Apply Names dialog box, 322
Arabic numerals, converting to/from Roman numerals, 182
AREAS() function, 233
arguments in functions
Function Arguments dialog box, 154-157
syntax, 151
array formulas
copying, 340
creating ranges of dates with, 338-339
implicit intersection and, 325-326
array functions, 38
extracting unique values with formulas, 48-50
filtering with formulas, 46-47
formulas with multiple answers, 39-42
generating random numbers, 53-54
generating sequence of numbers, 51-52
arrow key method (entering formulas), 128-129
Artificial Intelligence feature, 5-6, 38. See also Ideas; Insights
artistic effects, applying to pictures, 659
ASC() function, 168
ASIN() function, 299
ASINH() function, 299
aspect ratio for pictures, unlocking, 655
assigning shortcut keys to macros, 472
asterisk (*) wildcard character, 188
ATAN() function, 299
ATAN2() function, 299
ATANH() function, 299
auditing
worksheets with Inquire, 511
AutoComplete, 153
AutoCorrect, 86
AutoFilter. See also Filter
grouping dates, 85
in macros, 501
automatic grouping of date/time columns in pivot tables, disabling, 82
automatic headers/footers, inserting, 678
automatic page breaks, 675-676
AutoSave
enabling, 82
AutoShapes. See shapes
errors, fixing in macros, 485-486
from top of column, 161
AVEDEV() function, 289
AVERAGE() function, 289
AVERAGEA() function, 289
AVERAGEIF() function, 163
AVERAGEIFS() function, 163, 186-188
Axel font family, 546
axis settings for sparklines
troubleshooting, 626
B
background of pictures, removing, 659-661
backing up menu levels with keyboard accelerators, 90
closing, 67
workbooks
clearing recent list, 68
finding hidden content, 69
marking as final, 69
recovering unsaved, 67
viewing current info, 68
BAHTTEXT() function, 168
bar of pie charts, creating, 596-597
BASIC versus VBA, 478
BESSELI() function, 300
BESSELJ() function, 300
BESSELK() function, 300
BESSELY() function, 300
BETA.DIST() function, 289
BETA.INV() function, 289
BIN2DEC() function, 300
BIN2HEX() function, 300
BIN2OCT() function, 300
Binary Power Query M functions, 743-744
BINOM.DIST() function, 289
BINOM.DIST.RANGE() function, 289
BINOM.INV() function, 289
BITAND() function, 301
BITLSHIFT() function, 301
BITOR() function, 301
BITRSHIFT() function, 301
BITXOR() function, 301
blank cells, viewing zero as, 85
blanks in pivot tables, removing, 401-402
blank workbooks, recording macros in, 475-477
bold format, 532
borders, formatting, 533
bottom of data, moving to, 101
brightness (of pictures), adjusting, 656-657
browsing file names in macros, 486-487
building. See also creating; entering
3D formulas with mouse, 330
additional visualizations for Power BI Desktop, 698-699
formulas by typing, 311
visualizations for Power BI Desktop, 694-698
C
calculating
data, shortcut keys for, 97-101
formulas in slow motion, 506-507
calculations, 205
with pivot tables, 403
adding outside pivot table, 405-406
changing field calculation, 406-409
running totals and rankings, 409-410
calculator, Excel as, 150
case sensitivity of sorting data, 359-360
CEILING() function, 163, 177-178
CEILING.MATH() function, 164, 177-178
cells
active cell
scrolling to, 509
viewing, 102
adding in status bar, 73
aligning, 530
characters in, finding, 221-224
copying, keeping on Clipboard, 20-22
finding
from past 30 days, 580
from specific days of week, 580
within three days of today, 580
formatting
bold/italic/underline, 532
borders, 533
changing numeric formats, 519-521
currency formats, 522
custom number formats, 525-529
font typeface, 531
with Format Cells dialog box, 518-519
justifying text in ranges, 550-551
numeric formatting with thousands separators, 522
in pivot tables, 402
resizing columns/rows, 535-536
with strikethrough, 102
superscripts/subscripts, 24-25
ZIP code/telephone/SSN formats, 524-525
formulas in
highlighting, 146
protecting, 509
highlight cells rules, 570-571
duplicate/unique values, 574-575
Greater Than/Less Than/Equal rules, 571-573
text containing values, 575-576
inserting, shortcut keys and, 21
names
advantages of, 315
avoiding absolute references, 326
defining multiple names from labels, 322-324
defining with New Name dialog box, 317-318
implicit intersection, 325
navigation with Name dialog box, 318-319
two-way lookups with, 324
worksheet-level scope, 319-320
number of characters in, finding, 220-221
in pivot tables, retrieving, 270-272
references, building and evaluating, 266-267
relative referencing, 120
returning single, 279
selecting
moving to corner of, 102
unselecting with Ctrl+click, 24
subsets, creating data bars for, 562
symbols, inserting, 507
toggling references with F4, 123-126
viewing contents, 284
visible cells, selecting in macros, 502
with hyperlinks, selecting, 268
zero in, viewing as blank, 85
centering
reports, 685
changing. See also adjusting pictures; editing
3D Map column colors, 615
AutoCorrect settings, 86
chart options with plus icon, 590
default chart template, 598
default file location, 84
default pivot table layout, 82
default save format, 84
Excel features, requesting, 20, 415
field calculations in pivot tables, 406-409
fields in pivot tables, 397-399
first page number, 685
font typeface, 531
gridline color, 85
number of recent workbooks, 84
orientation, 673
page setup settings, 683
paper size, 673
pie charts to bar of pie charts, 596-597
pivot tables, 403
printer properties, 683
shapes, 648
sparkline axis settings, 630-631
touchscreen behavior, 82
characters
finding number in cell, 220-221
charts. See also sparklines; visualizations
available as sparklines, 623-624
creating
changing options with plus icon, 590
combo charts, 591
deleting extraneous data, 589-590
histogram charts, 592
from Recommended Charts, 587-588
styling with paintbrush icon, 588-589
with templates, 598
troubleshooting chart positioning, 588
default template, changing, 598
pasting data into, 594
Chat panel, 37
check boxes, filtering pivot tables, 419-420
checked out files, saving as server drafts, 82
Child and Parent DAX functions, 726
CHISQ.DIST() function, 290
CHISQ.DIST.RT() function, 290
CHISQ.INV() function, 290
CHISQ.INV.RT() function, 290
CHISQ.TEST() function, 290
CHOOSE() function, 234, 239-242, 248-249
choosing. See selecting
CLEAN() function, 168
cleaning data
Power Query. See Power Query
clearing
filters, 377
pivot table filters, 419
recent workbooks list, 68
slicer filters, 427
Clipboard, copied cells on, 20-22
Close command (Backstage), 67
closed workbooks, updating links to, 313
closing Backstage view, 67
co-authoring. See shared workbooks
Code pane (VBE), 477
collapsed view (subtotals)
collapsing. See resizing
collections (VBA), 479
colors
in custom number formats, 528
of gridlines, changing, 85
multiple colors in data bars, 586
in themes
choosing, 545
in old Office theme, 545
column charts, 604
as sparklines, 623
COLUMN() function, 234
column headings, printing, 685
columns
in 3D Maps
changing colors, 615
resizing, 615
adding in Power Query, 350
identifying filtered, 376
multiple columns, VLOOKUP function with, 15
sorting, left-to-right sorts, 360
COLUMNS() function, 234
COMBIN() function, 164
COMBINA() function, 164
combination macro example, 497-498
AutoFilter, 501
selecting visible cells, 502
Combiner Power Query M functions, 745
combining
3D Map layers, 615
filters, 376
combo charts, creating, 591
comma delimited files (CSV), viewing data loss warning, 82
commands
Excel 2003 keyboard accelerators, list of, 106-113
on Quick Access Toolbar
removing, 64
selecting with keyboard accelerators, 88-89
comments
adding to formulas, 246
formatting, 37
printing, 685
Compact Form layout (pivot tables), 399-400
Comparer Power Query M functions, 745
comparing
dates with conditional formatting, 573-574
worksheets with Inquire, 511
comparison operators, list of, 237
COMPLEX() function, 301
CONCATENATE() function, 168, 210
CONCAT() function, 14, 168, 210
conditional calculations, 186-188
conditional formatting. See also data visualizations
copying, 584
finding cells from past 30 days, 580
finding cells from specific days of week, 580
finding cells within three days of today, 580
highlighting entire row, 580-581
highlighting every other row, 581-582
highlight cells rules, 570-571
duplicate/unique values, 574-575
Greater Than/Less Than/Equal rules, 571-573
text containing values, 575-576
pasting, 554
on ranges, 185
unique values in, 570
conditionally summing database records, 274
conditions
in custom number formats, 528
testing multiple
with AND() function, 238
with OR() function, 239
CONFIDENCE.NORM() function, 290
CONFIDENCE.T() function, 290
connecting worksheets, 303-304
creating links
with Alternate Drag-and-Drop menu, 307-309
with Paste Options menu, 306-307
by typing, 311
to unsaved workbooks, 311
suppressing Update Links dialog box, 314
updating links, 313
Consolidate dialog box, 389-390
constants, names for, 327
contextual tabs, viewing, 60-61
contrast (of pictures), adjusting, 656-657
CONVERT() function, 301
converting
formulas
to text, 143
to values, 309
Roman/Arabic numerals, 182
text dates to real dates, 199-200
text times to real times, 200-201
text to number/date, 229
copying
array formulas, 340
cells, keeping on Clipboard, 20-22
conditional formatting, 584
data in collapsed view, 368-369
formats, 553
pasting conditional formats, 554
formulas
with Ctrl+Enter key, 130
by double-clicking fill handle, 131
by dragging fill handle, 131
list of methods, 130
pivot tables for every Report Filter value, 429-430
sparklines, 637
copyrighted images, 653
corner of selection, moving to, 102
CORREL() function, 290
COS() function, 299
COSH() function, 299
COT() function, 299
COTH() function, 299
COUNTIF() function, 164
COUNTIFS() function, 164, 186-188
counting data with COUNT() and COUNTA() functions, 175-176
counting distinct in pivot tables, 436-438
COUPDAYBS() function, 285
COUPDAYS() function, 285
COUPDAYSNC() function, 285
COUPNCD() function, 285
COUPNUM() function, 285
COUPPCD() function, 285
COVARIANCE.P() function, 290
COVARIANCE.S() function, 290
Create PivotTable dialog box, 395
creating. See also building; entering
charts
changing options with plus icon, 590
combo charts, 591
deleting extraneous data, 589-590
histogram charts, 592
from Recommended Charts, 587-588
styling with paintbrush icon, 588-589
with templates, 598
troubleshooting chart positioning, 588
data bars, 560
drill-down hierarchies for Power BI Desktop, 700-702
frequency distributions, 592-593
links in worksheets
with Alternate Drag-and-Drop menu, 307-309
with Paste Options menu, 306-307
by typing, 311
to unsaved workbooks, 311
pivot table medians with DAX Measures, 440-443
pivot tables
from blank pivot table, 395
with Recommended PivotTable dialog box, 393-395
ranges of dates with array formulas, 338-339
scenarios, 457
summary reports, 458
table relationships for Data Model, 433-434
time functions in pivot tables with DAX Measures, 443-445
tours in 3D Maps, 618
criteria
dates or numbers as, 277
joining
criteria ranges
creating, 275
headings for, 272
cross-filtering visualizations for Power BI Desktop, 699
CSC() function, 299
CSCH() function, 299
Ctrl+Enter key, copying formulas, 130
CUMIMPT() function, 285
CUMPRINC() function, 285
currency formats, 522
current date/time, calculating, 195-196
current workbook, viewing info, 68
custom 3D Maps, 619
image preparation, 619
Custom AutoFilter dialog box, 383
custom headers/footers, inserting, 678-679
customizations (ribbon)
frequently asked questions, 79
resetting to original ribbon, 79
Customize Ribbon settings (Excel Options dialog box), 80
customizing. See also Excel Options dialog box
custom lists
editing, 84
sorting pivot tables by, 445-446
custom number formats, 525-526
colors in, 528
conditions in, 528
scientific notation in, 529
text/spacing in, 527
zones in, 526
custom tabs (ribbon), creating, 78
custom visualizations
for Power BI Desktop, importing, 702-703
cutting formulas versus copying, 142-143
D
dashboards in Power BI Desktop, 687
data
preparing in Excel, 688
preparing in Power BI, 691-693
visualizations
cross-filtering, 699
dashboards in Power View, 705-706
data
accessing with Power Query M functions, 738-743
cleaning with Flash Fill, 356-358. See also Power Query
copying in collapsed view, 368-369
counting with COUNT() and COUNTA() functions, 175-176
filtering
Advanced Filter command, 385-386
clearing filters, 377
combining filters, 376
formatting and copying results, 384-385
formulas in advanced filters, 386-387
identifying filtered columns, 376
ranges of values in advanced filters, 387
reapplying filters, 374
refreshing filters, 377
resizing drop-down menu, 377
selecting items in drop-down menu, 375-376
special filters by data type, 382-384
totaling results, 384
finding last row, 488
fitting to printed page, 675
formatting in collapsed view, 369-370
last data row, looping through, 491
loading
with legacy connectors, 345
loading and refreshing in Power Query, 351-352
missing data in sparklines, 637-638
moving to end of, 475
pasting into charts, 594
for Power BI Desktop
preparing in Excel, 688
preparing in Power BI, 691-693
profiling in Power Query, 355
refreshing in pivot tables, 402
selecting for charts, 588
sorting, 358
by color or icon, 359
left-to-right sorts, 360
troubleshooting, 362
subtotals
copying data in collapsed view, 368-369
of dates by month, 372
formatting data in collapsed view, 369-370
of multiple fields, 371
one-page summaries with, 366
removing, 370
sorting data in collapsed view, 366-367
transactional data
consolidating and adding unique values, 389-390
finding unique values, 387-389
pivot tables. See pivot tables
transforming in Power Query, 346-348
unpivoting in Power Query, 349-350
creating, 560
multiple colors, 586
for subset of cells, 562
database functions, 231, 272-274
conditionally summing database records, 274
creating criteria ranges, 275
criteria range headings, 272
criteria range miracle version, 277-278
dates or numbers as criteria, 277
joining criteria with AND, 275-276
joining criteria with OR, 276-277
list of, 236
returning all records, 275
returning single cell, 279
Data Import wizards, viewing legacy wizards, 82
data loss warning, viewing, 82
data manipulation shortcut keys, 97-101
Data Model, 432
benefits, 447
counting distinct in pivot tables, 436-438
creating pivot table medians, 440-443
creating table relationships, 433-434
creating time functions, 443-445
including filtered items in subtotals, 438-440
preparing data, 432
VLOOKUP function versus, 432
Data settings (Excel Options dialog box), 80
Data Table command
random scenario modeling, 453-455
two-variable what-if analysis, 451-452
Data view (Power BI Desktop), 691
data visualizations. See also conditional formatting
color scales, creating, 563-564
data bars, 558
creating, 560
multiple colors, 586
for subset of cells, 562
icon sets
mixing icons, 568
realigning numbers in, 566-567
DATEDIF() function, 166, 203-205
Cate Power Query M functions, 746-749
date stamps, shortcut keys for, 103
dates/times
comparing with conditional formatting, 573-574
converting text to, 229
as criteria, 277
in custom number formats, 528-529
Flash Fill and, 357
grouping
in AutoFilter, 85
limitations on, 190
Lotus 1-2-3 compatibility, 191, 446
by month, subtotals of, 372
in pivot tables, summaries of, 404-405
ranges, creating with array formulas, 338-339
time calculations, 198
date/time columns in pivot tables, disabling automatic grouping, 82
date/time formats, 523
date/time functions
converting
text dates to real dates, 199-200
text times to real times, 200-201
end of month, 206
extracting date/time portions, 196-197
grouping dates
time calculations, 198
Date and Time DAX functions, 714-715
DateTime Power Query M functions, 750-751
DateTimeZone Power Query M functions, 751-752
DATEVALUE() function, 166, 199-200
DAX functions
Logical, 721
Parent and Child, 726
DAX Measures
creating pivot table medians, 440-443
creating Time functions, 443-445
troubleshooting, 443
day of week, grouping dates by, 201-202
DAYS() function, 166, 199, 206
DAYS360() function, 166
DB() function, 285
DCOUNT() function, 236, 273-274
DCOUNTA() function, 236, 273-274
DDB() function, 285
debugging mode (macros), 496
DEC2BIN() function, 301
DEC2HEX() function, 301
DEC2OCT() function, 301
DECIMAL() function, 301
decimal places in custom number formats, 527-528
decimal points in adding machine setting, enabling, 86
decision-making with IF() function, 237-242
default chart template, changing, 598
default font, changing, 548-549
defaults for pivot tables, setting, 82, 413-415
defining
multiple names from labels, 322-324
with New Name dialog box, 317-318
worksheet-level scope, 319-320
ranges as tables, 132
DEGREES() function, 299
deleting. See removing
delimiters
splitting in Power Query, 353-354
DELTA() function, 301
dependents, viewing, 147
Developer tab, viewing, 60
DEVSQ() function, 291
DGET() function, 236, 274, 279
Diagram view, creating table relationships, 434
dialog boxes
legacy dialog boxes, selecting in, 92
direct precedents, viewing, 146-147
disabling
automatic grouping of date/time columns in pivot tables, 82
DISC() function, 285
distinct counts in pivot tables, 436-438
division remainder, finding, 184-185
documenting worksheets, 261-262
Document Inspector, 69
DOLLAR() function, 168
DOLLARDE() function, 285
DOLLARFR() function, 285
double-clicking fill handle, copying formulas by, 131
dragging fill handle, copying formulas by, 131
drawing tools, new features, 19
drill-down hierarchies for Power BI Desktop, 700-702
drop-down lists, navigating with keyboard accelerators, 90
drop-down menu (Filter)
resizing, 377
drop zones (pivot tables), 400-401
DSTDEV() function, 236, 273-274
DSTDEVP() function, 236, 273-274
duplicate values, conditional formatting, 574-575
Duration Power Query M functions, 752-753
DURATION() function, 286
DVARP() function, 236, 273-274
E
Ease of Access settings (Excel Options dialog box), 80-82
EDATE() function, 166
Edit Default Layout dialog box, 13, 413-415
editing. See also adjusting pictures; changing
custom lists, 84
data, shortcut keys for, 97-101
preventing, 69
Edit Links dialog box, 313-314
EFFECT() function, 286
effects in themes, 546
elapsed time, calculating, 203-206
embedding custom visualizations as images, 600-601
enabling. See also viewing
adding machine setting, 86
AutoSave, 82
LinkedIn features, 81
macro settings, 466
ENCODEURL() function, 283
end of month, calculating, 206
engineering functions, list of, 300-302
entering. See also building; creating
data, shortcut keys for, 97-101
Enter mode (formula box), 579
Equation Editor, 508
equations
solving, troubleshooting, 508
ERF() function, 301
ERFC() function, 301
error cells, ignoring with AGGREGATE() function, 172-175
error checking with IFERROR() and IFNA() function, 242-244
error messages
in functions, 152
Error Power Query M functions, 753
error values, replacing when printing, 685
ERROR.TYPE() function, 232
errors
AutoSum errors, fixing in macros, 485-486
magnitude of, determining with ABS() function, 182-183
etiquette for shared workbooks, 11
Evaluate Formula dialog box, 506-507
evaluating
EVEN() function, 164
Evolutionary engine (Solver), 463
EXACT() function, 169, 226-227
exact values, finding, 251-253
Excel
as calculator, 150
feature changes, requesting, 20, 415
Excel 2003 keyboard accelerators, 104-105
Excel 2019
extended functionality, 4
keyboard accelerators
accessing slicers, 91
backing up a menu level, 90
navigating drop-down lists, 90
selecting gallery options, 89-90
selecting in legacy dialog boxes, 92
selecting ribbon commands, 88-89
troubleshooting, 90
new features
accessibility improvements, 26-28
copied cells on Clipboard, 20-22
disabling CSV file warning, 25-26
formatting superscripts/subscripts, 24-25
Home screen, 28
inking tools, 19
pivot table improvements, 12-14
unselecting cells, 24
Excel Options dialog box, 79
AutoRecover settings, 83
customizing Quick Access Toolbar, 64-66
image size settings, 83
protected mode for Internet-originated files, 83
settings categories, 80
submitting ideas for new settings, 81
Trusted Document settings, 84
expanding formula bar, 71. See also resizing
EXP() function, 164
EXPON.DIST() function, 291
exponents, calculating, 186
Export command (Backstage), 67
exporting
worksheets as PDF, 686
Expression Power Query M functions, 753
extended functionality in Excel 2019, 4
extended selection shortcut keys, 96-97
external workbooks, links to, 310
extracting
unique values with formulas, 48-50
F
F.DIST() function, 291
F.DIST.RT() function, 291
F4 key, toggling references, 123-126
F6 Loop, 90
F9 key, evaluating formulas, 149-150
FACT() function, 164
FACTDOUBLE() function, 164
FALSE() function, 231, 251-253
feature changes, requesting, 20, 415
Feedback command (Backstage), 67
Fields panel (Power BI Desktop), 690
fields (pivot tables)
file location for saving workbooks, changing default, 84
File menu
clearing recent workbooks list, 68
closing, 67
Document Inspector, 69
marking workbook as final, 69
recovering unsaved workbooks, 67
viewing current workbook info, 68
changing page setup settings, 683
changing printer properties, 683
selecting printer, 682
selecting what to print, 682-683
file names, browsing in macros, 486-487
file size of pictures, reducing, 661
files. See workbooks
fill handle, copying formulas, 131
Filter. See also AutoFilter
clearing filters, 377
combining filters, 376
formatting and copying results, 384-385
identifying filtered columns, 376
reapplying filters, 374
refreshing filters, 377
resizing drop-down menu, 377
selecting items in drop-down menu, 375-376
special filters by data type, 382-384
totaling results, 384
filtered rows, ignoring with AGGREGATE() function, 172-175
filtering
data
Advanced Filter command, 385-386
advanced filters in macros, 499-500
AutoFilter in macros, 501
clearing filters, 377
combining filters, 376
formatting and copying results, 384-385
formulas in advanced filters, 386-387
identifying filtered columns, 376
ranges of values in advanced filters, 387
reapplying filters, 374
refreshing filters, 377
resizing drop-down menu, 377
selecting items in drop-down menu, 375-376
special filters by data type, 382-384
with SUBTOTAL() function, 179
totaling results, 384
pivot tables
clearing filters, 419
copying for every Report Filter value, 429-430
with Date Filter fly-out, 421-422
dates with timeline feature, 427
including filtered items in subtotals, 438-440
with Label Filter fly-out, 420-421
with row label filters, 417-419
troubleshooting, 420
visualizations for Power BI Desktop, cross-filtering, 699
financial data, viewing with waterfall chart, 593-594
financial functions, list of, 284-288
FINDB() function, 169
finding
cells
from past 30 days, 580
from specific days of week, 580
within three days of today, 580
Edit Links dialog box, 314
functions
with AutoComplete feature, 153
with Insert Function dialog box, 154
hidden content, 69
last data row, 488
last record, 475
number of characters in cell, 220-221
position of matching value, 254-255
values based on ranges, 249-250
F.INV() function, 291
F.INV.RT() function, 291
FISHER() function, 291
FISHERINV() function, 291
fitting data to printed page, 675
five-icon sets, 565
FIXED() function, 169
fixing. See troubleshooting
Flash Fill, 356
dates and, 357
numbers and, 356
Flat Map option (3D Maps), 616
FLOOR() function, 164, 177-178
FLOOR.MATH() function, 164, 177-178
flow control in macros
If-End-If, 491
folders
as trusted locations, 85
workbooks in, appending worksheets from, 354-355
font typeface, changing, 531
fonts
default font, changing, 548-549
in themes, choosing, 546
automatic, inserting, 678
different in same document, 680
pictures, inserting, 679
scaling, 681
FORECAST() function, 291
FORECAST.ETS() function, 292
FORECAST.ETS.CONFINT() function, 292
FORECAST.ETS.SEASONALITY() function, 292
FORECAST.ETS.STAT() function, 292
FORECAST.LINEAR() function, 292
Format Cells dialog box, 518-519
numeric formats, changing, 521
viewing, 193
formatting. See also conditional formatting; presentation formatting
cells
justifying text in ranges, 550-551
in pivot tables, 402
with strikethrough, 102
comments, 37
conditional formatting on ranges, 185
data
dates/times, 145, 191-194, 357
invoice register example (macros), 474
finding last record, 475
recording in blank workbook, 475-477
variable number of rows, 475
mail merge example (macros), 467-469
numbers
quick formatting, 576
ranges, 53
worksheets
aligning cells, 530
bold/italic/underline, 532
borders, 533
changing numeric formats, 519-521
currency formats, 522
custom number formats, 525-529
date/time formats, 523
font typeface, 531
with Format Cells dialog box, 518-519
numeric formatting with thousands separators, 522
resizing columns/rows, 535-536
ZIP code/telephone/SSN formats, 524-525
formula bar, expanding, 71
formulas
3D formulas
building with mouse, 330
spearing through worksheets, 328-330
=+ in, 129
adding comments to, 246
array formulas
copying, 340
creating ranges of dates with, 338-339
building by typing, 311
calculating in slow motion, 506-507
in conditional formatting, 578-579
finding cells from past 30 days, 580
finding cells from specific days of week, 580
finding cells within three days of today, 580
highlighting entire row, 580-581
highlighting every other row, 581-582
converting to values, 309
copying
with Ctrl+Enter key, 130
by double-clicking fill handle, 131
by dragging fill handle, 131
list of methods, 130
DAX Measures, troubleshooting, 443
extracting unique values with, 48-50
implicit intersection, 325
multiple formulas, combining, 332-335
operators in
list of, 135
protecting, 509
R1C1 style
fixing AutoSum errors in, 485-486
range of, transposing, 335-338
references to previous worksheet, 331-332
relative referencing, 120
spearing, 172
speed of, 260
with Stocks and Geography data types, 6-7
SUM function and, 119
syntax, 119
converting to, 143
toggling references with F4, 123-126
troubleshooting
dependents, 147
direct precedents, 146
Evaluate Formula dialog box, 149
Excel as calculator, 150
highlighting formula cells, 146
implicit intersection, 126
Show Formulas mode, 145
Trace Error feature, 140
Watch Window feature, 148
two-way lookups with intersection operator, 324
values versus, 118
Formulas settings (Excel Options dialog box), 80
Formulas tab, finding functions on, 152-153
FORMULATEXT() function, 234, 261-262
four-icon sets, 565
frequency distributions, creating, 592-593
FREQUENCY() function, 292
from-scratch macro example, 487
deleting records in loop, 494-495
finding last data row, 488
If-End-If flow control, 491
looping with FinalRow, 491
range references, 490
Select Case flow control, 491-492
testing each record in loop, 492-493
F.TEST() function, 291
full-screen Print Preview, 672
Function Arguments dialog box, 154-157
Function Power Query M functions, 753
functions
array functions, 38
extracting unique values with formulas, 48-50
filtering with formulas, 46-47
formulas with multiple answers, 39-42
generating random numbers, 53-54
generating sequence of numbers, 51-52
AutoComplete feature, 153
from top of column, 161
calculation functions, new features, 14-15
database functions, 231, 272-274
conditionally summing database records, 274
creating criteria ranges, 275
criteria range headings, 272
criteria range miracle version, 277-278
dates or numbers as criteria, 277
joining criteria with AND, 275-276
joining criteria with OR, 276-277
list of, 236
returning all records, 275
returning single cell, 279
date/time functions
converting text dates to real dates, 199-200
converting text times to real times, 200-201
end of month, 206
extracting date/time portions, 196-197
grouping dates by day of week, 201-202
grouping dates into weeks, 202-203
time calculations, 198
DAX equivalents, 709-713. See also DAX funtions
engineering functions, list of, 300-302
error messages in, 152
financial functions, list of, 284-288
Formulas tab, finding functions, 152-153
help with
Function Arguments dialog box, 154-157
ToolTips, 155
information functions, 231
adding comments to formulas, 246
marking formula cells, 244-245
returning worksheet name, 247
testing value types, 245
Insert Function dialog box, 154
linked data types, 231, 279-281
logical functions, 231
nesting IF() functions, 239-242
reversing values, 242
testing multiple conditions, 238-239
lookup functions, 231
building and evaluating cell references, 266-267
documenting worksheets, 261-262
finding position of matching value, 254-255
finding values based on ranges, 249-250
Fuzzy Lookup add-in, 281
retrieving cells from pivot tables, 270-272
simple lookups with CHOOSE, 248-249
math functions
adding numbers, 171-172, 178-179
available in AGGREGATE(), 174-175
conditional calculations, 186-188
converting Roman/Arabic numerals, 182
finding division remainder, 184-185
generating random numbers, 180-182
greatest common denominator, 183-184
ignoring error cells/filtered rows, 172-175
least common multiple, 183-184
square roots and exponents, 186
matrix functions, list of, 300
nesting, 50
Power Query M equivalents, 736-738. See also Power Query M functions
statistical functions, list of, 289-298
text functions
converting text to number/date, 229
finding characters in cell, 221-224
finding number of characters in cell, 220-221
formatting numbers as text, 227-228
removing leading/trailing spaces, 212-215
returning original text, 229
time functions, creating with DAX Measures, 443-445
trigonometry functions, list of, 298-299
web functions, list of, 283
funnel charts, 15
Funnel icon, deleting extraneous data from charts, 589-590
Fuzzy Lookup add-in, 281
FV() function, 286
FVSCHEDULE() function, 286
G
GAMMA() function, 292
GAMMA.DIST() function, 292
GAMMA.INV() function, 292
GAMMALN() function, 292
GAUSS() function, 292
General settings (Excel Options dialog box), 80
generating random numbers, 180-182
GEOMEAN() function, 292
GESTEP() function, 301
GetOpenFileName in macros, 486-487
GETPIVOTDATA() function, 234, 270-272
GetSaveAsFileName in macros, 486-487
Go To Special dialog box, 368
greatest common denominator, 183-184
green bar formatting, creating, 185
green triangles in numbers as text, removing, 86
GRG engine (Solver), 463
gridlines
color, changing, 85
printing, 685
Group and Outline buttons (subtotals), 365
grouping
dates
in AutoFilter, 85
pictures, 664
groups of sparklines, creating, 626-628
GROWTH() function, 293
H
HARMEAN() function, 293
automatic, inserting, 678
different in same document, 680
pictures, inserting, 679
scaling, 681
headings
column/row headings, printing, 685
for criteria ranges, 272
repeating on each page, 674
help
for Excel Options dialog box settings, 80-81
with functions
Function Arguments dialog box, 154-157
ToolTips, 155
in VBA, accessing, 482
HEX2BIN() function, 301
HEX2DEC() function, 301
HEX2OCT() function, 301
hidden content, finding, 69
hidden data in sparklines, 637-638
Hide command with SUBTOTAL() function, 179
hiding
all pictures, 663
icons in icon sets, 568
interface, 85
hierarchical charts in SmartArt, 644-646
hierarchies, creating for Power BI Desktop, 700-702
highlight cells rules, 570-571
duplicate/unique values, 574-575
Greater Than/Less Than/Equal rules, 571-573
text containing values, 575-576
highlighting
formula cells, 146
rows with conditional formatting, 580-582
histogram charts, creating, 592
HLOOKUP() function, 234
Home command (Backstage), 66
Home screen, new features, 28
Home tab
icons, formatting with, 517-518
numeric formats, changing, 519-521
HYPERLINK() function, 234, 267-268
hyperlinks
cells with, selecting, 268
HYPGEOM.DIST() function, 293
I
icon sets, 557
mixing icons, 568
realigning numbers in, 566-567
filtering by, 48
on Home tab
changing numeric formats, 519-521
on ribbon, size of, 78
Ideas artificial intelligence tool, 5, 394
ideas for new settings, submitting, 81
If-End-If flow control, 491
IFERROR() function, 232, 242-244
IFS() function, 14, 232, 239-242
ignoring error cells/filtered rows with AGGREGATE() function, 172-175
IMABS() function, 301
images
adding to SmartArt, 644
embedding custom visualizations as, 600-601
preparing for custom 3D maps, 619
resizing, 83
IMAGINARY() function, 301
IMARGUMENT() function, 301
IMCONJUGATE() function, 302
IMCOS() function, 302
IMCOSH() function, 302
IMCOT() function, 302
IMCSC() function, 302
IMCSCH() function, 302
IMDIV() function, 302
IMEXP() function, 302
IMLN() function, 302
IMLOG2() function, 302
IMLOG10() function, 302
implicit intersection, 41-42, 126, 325
importing
custom visualizations for Power BI Desktop, 702-703
data
into Power BI Desktop, 688-689
viewing legacy Data Import wizards, 82
IMPOWER() function, 302
IMPRODUCT() function, 302
IMREAL() function, 302
IMSEC() function, 302
IMSECH() function, 302
IMSIN() function, 302
IMSINH() function, 302
IMSQRT() function, 302
IMSUB() function, 302
IMSUM() function, 302
IMTAN() function, 302
indenting macro code, 497
INDEX() function, 234
as alternative to OFFSET() function, 265
INDIRECT() function, 234, 266-267, 325
Info command (Backstage), 67-68
INFO() function, 232
Information DAX functions, 720-721
information functions, 231
adding comments to formulas, 246
marking formula cells, 244-245
returning worksheet name, 247
testing value types, 245
inking tools, new features, 19
Inquire, 511
Insert Chart dialog box, 587-588
Insert Function dialog box, 154
inserting. See also adding
automatic headers/footers, 678
cells, shortcut keys and, 21
chart data by pasting, 594
columns in Power Query, 350
custom headers/footers, 678-679
manual page breaks, 676
pictures, 651
from computer, 652
in headers/footers, 679
multiple pictures, 652
online pictures/clip art, 652-653
screen clippings, 662
symbols in cells, 507
worksheets in workbooks, 70
Insights artificial intelligence tool, 5, 394
installing Solver, 461
interactive reports in Power BI Desktop, visualizations
cross-filtering, 699
drill-down hierarchies, 700-702
INTERCEPT() function, 293
interface
clearing recent workbooks list, 68
closing, 67
Document Inspector, 69
marking workbook as final, 69
recovering unsaved workbooks, 67
viewing current workbook info, 68
hiding, 85
Quick Access Toolbar, 63
removing commands from, 64
touch mode, 69
ribbon
creating custom tabs, 78
exporting customizations, 78-79
Format Painter, 55
frequently asked customization questions, 79
galleries, 57
icon size, 78
resetting to original, 79
scrolling through tabs, 58
selecting commands with keyboard accelerators, 88-89
viewing contextual tabs, 60-61
viewing Developer tab, 60
status bar, viewing statistics in, 73
Internet-originated files, protected mode for, 83
intersection operator
implicit intersection, 325
two-way lookups with, 324
INT() function, 164
INTRATE() function, 286
invoice register formatting example (macros), 474
finding last record, 475
recording in blank workbook, 475-477
variable number of rows, 475
IPMT() function, 286
IRR() function, 286
IS functions, 245
ISBLANK() function, 232
ISERR() function, 233
ISERROR() function, 233
ISEVEN() function, 233
ISFORMULA() function, 233, 244-245
ISLOGICAL() function, 233
ISNA() function, 233
ISNONTEXT() function, 233
ISNUMBER() function, 233
ISODD() function, 233
ISOWEEKNUM() function, 167, 202-203
ISPMT() function, 286
ISREF() function, 233
ISTEXT() function, 233
italic format, 532
J
JavaScript User Defined Functions, 465
joining
criteria
tables with Data Model
creating relationships, 433-434
preparing data, 432
justifying text in ranges, 550-551
K
keyboard accelerators. See also shortcut keys
Excel 2019
accessing slicers, 91
backing up a menu level, 90
navigating drop-down lists, 90
selecting gallery options, 89-90
selecting in legacy dialog boxes, 92
selecting ribbon commands, 88-89
troubleshooting, 90
KURT() function, 293
L
labels
on sparklines, adding, 634-637
landscape orientation, 673
Language settings (Excel Options dialog box), 80
LARGE() function, 293
last data row
finding, 488
looping through, 491
last record, finding, 475
layers in 3D Maps, combining, 615
leading spaces, removing, 212-215
leading zeros, adding to numbers, 228-229
leap day, Lotus 1-2-3 compatibility and, 191, 446
least common multiple, 183-184
LEFTB() function, 169
left-to-right sorts, 360
legacy connectors, loading data with, 345
legacy Data Import wizards, viewing, 82
legacy dialog boxes, selecting in with keyboard accelerators, 92
legal issues, copyrighted images, 653
LENB() function, 169
line charts, as sparklines, 623
lines between 3D Map points, animating, 617
Lines Power Query M functions, 754
LINEST() function, 293
linked data types, 6-7, 231, 279-281
LinkedIn, enabling features, 81
links
in worksheets
creating by typing, 311
creating in Trust Center, 312-313
creating to unsaved workbooks, 311
creating with Alternate Drag-and-Drop menu, 307-309
creating with Paste Options menu, 306-307
suppressing Update Links dialog box, 314
updating links to closed workbooks, 313
updating links to missing linked workbooks, 313
to external workbooks, 310
Links tab (Trust Center), 312-313
List Averages Power Query M functions, 754
List Generators Power Query M functions, 755
List Information Power Query M functions, 755
List Membership Power Query M functions, 756
List Numerics Power Query M functions, 756
List Ordering Power Query M functions, 756-757
List Selection Power Query M functions, 757-758
List Set Operations Power Query M functions, 758
List Transformation Power Query M functions, 758-759
lists
custom
editing, 84
sorting pivot tables by, 445-446
LN() function, 299
loading data
with legacy connectors, 345
locations in 3D Maps, troubleshooting, 613
LOG() function, 299
LOG10() function, 299
LOGEST() function, 293
Logical DAX functions, 721
logical functions, 231
nesting IF() functions, 239-242
reversing values, 242
testing multiple conditions, 238-239
Logical Power Query M functions, 759
logical values, reversing, 242
LOGNORM.DIST() function, 293
LOGNORM.INV() function, 294
LOOKUP() function, 234-235, 260-261
lookup functions, 231
building and evaluating cell references, 266-267
documenting worksheets, 261-262
finding position of matching value, 254-255
finding values based on ranges, 249-250
Fuzzy Lookup add-in, 281
retrieving cells from pivot tables, 270-272
simple lookups with CHOOSE, 248-249
lookups, two-way lookups with intersection operator, 324
looping
with FinalRow, 491
Lotus 1-2-3 compatibility with dates/times, 191, 446
LOWER() function, 169, 211-212
LP Solver engine (Solver), 463
M
M language, 351
macros. See also VBA
assigning shortcut keys, 472
combination macro example, 497-498
AutoFilter, 501
selecting visible cells, 502
from-scratch example, 487
deleting records in loop, 494-495
finding last data row, 488
If-End-If flow control, 491
looping with FinalRow, 491
range references, 490
Select Case flow control, 491-492
testing each record in loop, 492-493
indenting code, 497
invoice register formatting example, 474
finding last record, 475
recording in blank workbook, 475-477
variable number of rows, 475
mail merge formatting example, 467-469
R1C1 style
fixing AutoSum errors in, 485-486
examining code from, 483
relative recording, 467, 470-471
security settings, 466
troubleshooting, debugging mode, 496
undo and, 474
magnitude of error, determining with ABS() function, 182-183
mail merge formatting example (macros), 467-469
manual page breaks
automatic breaks versus, 676
inserting, 676
moving, 677
removing, 677
maps. See 3D Maps
marking formula cells, 244-245
MATCH() function, 235
finding position of matching value, 254-255
math functions
adding numbers, 171-172, 178-179
available in AGGREGATE(), 174-175
conditional calculations, 186-188
converting Roman/Arabic numerals, 182
finding division remainder, 184-185
generating random numbers, 180-182
greatest common denominator, 183-184
ignoring error cells/filtered rows, 172-175
least common multiple, 183-184
square roots and exponents, 186
Math and Trig DAX functions, 722-725
matrix functions, list of, 300
MAX() function, 294
MAXA() function, 294
MAXIFS() function, 14, 164, 186-188
MDETERM() function, 300
MDURATION() function, 286
Measures (DAX)
creating pivot table medians, 440-443
creating time functions, 443-445
troubleshooting, 443
MEDIAN() function, 294
medians in pivot tables, creating with DAX Measures, 440-443
menu levels, backing up with keyboard accelerators, 90
methods (VBA), 479
MIDB() function, 169
MIN() function, 294
MINA() function, 294
MINIFS() function, 14, 165, 186-188
mini toolbar, formatting text, 70-71
MINUTE() function, 167, 196-197
MINVERSE() function, 300
miracle version of criteria ranges, 277-278
MIRR() function, 286
missing data in sparklines, 637-638
missing linked workbooks, updating links to, 313
missing pivot table tools, troubleshooting, 403
mixing icons in icon sets, 568
MMULT() function, 300
models
Monte Carlo analysis, 464
adding multiple scenarios, 458
creating scenarios, 457
creating summary reports, 458
MODE.MULT() function, 294
MODE.SNGL() function, 294
Monte Carlo analysis, 464
MONTH() function, 167, 196-197
months
daily dates by, 372
end of month calculations, 206
More commands (ribbon), 59
mouse
building 3D formulas, 330
mouse method (entering formulas), 127-128
moving
to corner of selection, shortcut keys for, 102
to end of data, 475
manual page breaks, 677
ToolTips, 155
to top/bottom of data, shortcut keys for, 101
MULTINOMIAL() function, 165
multiple answers, formulas with, 39-42
multiple colors in data bars, 586
multiple columns, VLOOKUP function with, 15
multiple conditions, testing
with AND() function, 238
with OR() function, 239
multiple fields, subtotals of, 371
multiple formulas, combining, 332-335
multiple names, defining, 322-324
multiple pictures, inserting, 652
multiple pivot tables, slicers for, 425-426
multiple scenarios, adding, 458
MUNIT() function, 300
N
name box, resizing, 319
Name dialog box, navigation with, 318-319
name of worksheet, returning with CELL() function, 247
named ranges in Scenario Manager, 455
names
advantages of, 315
avoiding absolute references, 326
multiple names from labels, 322-324
with New Name dialog box, 317-318
worksheet-level scope, 319-320
holding values, 327
implicit intersection, 325
navigation with Name dialog box, 318-319
references with worksheet-level scope, 320
of tables, 316
two-way lookups with, 324
navigating
drop-down lists with keyboard accelerators, 90
multiple worksheets in workbooks, 70
negative data bars, 559
negative time, allowing, 85
NEGBINOM.DIST() function, 294
nesting
parentheses example (order of operations), 137-138
NETWORKDAYS() function, 167, 206-208
NETWORKDAYS.INTL() function, 167, 208-209
New command (Backstage), 67
new features in Excel 2019
accessibility improvements, 26-28
copied cells on Clipboard, 20-22
disabling CSV file warning, 25-26
in Excel Options dialog box, 81-82
formatting superscripts/subscripts, 24-25
Home screen, 28
inking tools, 19
pivot table improvements, 12-14
etiquette for, 11
submitting ideas for, 81
unselecting cells, 24
new features in Office 365
array functions, 38
extracting unique values with formulas, 48-50
filtering with formulas, 46-47
formulas with multiple answers, 39-42
generating random numbers, 53-54
generating sequence of numbers, 51-52
learning about, 54
Smart Lookup feature, 38
Workbook Statistics panel, 37
New Name dialog box
names for holding values, 327
NOMINAL() function, 286
non-breaking spaces, 215
NORM.DIST() function, 294
NORM.INV() function, 294
NORM.S.DIST() function, 295
NORM.S.INV() function, 295
NPER() function, 286
NPV() function, 287
Number Bytes Power Query M functions, 760
Number Constants Power Query M functions, 760
Number Conversion and Formatting Power Query M functions, 761
Number Information Power Query M functions, 761
Number Operations Power Query M functions, 762
Number Random Power Query M functions, 762
Number Rounding Power Query M functions, 763
numbers
adding
with SUBTOTAL() function, 178-179
converting
Roman/Arabic numerals, 182
text to, 229
as criteria, 277
filtering by, 382
Flash Fill and, 356
formatting in pivot tables, 401-402
in icon sets, realigning, 566-567
random numbers, generating, 53-54
sequences of, generating, 51-52
serial numbers for dates/times, 189
removing green triangles, 86
Number Trigonometry Power Query M functions, 763
NUMBERVALUE() function, 169
numeric formats
currency, 522
colors in, 528
conditions in, 528
scientific notation in, 529
text/spacing in, 527
zones in, 526
date/time, 523
with thousands separators, 522
ZIP codes/telephone numbers/SSNs, 524-525
O
object variables in VBA code, 483-484
objects (VBA), 479
OCT2BIN() function, 302
OCT2DEC() function, 302
OCT2HEX() function, 302
ODD() function, 165
ODDFPRICE() function, 287
ODDFYIELD() function, 287
ODDLPRICE() function, 287
ODDLYIELD() function, 287
Artificial Intelligence feature, 5-6
custom visualization support, 599-601
new features
learning about, 54
Smart Lookup feature, 38
Workbook Statistics panel, 37
Stocks and Geography data types, 6-7
Office 2003 Access Key mode, 105
Office 2019, support period for, 4
Office Intelligent Services, 81
OFFSET() function, 235, 263-265
OLAP models, limitations, 445-447
one-page summaries with subtotals, viewing, 366
online, saving workbooks, 10-11
online pictures, 639
Open command (Backstage), 67
opening right-click menus, shortcut keys for, 102
operators in formulas
list of, 135
Options command (Backstage), 67
Options dialog box. See Excel Options dialog box
order of operations in formulas, 136
addition and multiplication example, 137
nesting parentheses example, 137-138
unary minus example, 136
OR() function, 232, 239, 276-277
organizational charts in SmartArt, 644-646
orientation, adjusting, 673
original text, returning, 229
overview of worksheets, viewing, 72
P
Page Break preview mode, 73
page breaks
automatic, 675
manual
automatic versus, 676
inserting, 676
moving, 677
removing, 677
page numbers
on printed documents, troubleshooting, 672
setting first page number, 685
page setup settings, changing, 683
paintbrush icon, applying chart styles, 588-589
panes in 3D Maps, resizing, 616
paper size, adjusting, 673
Parent and Child DAX functions, 726
parentheses in formulas, 137-138
Pareto charts, creating, 592-593
parts of speech analogy (VBA), 478-479
collections, 479
examining recorded code, 483
objects and methods, 479
Paste Options menu, creating links with, 306-307
Paste Special dialog box, creating scatter charts, 595-596
pasting
conditional formats, 554
data into charts, 594
PDF, exporting as, 686
PDURATION() function, 287
PEARSON() function, 295
PERCENTILE.EXC() function, 295
PERCENTILE.INC() function, 295
PERCENTRANK.EXC() function, 295
PERCENTRANK.INC() function, 295
performance, troubleshooting, 663
PERMUT() function, 295
PERMUTATIONA() function, 295
PHI() function, 295
PHONETIC() function, 169
photos in 3D Maps, adding to points, 614
PI() function, 165
pictures, 639
adjusting, 654
artistic effects, 659
Picture Styles gallery, 657-659
reducing file size, 661
grouping, 664
in headers/footers, inserting, 679
hiding all, 663
inserting, 651
from computer, 652
multiple pictures, 652
online pictures/clip art, 652-653
screen clippings, 662
transparency, 653
Picture Styles gallery, 657-659
pie charts, changing to bar of pie charts, 596-597
pivot tables. See also Power Pivot
additional information, 416
calculations and roll-ups, 403
adding outside pivot table, 405-406
changing field calculation, 406-409
running totals and rankings, 409-410
conditional formatting for, 584-585
creating
from blank pivot table, 395
with Recommended PivotTable dialog box, 393-395
Data Model benefits, 447
Data Model limitations, 445-447
date/time columns, disabling automatic grouping, 82
defaults, setting, 82, 413-415
filtering
clearing filters, 419
copying for every Report Filter value, 429-430
with Date Filter fly-out, 421-422
dates with timeline feature, 427
including filtered items in subtotals, 438-440
with Label Filter fly-out, 420-421
with row label filters, 417-419
troubleshooting, 420
medians in, creating with DAX Measures, 440-443
single cell formatting, 402
Time functions, creating with DAX Measures, 443-445
troubleshooting
changing pivot table, 403
missing tools, 403
rankings, 411
refreshing data, 402
viewing contextual tabs, 60
plus icon, changing chart options, 590
Point mode (formula box), 579
points in 3D Maps
adding photos, 614
animating line between, 617
POISSON.DIST() function, 295
portrait orientation, 673
position of matching value, finding, 254-255
Power BI Desktop, 687
data
preparing in Excel, 688
preparing in Power BI, 691-693
Power View and, 706
signing in, 687
visualizations
cross-filtering, 699
custom, Office 365 support, 599-601
drill-down hierarchies, 700-702
Power Map add-in. See 3D Maps
Power Pivot, 431. See also Data Model
benefits, 447
resources for information, 447
sorting by custom lists, 445-446
columns, adding, 350
data
loading and refreshing, 351-352
delimiters, splitting to new rows, 353-354
new features, 355
resources for information, 447
workflow, establishing, 344
worksheets, appending, 352-355
Power Query M functions
Combiner, 745
Comparer, 745
Error, 753
Expression, 753
Function, 753
Lines, 754
List Averages, 754
List Generators, 755
List Information, 755
List Membership, 756
List Numerics, 756
List Set Operations, 758
Logical, 759
Number Bytes, 760
Number Constants, 760
Number Conversion and Formatting, 761
Number Information, 761
Number Operations, 762
Number Random, 762
Number Rounding, 763
Number Trigonometry, 763
Record Information, 764
Record Selection, 764
Record Serialization, 764
Record Transformation, 765
Replacer, 765
Table Column Cperations, 766-767
Table Conversion, 768
Table Information, 768
Text Comparison, 772
Text Extraction, 770
Text Information, 770
Text Membership, 771
Text Modification, 771
URI, 775
Value, 775
PPMT() function, 287
precedents, viewing direct, 146-147
preparing data
for Data Model, 432
for Power BI Desktop, 688, 691-693
presentation formatting
pictures
adjusting with ribbon tab, 654-661
screen clippings, 662
selecting and arranging, 662-664
shapes
changing, 648
adding images to, 644
organizational and hierarchical charts, 644-646
preventing editing, 69
previewing printed document, 670-672
previous worksheet, referencing in formulas, 331-332
PRICE() function, 287
PRICEDISC() function, 287
PRICEMAT() function, 287
print area, selecting, 675
Print command (Backstage), 67
printer
changing properties, 683
selecting, 682
printing workbooks
automatic page breaks, 675
centering reports, 685
column/row headings, 685
comments, 685
error values, 685
finding print settings, 668-670
fitting data to page, 675
gridlines, 685
orientation, 673
Page Layout view, 684
page number settings, 685
paper size, 673
as PDF, 686
previewing printed document, 670-672
repeating headings, 674
selecting print area, 675
troubleshooting page numbers, 672
PROB() function, 296
PRODUCT() function, 165
profiling data, 355
Project Explorer pane (VBE), 477
Proofing settings (Excel Options dialog box), 80
PROPER() function, 169, 211-212
Properties pane (VBE), 477
protected mode for Internet-originated files, 83
protecting
formula cells, 509
workbooks, marking as final, 69
Publish command (Backstage), 67
publishing to Power BI Desktop, 704-705
PV() function, 287
Q
QUARTILE.EXC() function, 296
QUARTILE.INC() function, 296
queries. See Power Query
question mark (?) wildcard character, 188
Quick Access Toolbar (QAT), 63
commands
removing, 64
settings (Excel Options dialog box), 80
touch mode, 69
quick formatting, 576
QUOTIENT() function, 165
R
R1C1 style
fixing AutoSum errors in, 485-486
RADIANS() function, 299
RANDBETWEEN() function, 165, 180-182
random numbers, generating, 53-54, 180-182
random scenario modeling, 453-455
random sorts, 45
Range property, syntax, 493
ranges
conditional formatting, 185
criteria ranges
creating, 275
headings for, 272
of dates, creating with array formulas, 338-339
formatting, 53
of formulas, transposing, 335-338
names
advantages of, 315
avoiding absolute references, 326
defining multiple names from labels, 322-324
defining with New Name dialog box, 317-318
implicit intersection, 325
navigation with Name dialog box, 318-319
in Scenario Manager, 455
two-way lookups with, 324
worksheet-level scope, 319-320
rectangular ranges, toggling references with F4, 125-126
referencing in macros, 490
selecting, shortcut keys for, 102
as tables, defining, 132
rankings in pivot tables, 409-410
RATE() function, 288
realigning numbers in icons sets, 566-567
reapplying filters, 374
rearranging pivot tables, 400-401
RECEIVED() function, 288
Recent File List command (Backstage), 67
recent workbooks list
changing number to view, 84
clearing, 68
Recommended PivotTable dialog box, 393-395
Record Information Power Query M functions, 764
examining code from, 483
relative recording, 467, 470-471
steps for mail merge formatting example, 471-473
records
database
conditionally summing, 274
returning all, 275
unique records, advanced filters in macros, 499-500
Record Selection Power Query M functions, 764
Record Serialization Power Query M functions, 764
Record Transformation Power Query M functions, 765
recovering unsaved workbooks, 67
rectangular ranges, toggling references with F4, 125-126
reducing picture file size, 661
reference functions. See lookup functions
references
avoiding with names, 326
in links to external workbooks, 310
building and evaluating, 266-267
to names with worksheet-level scope, 320
to previous worksheet in formulas, 331-332
to ranges in macros, 490
relative referencing, 120
refreshing
data
in pivot tables, 402
filtered pivot tables, troubleshooting, 420
filters, 377
relationships, creating for Data Model, 433-434
Relationships view (Power BI Desktop), 691
relative recording of macros, 467, 470-471
remainder in division, finding, 184-185
Remove Duplicates command, finding unique values, 387-389
removing
blanks in pivot tables, 401-402
commands from Quick Access Toolbar, 64
green triangles in numbers as text, 86
leading/trailing spaces, 212-215
manual page breaks, 677
non-breaking spaces, 215
subtotals, 370
renaming worksheets, 90
repeating
headings on each page, 674
REPLACE() function, 170
REPLACEB() function, 170
Replacer Power Query M functions, 765
replacing
error values when printing, 685
Report Filter values, copying pivot tables for, 429-430
Report view (Power BI Desktop), 691
reports, centering, 685
requesting Excel feature changes, 20, 415
resetting ribbon to original, 79
resizing
3D Map columns, 615
3D Map panes, 616
columns/rows, 360, 535-536, 554
Filter drop-down menu, 377
name box, 319
resources for information
learning about new features, 54
Power Pivot and Power Query, 447
retrieving cells in pivot tables, 270-272
returning
all database records, 275
original text, 229
single cell, 279
worksheet name with CELL() function, 247
reversing logical values, 242
reviewing Power Query, 350-351
ribbon
selecting with keyboard accelerators, 88-89
customizations
frequently asked questions, 79
resetting to original ribbon, 79
Format Painter, 55
galleries, 57
icon size, 78
tabs
creating custom, 78
scrolling through, 58
viewing contextual tabs, 60-61
viewing Developer tab, 60
Ribbon Commander, 78
RibbonML, 78
RibbonX, 79
RIGHTB() function, 170
right-click menus, opening, 102
right-dragging, creating links with, 307-309
RIGHT() function, 170, 218-220
roll-ups with pivot tables, 403-405
Roman numerals, converting to/from Arabic numerals, 182
roots, calculating, 186
rotating
3D Maps, 614
ROUNDDOWN() function, 165, 177
ROW() function, 235
row headings, printing, 685
row label filters for pivot tables, 417-419
rows
highlighting with conditional formatting, 580-582
last data row, finding, 488
splitting delimiters in Power Query, 353-354
variable number of, 475
ROWS() function, 235
RRI() function, 288
RSQ() function, 296
RTD() function, 235
rules. See also conditional formatting
duplicate/unique values, 574-575
Greater Than/Less Than/Equal rules, 571-573
text containing values, 575-576
running macros, steps for mail merge formatting example, 473-474
running totals in pivot tables, 409-410
S
Sankey charts, creating, 600-601
satellite photos in 3D Maps, 616
Save As command (Backstage), 67
Save command (Backstage), 67
Save settings (Excel Options dialog box), 80
saving
CSV files, disabling warning, 25-26
themes, 546
workbooks
AutoRecover settings, 83
changing default file location, 84
changing default format, 84
as checked out server drafts, 82
worksheets as PDF, 686
scaling headers/footers, 681
scatter charts, creating, 595-596
adding multiple scenarios, 458
creating scenarios, 457
creating summary reports, 458
scenarios
adding multiple, 458
creating, 457
scenes in 3D Maps, creating, 618
scientific notation in custom number formats, 529
screen clippings, 662
scrolling
to active cell, 509
through ribbon tabs, 58
search box, finding commands, 61-63
SEARCH() function, 170, 221-224
SEARCHB() function, 170
SEC() function, 299
SECH() function, 299
SECOND() function, 167, 196-197
security
enabling macros, 466
protected mode for Internet-originated files, 83
Trusted Document settings, 84
Select Case flow control, 491-492
selecting
cells
with hyperlinks, 268
moving to corner of, 102
unselecting with Ctrl+click, 24
colors in themes, 545
data for charts, 588
extended selections, shortcut keys for, 96-97
Filter drop-down menu items, 375-376
fonts in themes, 546
gallery options with keyboard accelerators, 89-90
in legacy dialog boxes with keyboard accelerators, 92
print area, 675
printer, 682
ranges, shortcut keys for, 102
ribbon commands with keyboard accelerators, 88-89
visible cells in macros, 502
selection
separating text by delimiter, 509-510
sequences of numbers, generating, 51-52
serial numbers for dates/times, 189
SERIESSUM() function, 300
server drafts, saving checked out files as, 82
setting pivot table defaults, 413-415
settings for printing, finding, 668-670
shaded area maps, 603
shapes, 639
changing, 648
Share command (Backstage), 67
etiquette for, 11
sharing
themes, 547
SHEET() function, 233
SHEETS() function, 233
shortcut keys. See also keyboard accelerators
assigning to macros, 472
copying in collapsed view, 369
date/time stamps, 103
finding last record, 475
formatting strikethrough, 102
inserting cells, 21
moving to corner of selection, 102
moving to end of data, 475
moving to top/bottom of data, 101
opening right-click menus, 102
repeating commands, 103-104, 509
scrolling to active cell, 509
ranges, 102
slicer items, 103
switching worksheets, 101
toggling absolute/relative references, 103
unselecting cells, 24
viewing active cell, 102
Show Formulas mode, 145
shrinking. See resizing
SIGN() function, 165
signing in to Power BI Desktop, 687
SIN() function, 299
single cell, returning, 279
SINH() function, 299
sizing. See resizing
SKEW() function, 296
SKEW.P() function, 296
accessing with keyboard accelerators, 91
clearing filters, 427
for multiple pivot tables, 425-426
SLN() function, 288
SLOPE() function, 296
slow motion formula calculation, 506-507
SMALL() function, 296
adding images to, 644
organizational and hierarchical charts, 644-646
Smart Lookup feature, 38
Social Security number (SSN) formats, 524-525
solving equations, troubleshooting, 508
sorting
data, 358
by color or icon, 359
left-to-right sorts, 360
troubleshooting, 362
pivot tables, 430
spaces
leading/trailing spaces, removing, 212-215
non-breaking spaces, removing, 215
spacing in custom number formats, 527
sparklines, 624
axis settings
troubleshooting, 626
copying, 637
stacked column charts as, 633-634
spearing formulas, 172
spearing through worksheets with 3D formulas, 328-330
special filters by data type, 382-384
speed of formulas, 260
Splitter Power Query M functions, 765-766
splitting
delimiters in Power Query, 353-354
square brackets [ ] in date/time formatting, 194
square roots, calculating, 186
stacked column charts as sparklines, 633-634
stacking. See nesting
STANDARDIZE() function, 296
Statistical DAX functions, 727-731
statistical functions, list of, 289-298
status bar, viewing statistics in, 73
STDEVA() function, 297
STDEVPA() function, 297
STDEV.P() function, 297
STDEV.S() function, 297
STEYX() function, 297
streets versus addresses in 3D Maps, 613
strikethrough, formatting cells with, 102
styles
applying with paintbrush icon, 588-589
Picture Styles gallery, 657-659
SmartArt styles, changing, 643-644
submitting ideas for new settings, 81
subset of cells, data bars for, 562
SUBSTITUTE() function, 170, 224-225
SUBTOTAL() function, 165, 178-179
subtotals
copying data in collapsed view, 368-369
of dates by month, 372
formatting data in collapsed view, 369-370
of multiple fields, 371
one-page summaries with, viewing, 366
in pivot tables, including filtered items, 438-440
removing, 370
sorting data in collapsed view, 366-367
SUM() function, 119, 165, 171-172. See also AutoSum feature
SUMIF() function, 165
SUMIFS() function, 166, 186-188
summary reports, creating, 458
summing database records, 274
SUMPRODUCT() function, 166, 300
SUMSQ() function, 297
SUMX2MY2() function, 297
SUMX2PY2() function, 297
SUMXMY2() function, 297
superscripts, formatting, 24-25
support periods for Windows and Office, 4
suppressing Update Links dialog box, 314
SWITCH() function, 14, 232, 239-242
switching
worksheets, shortcut keys for, 101
SYD() function, 288
symbols, inserting in cells, 507
T
T.INV() function, 297
T.INV.2T() function, 297
T.TEST() function, 297
Table Column operations Power Query M functions, 766-767
Table Construction Power Query M functions, 767-768
Table Conversion Power Query M functions, 768
Table Information Power Query M functions, 768
Table Row Operations Power Query M functions, 768-770
tables
joining with Data Model
creating relationships, 433-434
preparing data, 432
names, 316
ranges as, defining, 132
Table tool, copying formulas with, 132-133
tabs (ribbon)
contextual tabs, viewing, 60-61
creating custom, 78
Developer tab, viewing, 60
scrolling through, 58
Tabular Form layout (pivot tables), 399-400
TAN() function, 299
TANH() function, 299
TBILLEQ() function, 288
TBILLPRICE() function, 288
TBILLYIELD() function, 288
T.DIST() function, 297
T.DIST.2T() function, 297
T.DIST.RT() function, 297
telephone number formats, 524-525
Tell Me What You Want to Do search box, 61-63
templates, saving charts as, 598-599
testing
multiple conditions
with AND() function, 238
with OR() function, 239
value types, 245
text
centering in merged cells, 536-538
containing values, conditional formatting for, 575-576
converting
to number/date, 229
in custom number formats, 527
default font, changing, 548-549
filtering by, 382
formatting
bold/italic/underline, 532
font typeface, 531
removing leading/trailing spaces, 212-215
returning original, 229
separating by delimiter, 509-510
Text Comparison Power Query M functions, 772
text dates, converting to real dates, 199-200
Text Extraction Power Query M functions, 770
text functions
converting text to number/date, 229
finding characters in cell, 221-224
finding number of characters in cell, 220-221
formatting numbers as text, 227-228
removing leading/trailing spaces, 212-215
returning original text, 229
Text Information Power Query M functions, 770
TEXTJOIN() function, 14, 141, 170, 210-211
Text Membership Power Query M functions, 771
Text Modification Power Query M functions, 771
text times, converting to real times, 200-201
Text Transformation Power Query M functions, 772-773
themes, 543
applying, 547
colors in
choosing, 545
old Office theme, 545
components of, 544
effects in, 546
fonts in, choosing, 546
Office theme, 543
saving, 546
sharing, 547
thousand separators, numeric formatting with, 522
three-icon sets, 565
time/date DAX functions, 714-715
time/date formats, 523
time/date functions
converting text dates to real dates, 199-200
converting text times to real times, 200-201
end of month, 206
extracting date/time portions, 196-197
grouping dates by day of week, 201-202
grouping dates into weeks, 202-203
in pivot tables, creating with DAX Measures, 443-445
time calculations, 198
Time and Date DAX functions, 714, 715
Time Intelligence DAX functions, 716-718
Time Power Query M functions, 773, 774
time Power Query M functions, 773-774
times/dates
animating 3D Maps over, 607
converting text to, 229
in custom number formats, 528-529
grouping by day of week, 201-202
limitations on, 190
Lotus 1-2-3 compatibility, 191, 446
negative time, allowing, 85
time calculations, 198
time stamps, shortcut keys for, 103
TIMEVALUE() function, 167, 200-201
tipping 3D Maps, 614
TODAY() function, 167, 195-196
toggling references, 103, 123-126
toolbars. See mini toolbar; Quick Access Toolbar; ribbon
ToolTips
for Excel Options dialog box settings, 80-81
for Format Painter, 55
for functions, 155
Top 10 filter for pivot tables, 422-423
top of data, moving to, 101
totaling filtered results, 384
totals in pivot tables, including filtered items, 438-440
touch mode, 69
touchscreen behavior, changing, 82
tours in 3D Maps, creating, 618
Trace Dependents feature, 147
Trace Error feature, 140
Trace Precedents feature, 147
trailing spaces, removing, 212-215
transactional data
consolidating and adding unique values, 389-390
finding unique values, 387-389
pivot tables. See pivot tables, 391
transforming data in Power Query, 346-348
transparency of pictures, 653
TRANSPOSE() function, 235, 268-270
transposing
TREND() function, 298
Trig and Math DAX functions, 722-725
trigonometry functions, list of, 298-299
TRIMMEAN() function, 298
troubleshooting
3D Maps, 613
array formulas, copying, 340
AutoSum errors in macros, 485-486
chart positioning, 588
date filters, 382
DAX Measures, 443
Edit Links dialog box, finding, 314
filtering pivot tables, 420
formulas
dependents, 147
direct precedents, 146
Evalute Formula dialog box, 149
Excel as calculator, 150
highlighting formula cells, 146
implicit intersection, 126
Show Formulas mode, 145
Trace Error feature, 140
Watch Window feature, 148
FORMULATEXT() function, 262
importing data into Power BI Desktop, 689
inserting cells with shortcut keys, 21
keyboard accelerators, 90
macros, debugging mode, 496
manual page breaks, 676
OFFSET() function, 265
page numbers on printed documents, 672
performance, 663
pivot tables
changing pivot tables, 403
missing tools, 403
rankings in, 411
refreshing data, 402
viewing contextual tabs, 60
PMT function, 450
selecting merged cells, 538-539
solving equations, 508
sorting data, 362
sparkline axis settings, 626
TRIM() function, 215
unique values, 570
TRUNC() function, 166
Trust Center links, creating, 312-313
Trust Center settings (Excel Options dialog box), 80
Trusted Document settings, 84
trusted locations, folders as, 85
two-variable what-if analysis, 451-452
two-way lookups with intersection operator, 324
TYPE() function, 233
Type Power Query M functions, 774-775
typing links to worksheets, 311
U
unary minus example (order of operations), 136
underline format, 532
undo, macros and, 474
undocking pivot table field list, 396
UNICHAR() function, 170, 216-217
UNICODE() function, 170, 217-218
unique records, advanced filters in macros, 499-500
unique values
conditional formatting, 570, 574-575
consolidating and adding, 389-390
extracting with formulas, 48-50
unlocking aspect ratio for pictures, 655
unpivoting data in Power Query, 349-350
unsaved workbooks
creating links to, 311
recovering, 67
unselecting cells with Ctrl+click, 24
Update Links dialog box, suppressing, 314
updating links in worksheets
to closed workbooks, 313
to missing linked workbooks, 313
suppressing Update Links dialog box, 314
UPPER() function, 170, 211-212
URI Power Query M functions, 775
V
Value Power Query M functions, 775
values
converting formulas to, 309
duplicate, conditional formatting, 574-575
formulas versus, 118
holding in names, 327
ranges in advanced filters, 387
text containing, conditional formatting for, 575-576
unique
conditional formatting, 570, 574-575
consolidating and adding, 389-390
extracting with formulas, 48-50
Values Filter fly-out (Top 10 filter), 422-423
value types, testing, 245
VARA() function, 298
variable number of rows, 475
variables in VBA code, 483-484
VAR.P() function, 298
VAR.S() function, 298
VARPA() function, 298
VBA (Visual Basic for Applications). See also macros
accessing help, 482
parts of speech analogy, 478-483
collections, 479
examining recorded code, 483
objects and methods, 479
VBE (Visual Basic Editor), panes in, 477
VDB() function, 288
videos, creating in 3D Maps, 618
viewing
active cell, shortcut keys for, 102
cell contents, 284
current workbook info, 68
data loss warning with comma delimited files (CSV), 82
dependents, 147
Developer tab, 60
expanded formula bar, 71
financial data with waterfall chart, 593-594
Format Cells dialog box, 193
icons with whitespace (touch mode), 69
legacy Data Import wizards, 82
one-page summaries with subtotals, 366
recent workbooks, changing number of, 84
status bar statistics, 73
worksheets
overview, 72
zooming in/out, 72
zero in cells as blank, 85
visible cells, selecting in macros, 502
Visual Basic Editor (VBE), panes in, 477
Visual Basic for Applications. See VBA
visualizations. See also data visualizations
custom visuals, Office 365 support, 599-601
for Power BI Desktop
cross-filtering, 699
drill-down hierarchies, 700-702
Visualizations panel (Power BI Desktop), 690
VLOOKUP() function, 235
Data Model versus, 432
finding values based on ranges, 249-250
Fuzzy Lookup add-in with, 281
with multiple columns, 15
W
Watch Window feature, 148
waterfall charts, creating, 593-594
watermarks in headers/footers, inserting, 679
web functions, list of, 283
WEBSERVICE() function, 283
WEEKDAY() function, 167, 201-202, 580
WEEKNUM() function, 167, 202-203
weeks, grouping dates into, 202-203
WEIBULL.DIST() function, 298
whitespace around icons, viewing with touch mode, 69
wildcard characters, 188
Windows, support period for, 4
Windows Ctrl shortcut keys, 93-94
win/loss charts as sparklines, 623, 632-633
Word Clouds, creating, 599-600
workbooks
blank workbooks, recording macros in, 475-477
clearing recent list, 68
external workbooks, links to, 310
finding hidden content, 69
in folders, appending worksheets from, 354-355
Internet-originated, protected mode for, 83
presentation formatting
screen clippings, 662
printing
automatic page breaks, 675
centering reports, 685
column/row headings, 685
comments, 685
error values, 685
finding print settings, 668-670
fitting data to page, 675
gridlines, 685
orientation, 673
Page Layout view, 684
page number settings, 685
paper size, 673
as PDF, 686
previewing printed document, 670-672
repeating headings, 674
selecting print area, 675
troubleshooting page numbers, 672
protecting, marking as final, 69
recent, changing number to view, 84
recovering unsaved, 67
saving
AutoRecover settings, 83
changing default file location, 84
changing default format, 84
as checked out server drafts, 82
etiquette for, 11
Trusted Document settings, 84
viewing current info, 68
worksheets in
creating links by typing, 311
creating links in Trust Center, 312-313
creating links to unsaved workbooks, 311
creating links with Alternate Drag-and-Drop menu, 307-309
creating links with mouse, 309-310
creating links with Paste Options menu, 306-307
inserting new, 70
missing linked workbooks, 313
navigating multiple, 70
suppressing Update Links dialog box, 314
updating links to closed workbooks, 313
Workbook Statistics panel, 37
WORKDAY() function, 167, 206-208
WORKDAY.INTL() function, 168, 208-209
workdays, calculating, 206-209
workflow for Power Query, establishing, 344
worksheet-level scope of names, 319-320
worksheets
appending in Power Query, 352-355
auditing with Inquire, 511
creating links by typing, 311
creating links in Trust Center, 312-313
creating links to unsaved workbooks, 311
creating links with Alternate Drag-and-Drop menu, 307-309
creating links with mouse, 309-310
creating links with Paste Options menu, 306-307
suppressing Update Links dialog box, 314
updating links to closed workbooks, 313
updating links to missing linked workbooks, 313
exporting as PDF, 686
formatting
aligning cells, 530
bold/italic/underline, 532
borders, 533
changing numeric formats, 519-521
currency formats, 522
custom number formats, 525-529
date/time formats, 523
font typeface, 531
with Format Cells dialog box, 518-519
numeric formatting with thousands separators, 522
resizing columns/rows, 535-536
ZIP code/telephone/SSN formats, 524-525
inserting new, 70
name of, returning with CELL() function, 247
navigating multiple, 70
previous worksheet, referencing in formulas, 331-332
renaming, 90
spearing through with 3D formulas, 328-330
suppressing Update Links dialog box, 314
switching, shortcut keys for, 101
updating links, 313
viewing
overview, 72
zooming in/out, 72
workspaces in Power BI Desktop, publishing to, 704-705
X
XIRR() function, 288
XNPV() function, 288
XOR() function, 232
XY scatter charts, creating, 595-596
Y
YIELD() function, 288
YIELDDISC() function, 288
YIELDMAT() function, 288
Z
Z.TEST() function, 298
zeros
in cells, viewing as blank, 85
leading zeros, adding to numbers, 228-229
zones in custom number formats, 526