Index
Symbols
#All specifier, 192
* (asterisk)
custom number formats, 149
in filter criteria, 271
in formulas, 176
text filters, 261
wildcard searches, 86
{ } (curly braces), array formulas, 193
#Data specifier, 192
$ (dollar sign) in cell referencing, 178-179
= (equal sign), in formulas, 176
#Headers specifier, 192
###### in cell, 199
#NAME? errors, 200
# (pound sign), custom number formats, 145
? (question mark)
in filter criteria, 271
text filters, 261
wildcard searches, 86
#REF! errors, 200
[ ] (square brackets), specifiers, 192
@ThisRow specifier, 192
~ (tilde)
in filter criteria, 271
wildcard searches, 87
#Totals specifier, 192
_ (underscore), custom number formats, 148
#VALUE! errors, 200
24 hours, time formats over, 150
A
absolute cell references, 163, 178-179
accounting format, currency format versus, 130
accounts, Microsoft, 411
sites for, 412
adding. See also inserting
commands
to QAT (Quick Access Toolbar), 15-16
groups to ribbon tabs, 14
Advanced Filter dialog box
creating unique lists, 268-269
filtering with criteria, 269-271
aligning text in cells, 121
allowing folder editing, 422
AND function, 229
applying
layouts to charts, 346
styles/colors to charts, 345
arguments for functions, 211-213
troubleshooting formulas, 235-237
multicell array formulas
entering, 194
resizing, 195
arrows, clearing, 203
artistic effects, applying to pictures, 407-408
aspect ratio
locking, 302
unlocking, 406
asterisk (*)
custom number formats, 149
in filter criteria, 271
in formulas, 176
text filters, 261
wildcard searches, 86
attachments (email), sharing workbooks, 316-317
AutoFilter, 254
automatic backups, 314
automatic table expansion, 79
AutoRecover, disabling, 315
AVERAGE function, 215
axis
display units, editing, 351
secondary axis, inserting, 356
B
Backstage view, 25
backups
automatic backups, 314
configuring, 314
disabling AutoRecover, 315
recovering, 315
unsaved file recovery, 316
bar of pie charts, creating, 361-362
black-and-white pictures, changing color pictures to, 407-408
bold text, applying, 111
multiple formats, 136
selective lines, 137
breaking links, 198
breaks. See page breaks
brightness of pictures, correcting, 407-408
Browser View Options, 425
creating online forms, 425-427
limitations, 427
C
calculated fields in PivotTables, 384
deleting, 385
editing, 385
calculating
dates and times, 231
number of days between, 233
formulas, 176
manual calculation settings, 207
payments with Goal Seek, 234-235
PivotTable fields, changing calculation type, 375
ranges
with AutoSum button, 215
multiple, nonadjacent ranges, 216-217
in tables, 190
calculation records (PivotTables), viewing, 387-388
canceling row/column selections, 92
cell addresses in table formulas, 190
cell comments, 280
deleting, 288
editing, 281
resizing, 287
cells, 7
absolute versus relative references, 163, 178-179
multiple formats, 136
selective lines, 137
centering text across, 124
conditional formatting, 154-166
highlighting duplicate/unique values, 159-160
highlighting top 10 values, 157-158
predefined rules, 159
Convert to Number option, 80
copied cells, inserting, 100
data entry, 46
alternative series methods, 52
dates and times, 48
fractions, 49
mixed series, 51
numbers as text, 47
numerical series, 51
Paste Special dialog box, 55-63
text series, 50
undoing/redoing, 49
editing data, 72
editing specific ranges, 310-311
bold/italic/underline, 111
changing multiple settings, 108-109
resizing typeface, 110
single-word formatting, 114
strikethrough/superscript/subscript text, 111-112
text color, 113
formulas
calculating, 176
inserting into tables, 189-192
manual calculation settings, 207
named cells in, 187
order of operations, 177
relative versus absolute referencing, 178-179
selecting all on sheet, 178
sheet names in references, 224
viewing all on sheet, 177
Freeze Panes option
first column, 293
multiple rows/columns, 294
top row, 293
unfreezing rows/columns, 294
functions, 209
DATEDIF function, 233
Function Arguments dialog box, 211-213
logical functions, 229
nested functions, 224
searching, 210
troubleshooting formulas with Function Arguments dialog box, 235-237
typing with formula tips, 214
gradient fills, applying, 139-140
hyperlinks
creating between sheets, 152
editing, 153
to other files, 153
removing, 153
selecting cells, 153
to web pages, 154
indenting contents, 127
across columns, 123
centering data, 122
limitations on, 122
moving, 105
named cells
in formulas, 187
global versus local scope, 188-189
number formats, 128
comma style, 128
currency symbols, 130
date and time codes, 151
decimal places, 128
filling leading/trailing spaces, 149
lining up decimals, 148
negative numbers, 129
optional versus required digits, 145-146
sharing, 144
time formats over 24 hours, 150
Paste Special dialog box, converting text to numbers, 81-83
recalculating, 196
reflowing paragraph text, 126
searching, 84
refining search, 85
troubleshooting, 85
selected cell filters, 264
selecting, 20
noncontiguous cells, 102
with name box, 101
deleting, 367
inserting, 365
spell checking, 83
subtotals
expanding/collapsing, 325
formatting, 329
multiple results in single row, 331-332
multiple results on multiple rows, 329-330
separating for print, 333
separating for shared files, 334-336
text alignment within, 121
unlocking, 309
unmerging, 123
visible cells, selecting, 328
wrapping text, 125
centering
data in merged cells, 122
text across cells, 124
changing. See also editing
axis display units, 351
calculation type of PivotTable fields, 375
chart type, 353
color pictures to black-and-white, 407-408
data point colors, 352
data source for charts, 357
layout in SmartArt, 402
series colors, 352
source workbook, 197
username, 281
charts, 341
editing
axis display units, 351
chart type, 353
secondary axis, 356
series colors, 352
inserting
with chart template, 364
manually, 343
with Quick Analysis tool, 342
layouts, applying, 346
moving, 347
previewing, 342
resizing, 346
sparklines. See sparklines
styles/colors, applying, 345
switching rows/columns, 344
templates
saving, 363
usage, 364
chart sheets, 347
chart titles, editing, 349-350
clearing. See also deleting; removing
arrows, 203
conditional formatting, 163-164
filters, 256
PivotTable filters, 384
print area, 296
sheets, 74
click-and-drag, sorting PivotTables, 378
closed workbooks, referencing, 275
closing workbooks, 30
collapsing
fields in PivotTables, 380
subtotals, 325
color codes in custom number formats, 146-147
color filters, 263
color of text, selecting, 113
color palette, themes and, 138
color pictures, changing to black-and-white, 407-408
colors
charts, applying, 345
data points, changing, 352
gradient fills, applying, 139-140
series colors, editing, 352
column headings, 7
copying formulas down, 182
filters
clearing, 256
enabling for single column, 257
reapplying, 257
selecting datasets, 256
grouping
removing grouping, 339
hiding/unhiding, 96
labels, merging datasets based on, 276-277
locking
copying formulas, 179
first column, 293
merging cells across, 123
moving
multiple columns
locking, 294
subtotals, 324
reorganizing, Advanced Filter dialog box, 266-267
repeating on printed page, 300
resizing, 199
selecting, 92
canceling selection, 92
multiple columns, 92
noncontiguous columns, 93
sorting
shortcuts for, 243
troubleshooting, 251
sums by multiple criteria, SUMIFS function, 225-227
swapping with rows in charts, 344
unfreezing, 294
combining
logical functions, 229
combo charts, inserting, 354-355
commands, adding
to QAT (Quick Access Toolbar), 15-16
comma style, formatting numbers, 128
comments. See cell comments
compact layout (PivotTables), 377
comparing values, IF function, 228
compressing pictures, 409
conditional formatting, 154-166
highlighting duplicate/unique values, 159-160
highlighting top 10 values, 157-158
predefined rules, 159
configuring
backups, 314
Consolidate tool, merging datasets
linking to source data, 277
contrast of pictures, correcting, 407-408
Convert to Number option (information symbol), 80
converting formulas to values, 184-185
co-owners of shared files, removing, 423-424
copied cells, inserting, 100
copying
formatting with Format Painter, 114-115
between workbooks without links, 183
down columns, 182
dragging fill handle, 181
paste options, 180
sheets
between workbooks, 42
linked formulas in, 43
within workbooks, 41
correcting
drilled-down data, 388
picture brightness/contrast, 407-408
COUNT NUMBERS function, 215
Creative Commons licensing, 285
criteria
rules for, 271
cropping
to shapes, 407
curly braces ({ }), 193
currency format, accounting format versus, 130
currency symbols, formatting numbers, 130
current workbook, customizing QAT (Quick Access Toolbar), 16
custom cell styles, creating, 167-168
customizing
QAT (Quick Access Toolbar), 15-16
for current workbook, 16
moving, 15
removing commands, 16
minimizing, 11
Ribbon Display Options menu, 12
custom number formats, 143-151
date and time codes, 151
filling leading/trailing spaces, 149
lining up decimals, 148
optional versus required digits, 145-146
sections, 144
sharing, 144
time formats over 24 hours, 150
custom rules, creating, 161-163
custom sequence, sorting by, 247-248
cutting
cells for moving, 105
D
data caches, unlinking PivotTables, 388-389
data entry, 46. See also typing
alternative series methods, 52
dates and times, 48
fractions, 49
fixed-width text, 67
mixed series, 51
numbers as text, 47
numerical series, 51
Paste Special dialog box, 55-63
mathematical operations in, 60-61
merging noncontiguous sections, 63
non-range data, 62
previewing paste options, 56
text series, 50
undoing/redoing, 49
data points, changing colors, 352
datasets
filters
clearing, 256
color filters, 263
creating unique lists, 268-269
date filters, 263
enabling for single column, 257
icon filters, 263
reapplying, 257
removing duplicate rows, 273-274
selected cell filters, 264
Top # filters, 262
merging
linking to source data, 277
scaling to fit page, 299
selecting
for filters, 256
keyboard shortcuts, 267
for sorting, 241
sorting. See sorting
data source
for charts, changing, 357
for PivotTables
editing, 391
DATEDIF function, 233
date filters, 263
date ranges in sparklines, 366-367
dates
calculating
number of days between, 233
data validation, 69
date and time codes, 151
fractions as, 49
Group Dates option, 258
enabling, 258
troubleshooting, 259
in PivotTables, grouping/ungrouping, 373, 381-383
storing as serial numbers, 231
typing, 48
decimal numbers, data validation, 69
decimal places, formatting numbers, 128
decimals, lining up, 148
default file type, saving workbooks, 30
default text color, applying, 113
deleting. See also clearing; removing
calculated fields in PivotTables, 385
cell comments, 288
multicell array formulas, 194-195
page breaks, 298
sparklines, 367
workbooks via OneDrive, 415-416
delimited text, importing, 64-66
deselecting cells, 102
disabling
AutoRecover, 315
Error Checking feature, 80
displaying. See viewing
dollar sign ($), in cell referencing, 178-179
downloading workbooks, 417-418
dragging
cells, 105
for column width adjustments, 119-120
fill handle, copying formulas, 181
ranges, converting formulas to values, 185
for row height adjustments, 116
drilling down in PivotTables, 387-388
duplicate rows, removing, 273-274
duplicate values, highlighting in conditional formatting, 159-160
dynamic chart titles, editing, 350
E
editing. See also changing; formatting
calculated fields in PivotTables, 385
cell comments, 281
charts
axis display units, 351
chart type, 353
secondary axis, 356
series colors, 352
conditional formatting, 164-166
data, 72
data source for PivotTables, 391
folders, allowing, 422
hyperlinks, 153
images in SmartArt, 399
startup prompt for links, 196
surveys in Excel Web App, 429
templates, 34
workbooks simultaneously, 424-425
email, sharing workbooks, 316-317
empty rows/columns in tables, 76
filters for single column, 257
Group Dates option, 258
entering. See typing
equal sign (=) in formulas, 176
error checking, enabling, 80, 201
errors. See also troubleshooting
data validation messages, 69
hiding with IFERROR function, 229-230
types, troubleshooting formulas, 199-201
Evaluate Formula dialog box, 204-206
Excel 2016 In Depth (Jelen), 209
Excel 2016 PivotTable Data Crunching (Jelen, Alexander), 369
Excel desktop version, uploading workbooks, 414-415
Excel Web App, 411. See also OneDrive
Browser View Options, 425
creating online forms, 425-427
limitations, 427
creating workbooks, 419
cropped pictures and, 405
downloading workbooks, 417-418
file extensions, 420
opening workbooks, 417
protected workbooks, 414
Reading View, 416
allowing editing, 422
creating view-only folders, 420-422
editing simultaneously, 424-425
SmartArt and, 395
uploading workbooks
via Excel desktop version, 414-415
WordArt and, 403
expanding
fields in PivotTables, 380
subtotals, 325
Extend Selection mode, 22
extensions
in Excel Web App, 420
F
F4 key (cell referencing), 179
F9 key (evaluating formulas), 206
faded slicers, troubleshooting, 393
fields in PivotTables
changing calculation type, 375
expanding/collapsing, 380
removing, 374
file extensions
in Excel Web App, 420
file size of pictures, reducing, 409
file-level protection, 306-307
files, hyperlinks to, 153. See also workbooks
fill handle, 50
dragging, copying formulas, 181
viewing, 181
filter drop-downs, hiding, 392
filters, 253
Advanced Filter dialog box
creating unique lists, 268-269
filtering with criteria, 269-271
AutoFilter, 254
clearing, 256
color filters, 263
date filters, 263
enabling for single column, 257
Group Dates option, 258
enabling, 258
troubleshooting, 259
icon filters, 263
PivotTables
clearing filters, 384
for listed items, 383
reapplying, 257
removing duplicate rows, 273-274
selected cell filters, 264
selecting datasets, 256
slicers
creating, 392
faded slicers, 393
usage, 393
Top # filters, 262
final files, marking as, 312
finding. See also searching
functions, 210
table names, 191
first column, locking, 293
fitting paragraph text to ranges, 126
fixed-width text, importing, 67
folders
creating
in OneDrive, 415
while saving workbooks, 420
allowing editing, 422
Browser View Options limitations, 427
creating view-only folders, 420-422
editing simultaneously, 424-425
font settings
bold/italic/underline, 111
changing multiple settings, 108-109
resizing typeface, 110
single-word formatting, 114
strikethrough/superscript/subscript text, 111-112
text color, 113
resizing for row height adjustments, 118
footers. See headers/footers
Format task pane, editing charts, 348-349
formatting. See also editing
cells
conditional formatting, 154-166
charts
layouts, 346
styles/colors, 345
copying/pasting formulas, 180
finding/replacing, 89
numbers, 128
comma style, 128
currency symbols, 130
date and time codes, 151
decimal places, 128
filling leading/trailing spaces, 149
lining up decimals, 148
negative numbers, 129
optional versus required digits, 145-146
in PivotTables, 376
sharing custom formats, 144
time formats over 24 hours, 150
subtotals, 329
sharing, 173
forms, creating online forms, 425-427
formula bar
resizing, 183
selecting text, 114
formulas. See also functions
multicell array formulas, 194-195
calculating, 176
between workbooks without links, 183
down columns, 182
dragging fill handle, 181
paste options, 180
inserting into tables, 189-192
breaking, 198
changing source workbook, 197
editing startup prompt, 196
invisible links, 195
refreshing data, 196
manual calculation settings, 207
in moved/copied sheets, 43
named cells in, 187
order of operations, 177
relative versus absolute referencing, 178-179
selecting all, 178
Evaluate Formula dialog box, 204-206
F9 key (evaluating highlighted portion), 206
Function Arguments dialog box, 235-237
###### in cell, 199
tracing precedents/dependents, 201-203
viewing all, 177
formula tips, typing functions, 214
fractions, typing, 49
Freeze Panes option
first column, locking, 293
multiple rows/columns, locking, 294
top row, locking, 293
unfreezing rows/columns, 294
Function Arguments dialog box, 211-213
troubleshooting formulas, 235-237
functions, 209. See also formulas
DATEDIF, 233
logical functions, 229
lookup functions
nested, 224
searching, 210
troubleshooting, 199
typing with formula tips, 214
fx button, 210
G
global scope, named cells, 188-189
gradient fills, applying, 139-140
grand totals, viewing in PivotTables, 386
Group Dates option, 258
enabling, 258
troubleshooting, 259
grouping
dates in PivotTables, 373, 381-383
rows/columns
removing groups, 339
sheets, 40
groups
adding to ribbon tabs, 14
subtotals
separating for print, 333
separating for shared files, 334-336
H
height of rows, adjusting, 115-118
by dragging, 116
with font size automatic adjustments, 118
troubleshooting automatic adjustments, 125
by typing values, 117
hidden rows, ignoring in subtotals, 321-322
hiding
errors with IFERROR function, 229-230
filter drop-downs, 392
ribbon, 12
rows/columns, 96
sheets, 291
subtotals in PivotTables, 386-387
totals in PivotTables, 386
highlighting
duplicate/unique values in conditional formatting, 159-160
points (in sparklines), 365-366
top 10 values in conditional formatting, 157-158
horizontal alignment in cells, 121
creating
between sheets, 152
to other files, 153
to web pages, 154
editing, 153
removing, 153
selecting cells, 153
I
icon filters, 263
icons
in conditional formatting, 155-157
ribbon, resizing, 7
images. See also pictures
Creative Commons licensing, 285
editing in SmartArt, 399
inserting
locking aspect ratio, 302
fixed-width text, 67
indenting cell contents, 127
input prompts, data validation, 69
inserting. See also adding
calculated fields in PivotTables, 384-385
charts
with chart template, 364
manually, 343
with Quick Analysis tool, 342
copied cells, 100
functions, 210
images
page numbering in headers/footers, 303-304
secondary axis, 356
sheets, 38
sparklines, 365
total row, 77
QAT (Quick Access Toolbar), customizing, 15-16
ribbon
usage, 10
sheets
invisible links, 195
italic text, applying, 111
J-K
bold/italic/underline text, 111
creating workbooks, 26
F4 key (cell referencing), 179
F9 key (evaluating formulas), 206
selecting datasets, 267
selecting ranges, 22
selecting visible cells, 328
undoing/redoing actions, 49
L
labels, merging datasets, 276-277
large numbers, custom formats, 146-147
layouts
changing
in SmartArt, 402
charts, applying, 346
leading spaces, filling in custom number formats, 149
leading zeros, preserving, 146
licensing, Creative Commons, 285
lining up decimals, 148
linked formulas in moved/copied sheets, 43
linking to source data when merging datasets, 277
links, 195-198. See also hyperlinks
breaking, 198
changing source workbook, 197
copying formulas without, 183
editing startup prompt, 196
invisible links, 195
refreshing data, 196
sharing, 319
listed items, filtering PivotTables, 383
lists
alternative series methods, 52
mixed series, 51
numerical series, 51
protecting, 67
returning values from, 219-220
text series, 50
local scope, named cells, 188-189
location of templates, changing, 35
locking
aspect ratio, 302
columns, copying formulas, 179
first column, 293
multiple rows/columns, 294
rows, copying formulas, 179
top row, 293
logical functions, combining, 229
logical tests, IF function, 228-229
lookup functions
M
macros, uploading workbooks with, 415
manual calculation settings, 207
manually creating PivotTables, 372-374
manually inserting charts, 343
manually updating charts, 358
margins, setting, 295
marking final files, 312
mathematical operations in Paste Special dialog box, 60-61
MAX function, 215
maximum height for rows, setting, 116
maximum width for columns, setting, 120
merging
across columns, 123
centering data, 122
limitations on, 122
datasets
linking to source data, 277
noncontiguous sections, 63
Microsoft accounts, 411
sites for, 412
MIN function, 215
minimizing ribbon, 11
mini toolbar
bold/italic/underline text, applying, 111
font settings, changing, 110
resizing typeface, 110
mixed series, 51
modifying. See editing
month and year, grouping dates by, 382-383
mouse
zoom level, 20
moving
cells, 105
charts, 347
page breaks, 297
pictures within shapes, 407
QAT (Quick Access Toolbar), 15
rows/columns
sheets
between workbooks, 42
linked formulas in, 43
within workbooks, 41
text placeholders in SmartArt, 401-402
multicell array formulas
entering, 194
resizing, 195
multiple border formats, 136
multiple cells, merging, 121-123
across columns, 123
centering data, 122
limitations on, 122
multiple columns
creating unique lists, 269
deleting, 94
inserting, 94
locking, 294
resizing, 119
selecting, 92
sorting
shortcuts for, 243
subtotals, 324
unfreezing, 294
multiple criteria, sums by (SUMIFS function), 225-227
multiple delimiters in imported data, 66
multiple font settings, changing, 108-109
multiple Paste Special options, combining, 57-59
multiple ranges, calculating, 216-217
multiple reports, grouping dates, 381
multiple rows
deleting, 94
inserting, 94
locking, 294
multiple subtotals on, 329-330
resizing, 116
selecting, 92
unfreezing, 294
multiple sheets
selecting, 40
Synchronous Scrolling, 18
window arrangements, 17
multiple subtotals
multiple type charts, inserting, 354-355
multiple users, sharing workbooks, 289-290
N
name box, selecting cells, 101
named cells
in formulas, 187
global versus local scope, 188-189
names
of sheets, 43
in formula references, 224
tables, finding, 191
negative numbers, formatting, 129
nested functions, 224
nonadjacent ranges, calculating, 216-217
nonconsecutive page numbering, 304
noncontiguous cells, selecting, 102
noncontiguous rows/columns
deleting, 95
selecting, 93
noncontiguous sections, merging, 63
non-Excel sources in Paste Special dialog box, 62
non-range data in Paste Special dialog box, 62
notes. See cell comments
NOT function, 229
number formats, copying/pasting formulas, 184
clearing data, 73
clearing sheets, 74
editing data, 72
extending in series, 51
alternative series methods, 52
formatting, 128
comma style, 128
currency symbols, 130
date and time codes, 151
decimal places, 128
filling leading/trailing spaces, 149
lining up decimals, 148
negative numbers, 129
optional versus required digits, 145-146
in PivotTables, 376
sharing custom formats, 144
time formats over 24 hours, 150
calculating, 176
inserting into tables, 189-192
manual calculation settings, 207
named cells in, 187
order of operations, 177
relative versus absolute referencing, 178-179
selecting all on sheet, 178
sheet names in references, 224
viewing all on sheet, 177
functions, 209
DATEDIF function, 233
Function Arguments dialog box, 211-213
logical functions, 229
nested functions, 224
searching, 210
troubleshooting formulas with Function Arguments dialog box, 235-237
typing with formula tips, 214
indenting, 127
mixed series, 51
PivotTable fields, troubleshooting, 375
searching, 84
refining search, 85
troubleshooting, 85
subtotals
expanding/collapsing, 325
formatting, 329
multiple results in single row, 331-332
multiple results on multiple rows, 329-330
separating for print, 333
separating for shared files, 334-336
as text, 79
converting via Paste Special, 81-83
Convert to Number option, 80
troubleshooting, 222
Top # filters, 262
typing, 46
as dates and times, 48
as fractions, 49
as text, 47
numerical series, 51
O
OneDrive. See also Excel Web App
creating folders, 415
saving workbooks to, 318-319, 414-415
online Excel version. See Excel Web App
online forms, creating, 425-427
online surveys, creating in Excel Web App, 428-429
online templates, 31
templates, 33
in Excel Web App, 417
in Outlook, 317
Recent Workbooks list, 27
refreshing PivotTables, 390
open workbooks, downloading, 418
optional digits versus required digits in custom number formats, 145-146
order of operations for formulas, 177
OR function, 229
orientation, setting, 295
outline layout (PivotTables), 377
outlines, Auto Outline, 336-337
Outlook, opening workbooks, 317
overwriting data
undoing, 98
P
deleting, 298
moving, 297
setting, 297
page numbering, inserting in headers/footers, 303-304
page setup, 295
page breaks
deleting, 298
moving, 297
setting, 297
paper size/margins/orientation, 295
print area, 296
repeating rows/columns, 300
scaling data to fit, 299
paper size, setting, 295
paragraph text, reflowing, 126
Paste Special dialog box, 55-63
converting text to numbers, 81-83
mathematical operations in, 60-61
merging noncontiguous sections, 63
non-range data, 62
previewing paste options, 56
pasting
cells for moving, 105
formulas
paste options, 180
as values, 184
payments, calculating with Goal Seek, 234-235
PDF file type, 29
percentages, formatting numbers, 132-133
personal templates. See templates
phone numbers, formatting, 134-135
corrections/color/artistic effects, 407-408
moving within shapes, 407
reducing file size, 409
pie charts
troubleshooting, small slices, 360-362
pinning
templates, 31
workbooks to Recent Workbooks list, 28
PivotTables, 369
calculated fields, 384
deleting, 385
editing, 385
creating
changing calculation type, 375
number formats, 376
Quick Analysis tool, 371
requirements, 370
expanding/collapsing fields, 380
clearing filters, 384
for listed items, 383
grand totals, viewing, 386
grouping dates
limitations, 381
number fields, troubleshooting, 375
refreshing, 389
editing data source, 391
on file open, 390
slicers
creating, 392
faded slicers, 393
usage, 393
totals, hiding, 386
viewing calculation records, 387-388
placeholders, reordering in SmartArt, 401-402
points (in sparklines), highlighting, 365-366
pound sign (#), custom number formats, 145
PowerPivot, 370
predefined rules in conditional formatting, 159
preserving leading zeros, 146
previewing
charts, 342
font changes, 110
paste options, 56
print area
clearing, 296
setting, 296
printing
page setup, 295
paper size/margins/orientation, 295
print area, 296
repeating rows/columns, 300
scaling data to fit, 299
separating subtotals for, 333
sheets
configuring print options, 304-305
specific sheets, 305
print options, configuring, 304-305
protected sheets, filters on, 264-265
protected workbooks in Excel Web App, 414
file-level protection, 306-307
for lists, 67
marking files as final, 312
sheet-level protection, 308
editing specific ranges, 310-311
unlocking cells, 309
workbook-level protection, 307-308
Q
QAT (Quick Access Toolbar), 8
for current workbook, 16
moving, 15
removing commands, 16
quarters (dates), filtering by, 263
question mark (?)
in filter criteria, 271
text filters, 261
wildcard searches, 86
Quick Access Toolbar. See QAT (Quick Access Toolbar)
Quick Analysis icon, 219
Quick Analysis tool
creating PivotTables, 371
inserting charts, 342
sorting PivotTables, 379
R
R1C1 notation in name box, 101
RAND function, random sorts, 246-247
ranges, 7
alternative series methods, 52
multiple formats, 136
selective lines, 137
calculating
with AutoSum button, 215
multiple, nonadjacent ranges, 216-217
Convert to Number option, 80
data entry, web queries, 70-72
deleting, 104
fitting paragraph text, 126
gradient fills, applying, 139-140
mixed series, 51
moving, 105
multicell array formulas
entering, 194
resizing, 195
named cells
in formulas, 187
global versus local scope, 188-189
numerical series, 51
Paste Special dialog box, 55-63
converting text to numbers, 81-83
mathematical operations in, 60-61
merging noncontiguous sections, 63
non-range data, 62
previewing paste options, 56
converting formulas to values, 185
noncontiguous ranges, 102
text series, 50
Reading View, Excel Web App, 416
read-only files, marking as, 312
reapplying filters, 257
recalculating cells, 196
Recent Workbooks list
changing number of files, 27
opening from, 27
pinning to, 28
removing from, 27
recovering
backups, 315
unsaved files, 316
redoing actions, 49
reducing file size of pictures, 409
referencing
absolute versus relative references, 163, 178-179
closed workbooks, 275
sheets in formulas, 176
refining searches, 85
reflowing paragraph text, 126
refreshing
link data, 196
PivotTables, 389
editing data source, 391
on file open, 390
relative cell references, 163, 178-179
Remote Duplicates tool, 273-274
removing. See also clearing; deleting
commands from QAT (Quick Access Toolbar), 16
co-owners of shared files, 423-424
fields from PivotTables, 374
file-level protection, 307
grouping, 339
hyperlinks, 153
page breaks, 298
workbooks from Recent Workbooks list, 27
renaming
sheets, 43
workbooks in Excel Web App, 419-420
reordering text placeholders in SmartArt, 401-402
reorganizing columns, Advanced Filter dialog box, 266-267
repeating rows/columns on printed page, 300
required digits versus optional digits in custom number formats, 145-146
reserved words, 187
resizing
cell comments, 287
charts, 346
formula bar, 183
multicell array formulas, 195
ribbon, 7
by dragging, 116
by typing values, 117
typeface, 110
for row height adjustments, 118
returning
table values
INDEX and MATCH functions, 223-224
ribbon, 7
minimizing, 11
Ribbon Display Options menu, 12
deleting rows, 95
icons, resizing, 7
inserting rows, 94
toggling, 11
usage, 10
Ribbon Display Options menu, 12
right-aligned numbers, indenting, 127
row headings, 7
grouping
removing grouping, 339
by dragging, 116
with font size automatic adjustments, 118
troubleshooting, 125
by typing values, 117
hidden rows, ignoring in subtotals, 321-322
hiding/unhiding, 96
labels, merging datasets based on, 276-277
locking
copying formulas, 179
multiple rows, 294
moving
repeating on printed page, 300
selecting, 92
canceling selection, 92
multiple rows, 92
noncontiguous rows, 93
sorting
shortcuts for, 243
troubleshooting, 251
subtotals
multiple results in single row, 331-332
multiple results on multiple rows, 329-330
swapping with columns in charts, 344
top row, locking, 293
total rows
expanding tables, 79
inserting, 77
sorting, 327
unfreezing, 294
S
saving
backups, 314
chart templates, 363
changing location, 35
default file type, 30
scaling data to fit page, 299
scrolling, Synchronous Scrolling, 18
functions, 210
help system, 9
sheets, 84
refining search, 85
troubleshooting, 85
secondary axis, inserting, 356
security. See protection
selected cell filters, 264
selecting
cells, 20
with name box, 101
noncontiguous cells, 102
datasets
for filters, 256
keyboard shortcuts, 267
for sorting, 241
formulas on sheet, 178
hyperlink cells, 153
converting formulas to values, 185
rows/columns, 92
canceling selection, 92
multiple rows/columns, 92
noncontiguous rows/columns, 93
sheets, 37
multiple sheets, 40
text color, 113
text in formula bar, 114
visible cells, 328
selective lines, borders, 137
separating subtotals
for print, 333
serial numbers, dates and times as, 231
series
alternative series methods, 52
mixed series, 51
numerical series, 51
text series, 50
series colors, editing, 352
shapes. See also SmartArt
cropping to, 407
shared files, separating subtotals for, 334-336
custom number formats, 144
folders
allowing editing, 422
Browser View Options limitations, 427
creating view-only folders, 420-422
editing simultaneously, 424-425
links, 319
themes, 173
limitations, 289
version compatibility, 313
sheet-level protection, 308
editing specific ranges, 310-311
unlocking cells, 309
cells. See cells
charts. See charts
chart sheets, 347
clearing, 74
clearing data, 73
Convert to Number option, 80
data entry. See data entry
editing data, 72
formulas. See formulas
Freeze Panes option
first column, 293
multiple rows/columns, 294
top row, 293
unfreezing rows/columns, 294
functions. See functions
grouping, 40
hiding, 291
hyperlinks. See hyperlinks
inserting, 38
moving/copying
between workbooks, 42
linked formulas in, 43
within workbooks, 41
number formats. See numbers
page setup, 295
paper size/margins/orientation, 295
print area, 296
repeating rows/columns, 300
scaling data to fit, 299
Paste Special dialog box, converting text to numbers, 81-83
pictures
corrections/color/artistic effects, 407-408
reducing file size, 409
PivotTables, placing, 373
printing
configuring print options, 304-305
specific sheets, 305
protection
file-level protection, 306-307
marking files as final, 312
sheet-level protection, 308
workbook-level protection, 307-308
referencing in formulas, 176
renaming, 43
rows/columns. See columns; rows
searching, 84
refining search, 85
troubleshooting, 85
selecting, 37
multiple sheets, 40
SmartArt. See SmartArt
spell checking, 83
subtotals. See subtotals
tables. See tables
tabs, 37
sharing, 173
ungrouping, 40
unhiding, 292
Synchronous Scrolling, 18
window arrangements, 17
visibility in Excel Web App, 425
creating online forms, 425-427
WordArt, 403
sheet tab, 8
shortcuts. See keyboard shortcuts
showing. See viewing
simultaneously editing workbooks, 424-425
single-column data, importing, 64-67
fixed-width text, 67
single words, formatting, 114
slicers
creating, 392
faded slicers, 393
usage, 393
small slices in pie charts, troubleshooting, 360-362
SmartArt, 395
changing layout, 402
Excel Web App and, 395
reordering text placeholders, 401-402
Social Security numbers, formatting, 134-135
Sort dialog box, 239
columns, sorting left to right, 249-250
selecting datasets, 241
sorting, 239
with Quick Sort buttons, 244-246
shortcuts, 243
with Sort dialog box, 239
columns left to right, 249-250
selecting datasets, 241
total rows, 327
troubleshooting, 251
source data, linking when merging datasets, 277
source workbook, changing, 197
spacebar, clearing cells, 73
deleting, 367
inserting, 365
special number format, 134-135
specific sheets, printing, 305
specifiers, inserting formulas into tables, 189-192
spell checking text, 83
spreadsheets. See sheets
square brackets ([ ]), 192
startup prompt for links, editing, 196
status bar, 8
storing dates and times as serial numbers, 231
strikethrough text, applying, 111-112
styles
charts, applying, 345
table styles, 167
subscript text, applying, 111-112
SUBTOTAL function, 78, 321-322
expanding/collapsing, 325
formatting, 329
hiding in PivotTables, 386-387
multiple results
separating
for print, 333
Subtotal tool
calculating subtotals, 323-324
expanding/collapsing subtotals, 325
tables and, 323
superscript text, applying, 111-112
surveys, creating in Excel Web App, 428-429
switching rows/columns in charts, 344
Synchronous Scrolling, 18
T
tables, 75
calculation options, 190
empty rows/columns, 76
Freeze Panes option and, 292-293
inserting formulas in, 189-192
names, finding, 191
PivotTables. See PivotTables
returning values from
INDEX and MATCH functions, 223-224
sorting by color, 243
styles, 167
Subtotal tool and, 323
total row
inserting, 77
tabs
ribbon, 7
adding groups, 14
minimizing, 11
usage, 10
viewing, 12
tabular layout (PivotTables), 377
telephone numbers, formatting, 134-135
templates, 30
changing location, 35
chart templates
saving, 363
usage, 364
editing, 34
online templates, 31
opening, 33
pinning, 31
templates window, 6
text
aligning in cells, 121
bold/italic/underline, applying, 111
centering across cells, 124
clearing data, 73
clearing sheets, 74
color, selecting, 113
in custom number formats, 146-147
data validation, 69
editing data, 72
extending in series, 50
alternative methods, 52
formatting numbers as, 133-134
indenting, 127
mixed series, 51
numbers as, 79
converting via Paste Special, 81-83
Convert to Number option, 80
troubleshooting, 222
placeholders, reordering in SmartArt, 401-402
reflowing paragraph text, 126
resizing font for row height automatic adjustments, 118
searching, 84
refining search, 85
troubleshooting, 85
selecting in formula bar, 114
single words, formatting, 114
spell checking, 83
strikethrough/superscript/subscript, applying, 111-112
typeface
resizing, 110
typing, 46
numbers as text, 47
in SmartArt, 397
WordArt, 403
wrapping in cells, 125
text-delimited files, importing, 64-66
Text Pane (SmartArt), viewing, 397
Text to Columns feature, 64-67
fixed-width text, 67
color palette and, 138
sharing, 173
thousands separators in custom number formats, 146-147
tilde (~)
in filter criteria, 271
wildcard searches, 87
time formats
date and time codes, 151
over 24 hours, 150
times
data validation, 69
storing as serial numbers, 231
typing, 48
titles, editing chart/axis titles, 349-350
toggling ribbon, 11
toolbars. See QAT (Quick Access Toolbar)
top 10 values, highlighting in conditional formatting, 157-158
Top # filters, 262
top row, locking, 293
total row
expanding tables, 79
inserting, 77
totals
grand totals, viewing in PivotTables, 386
hiding in PivotTables, 386
rows, sorting, 327
subtotals
expanding/collapsing, 325
formatting, 329
hiding in PivotTables, 386-387
multiple results in single row, 331-332
multiple results on multiple rows, 329-330
separating for print, 333
separating for shared files, 334-336
tracing precedents/dependents, 201-203
trailing spaces, filling in custom number formats, 149
faded slicers, 393
filters, selecting datasets, 256
Evaluate Formula dialog box, 204-206
F9 key (evaluating highlighted portion), 206
Function Arguments dialog box, 235-237
###### in cell, 199
tracing precedents/dependents, 201-203
functions, 199
AutoSum function, 216
Group Dates option, 259
grouping dates in PivotTables, 381
hiding sheets, 292
images in headers/footers, 302
numbers as text, 222
pie charts, small slices, 360-362
PivotTable number fields, 375
row height adjustments, 125
searches, 85
selecting datasets for sorting, 241
sorting, 251
VLOOKUP function, 222
turning on/off. See enabling
typeface
resizing, 110
for row height automatic adjustments, 118
typing. See also data entry
apostrophe (‘), 47
column width adjustments, 120-121
functions with formula tips, 214
multicell array formulas, 194
numbers, 46
as dates and times, 48
as fractions, 49
as text, 47
row height adjustments, 117
text, 46
numbers as text, 47
in SmartArt, 397
undoing/redoing actions, 49
U
underlined text, applying, 111
underscore (_), custom number formats, 148
undoing
actions, 49
deleted workbooks, 416
overwriting data, 98
unfreezing rows/columns, 294
ungrouping
dates in PivotTables, 373
sheets, 40
unhiding
rows/columns, 96
sheets, 292
unique lists, creating, 268-269
unique values, highlighting in conditional formatting, 159-160
units, changing axis display units, 351
unlinking PivotTables, 388-389
unlocking
aspect ratio, 406
cells, 309
unmerging cells, 123
unsaved files, recovering, 316
unsharing workbooks, 290
updating
data source for PivotTables, 390-391
link data, 196
PivotTables, 389
editing data source, 391
on file open, 390
uploading workbooks
via Excel desktop version, 414-415
username, changing, 281
V
values
comparing, 228
converting formulas to, 184-185
Values option (Paste Special dialog box), 55-56
version compatibility, sharing workbooks, 313
vertical alignment in cells, 121
calculation records for PivotTables, 387-388
fill handle, 181
formulas on sheet, 177
grand totals in PivotTables, 386
hidden sheets, 292
Synchronous Scrolling, 18
window arrangements, 17
ribbon tabs, 12
subtotals in PivotTables, 387
Text Pane (SmartArt), 397
view-only folders
allowing editing, 422
visibility of sheets in Excel Web App, 425
creating online forms, 425-427
visible cells, selecting, 328
W
web app. See Excel Web App
web pages, hyperlinks to, 154
weeks, grouping dates by, 381-382
whole numbers, data validation, 69
width of columns, adjusting, 119-120
wildcards in filter criteria, 271
WordArt, 403
Excel Web App and, 403
words, formatting, 114
workbook-level protection, 307-308
workbooks, 25
backups
automatic backups, 314
configuring, 314
disabling AutoRecover, 315
recovering, 315
unsaved file recovery, 316
changing number in Recent Workbooks list, 27
closed workbooks, referencing, 275
closing, 30
copying formulas between without links, 183
creating, 26
in Excel Web App, 419
current workbook, customizing QAT (Quick Access Toolbar), 16
deleting via OneDrive, 415-416
editing simultaneously, 424-425
moving/copying sheets
between workbooks, 42
linked formulas in, 43
within workbooks, 41
multiple sheets, window arrangements, 17
in Excel Web App, 417
in Outlook, 317
Recent Workbooks list, 27
refreshing PivotTables, 390
pinning to Recent Workbooks list, 28
protection
in Excel Web App, 414
file-level protection, 306-307
marking files as final, 312
sheet-level protection, 308
workbook-level protection, 307-308
Reading View (Excel Web App), 416
removing from Recent Workbooks list, 27
renaming in Excel Web App, 419-420
default file type, 30
allowing editing, 422
Browser View Options limitations, 427
creating view-only folders, 420-422
editing simultaneously, 424-425
limitations, 289
version compatibility, 313
source workbook, changing, 197
templates, 30
changing location, 35
editing, 34
online templates, 31
opening, 33
pinning, 31
unsharing, 290
uploading
via Excel desktop version, 414-415
worksheets. See sheets
wrapping text in cells, 125
X-Y-Z
.xlsm file type, 29
.xlsx file type, 29
.xltm file type, 32
.xltx file type, 32
year and month, grouping dates by, 382-383
zeros
custom number formats, 145
leading zeros, preserving, 146