Index
Symbols
######, 138
100% stacked charts, 257
* (asterisk), 198
= (equal sign), 115
# (pound sign), 91
? (question mark), 91
A
absolute formulas versus relative formulas, 118
access, restricting using IRM, 230
accessing SkyDrive, 358
Accounting format, 85
acquiring Microsoft accounts, 356-358
activating sheets, 37
adding
borders around ranges, 96
chart elements, 262
charts to sheets, 258
viewing available charts, 260-261
viewing recommended charts, 259
groups to tab, 16
images to headers/footers, 223-224
notes to cells, 214
page numbering to headers/footers, 224
points to sparklines, 273
shapes, SmartArt, 321
space between subtotaled groups, 246-249
tiles to visualizations, 309
Total Row to tables, 67
adjusting
calculation settings, 114
Advanced Filter, 202
Unique Records Only option, 206-207
Advanced Filter Mode, Power View, 314
aligning text in cells, 79
centering text across selection, 81
changing the way text is oriented, 83
indenting cell contents, 82-83
merging two or more cells, 80-81
wrapping text in cells to the next line, 81
#All, 130
allowing filtering on protected sheets, 201-202
applying
chart layouts, 265
chart styles, 264
colors, charts, 264
Area charts, 257
arguments, 144
arranging
windows so you can see multiple sheets at the same time, 9
deleting, 136
editing, 136
artistic effects, pictures, 329
asterisk (*), 198
attachments, sending Excel files as, 233-234
AutoCorrection Options button, 69
AutoFilter drop-downs, 64
status bar for quick calculation results, 150-151
SUM rows and columns at the same time, 149-150
Average, 150
avoiding AutoSum button, macro recorder, 341-342
axes, 255
axis, changing display units, 263
B
Bar charts, 256
bar of pie charts, creating, 271-272
BMI (body mass index), calculating, 352
borders
adding to ranges, 96
thick outer borders and thin inner lines, formatting tables, 97
browser view options, configuring, 367-368
bubble charts, creating, 270
built-in rules
dynamic cell formatting with conditional formatting, 104-105
mixing cell formats, 104
buttons, adding to ribbon, 15-16
C
calculated fields, creating (PivotTables), 296-297
Calculate Now, 115
Calculate Sheet, 115
calculating
BMI (body mass index), 352
commission, 351
days between dates, 170
Calculation group (Formulas tab), 115
Calculation Options, 115
calculations, adjusting settings, 114
category labels, 255
cell comments, 214
deleting, 216
editing, 214
formatting, 214
hiding, 216
inserting, 214
showing, 216
cell references
entering, 118
simplifying with names, 125-126
cells
adding notes to, 214
aligning text, 79
centering text across a selection, 81
changing the way text is oriented, 83
indenting cell contents, 82-83
merging two or more cells, 80-81
wrapping text in cells to the next line, 81
changing next cell direction when pressing Enter, 51
clearing, 63
coloring, 98
controlling next cell selection, 50
deleting, 45
font settings, changing, 77-79
inserting, 45
limiting data entry, Data Validation, 71-72
linking to SmartArt shapes, 324-325
moving, 46
on other sheets, using in formulas, 119
referencing
F4, 121
selecting, 44
unlocking, 228
when formatting doesn’t seem to be working right, 95
wrapping text in, 81
Cell Styles, 109
centering text across selection, 81
cent (¢) symbol, Custom Number format, 93
certifying workbooks with digital signatures, 230
changes
lost changes, recovering, 232-233
preventing by marking files as fine, 230
changing
appearance of PivotTables on sheets, 286
calculation types of field values, PivotTables, 286
chart types, 266
colors, Power View, 310
data visualizations, 305
display units in an axis, 263
font color, 79
next cell direction when pressing Enter, 51
selected layout, SmartArt, 323
shapes, SmartArt, 324
values field calculation, Power View reports, 303
characters, formatting in cells, 79
chart areas, 256
chart data visualizations, inserting, 306-307
Chart Elements list, 262
chart layouts, applying, 265
charts, 253
adding to sheets, 258
viewing available charts, 260-261
viewing recommended charts, 259
bar of pie charts, creating, 271-272
bubble charts, creating, 270
changing chart types, 266
chart styles, applying, 264
color, applying, 264
creating
with multiple chart types, 266-267
with user-created templates, 275
Paste Special, 54
preparing data, 254
sorting, 311
stock charts, creating, 269
switching rows/columns, 266
titles
chart titles, 255
chart types, creating multiple, 266-267
clearing
cells, 63
conditional formatting, 108
entire sheets, 63
filters, 191
PivotTables, 296
Freeze Panes, 219
closing
files, 12
workbooks, 33
clustered charts, 257
collapsing
fields in PivotTables, 289-290
subtotals, 239
color
applying to charts, 264
changing with Power View, 310
Custom Number format, 91
filtering by, 200
pictures, 328
color scales, 102
coloring
cells, 98
sheet tabs, 40
Column charts, 256
columns
copying formulas, 125
deleting, 42
filtering for one column, 192
freezing, multiple, 218
inserting, 41
locking in place, 218
repeating on printed pages, 220-221
sorting by value, PivotTables, 289
subtotaling multiple columns, 245
switching in charts, 266
column totals, Quick Analysis, 152
combining
conditional formatting rules, 107
multiple subtotal results to one row, 243-244
multiple visualizations, 309
Combo charts, 257
commands, Quick Access Toolbar
removing, 19
comments, cell comments, 214
commission, calculating, 351
comparison operators, 163
compatibility, PivotTables, 279-280
Compatibility Checker, 231
Compress Pictures dialog box, 331
conditional formatting with dynamic cell formatting, 101-102
applying multiple icon sets, 103-104
clearing conditional formatting, 108
combining conditional formatting, 107
custom conditional formatting rules, 105-107
editing conditional formatting, 108
stopping further rules from being, 107
conditions, Custom Number format, 91
confidential information, removing, 231
configuring
page setup, 220
repeating rows/columns on each printed page, 220-221
scaling data to fit printed pages, 221-222
controlling next cell selection, 50
converting
text to dates and times, 172
text to numbers, 69
Convert to Number, ranges, 68
copying
formulas to other cells, 123-125
sheets
between workbooks, 39
within same workbook, 38
subtotals to new locations, 240-241
corrections, pictures, 328
Count Numbers, 150
Create PivotTable dialog box, 284
criteria_range, 160
Criteria Range, Advanced Filter, 203-206
Ctrl+Enter, copying formulas, 124
Currency format, 85
custom conditional formatting rules, 105-107
Custom formats, 88
cent (¢) symbol, 93
color and conditions, 91
decimals and thousands separator, 91
symbols, 92
customizing
commands on Quick Access Toolbar, 17-19
Excel window, 12
adding buttons to ribbon, 15-16
changing size of formula bar, 13
minimizing ribbon to show only tab names, 13
moving Quick Access Toolbar below the ribbon, 17
removing groups from tabs, 15
showing/hiding tabs in ribbon, 14
future workbooks, templates, 35-36
Quick Access Toolbar for the current workbook, 19
custom sequences, sorting data, 184-185
D
dashboards, 301
#Data, 130
data
dates and times, 48
entering in multicolumn lists, 50
filtering in PivotTables, 292
clearing filters, 296
Label, Value, and Date special filters, 293-295
Top 10 option, 295
formulas, 48
functions, 48
importance of laying out properly, 114
numbers, typing into cells, 48
preparing for PivotTables, 278-279
preparing for filtering, 190
protecting on sheets, 228
allowing users to edit specific ranges, 228-230
unlocking cells, 228
ranges, preselecting, 51
scaling to fit printed pages, 221-222
separating into single columns, Text to Columns, 54-55
sorting
fixing sort problems, 187
preparations for, 178
quick sort buttons, 182
quick sorting multiple columns, 182-183
Sort dialog box, opening, 178
with custom sequences, 184-185
summarizing with Subtotal tool, 237-240
text, typing into cells, 48
types of, 48
data bars, 101
data labels, 255
data sets
data visualizations
changing, 305
inserting chart data visualizations, 306-307
inserting map data visualizations, 308-309
inserting table data visualizations, 305-306
date and time functions, 166
calculating days between dates, 170
date calculation functions, 169-170
date conversion functions, 166-168
time conversion functions, 168
date calculation functions, 169-170
calculating days between dates, 170
date conversion functions, 166-168
Date format, 86
dates
grouped dates, Search function, 196-197
grouped dates listing, filtering, 193-195
grouping in PivotTables, 290-292
dates and times, 48
dates and times stored as strings, troubleshooting, 172
DATEVALUE, 167
DAY, 167
days between dates, calculating, 170
decimals, 166
Custom Number format, 91
decreasing font size, 78
deleting
array formulas, 136
cell comments, 216
cells, 45
columns, 42
files from SkyDrive, 359
ranges, 45
rows, 42
shapes, SmartArt, 322
share options, 365
sheets, 38
sparklines, 275
diagrams, SmartArt
selecting, 321
digital signatures, certifying workbooks, 230
Disable All Macros Except Digitally Signed Macros, 334
Disable All Macros with Notification, 334
Disable All Macros Without Notification, 334
display units, changing in an axis, 263
distributed files, separating subtotaled groups, 248
#DIV/0!, 138
Document Inspector, 232
double-click, editing data, 62
downloading workbooks, 358
drilling down, 290
duplicates, removing from data sets, 207-208
dynamic cell formatting with conditional formatting, 101-102
applying multiple icon sets, 103-104
clearing conditional formatting, 108
combining conditional formatting, 107
custom conditional formatting rules, 105-107
editing conditional formatting, 108
stopping rules from being processed, 107
E
EDATE, 167
editing
array formulas, 136
cell comments, 214
conditional formatting, 108
simultaneous editing, sharing folders, 365-366
workbooks, allowing multiple users to edit at the same time, 216-217
Enable All Macros, 335
Enter, changing next cell direction when pressing, 51
entering
data
in multicolumn lists, 50
Paste Special with images and charts, 54
Paste Special with ranges, 52-53
Paste Special with text, 53-54
using Paste to merge noncontiguse selection in rows or columns, 54
formulas cell references, 118
functions
Function Arguments dialog box, 146-147
EOMONTH, 167
equal sign (=), 115
error bars, 256
error icon, 139
error messages, troubleshooting formulas, 138-139
Evaluate Formulas dialog box, 141-142
Excel
opening workbooks, 361
saving to SkyDrive, 359
Excel 97, 2
Excel 2000, 2
Excel 2002, 2
Excel 2003, 2
Excel 2007, 2
Excel 2010, 2
Excel 2013, 2
Excel Help, 11
Excel versions, sharing files between, 231
Excel Web App, 355
configuring browser view options, 367-368
requirements, 356
Excel WebApp, 2
customizing, 12
adding buttons to ribbon, 15-16
changing size of formula bar, 13
minimizing ribbon to show only tab names, 13
moving Quick Access Toolbar below the Ribbon, 17
removing groups from tabs, 15
showing/hiding tabs in ribbon, 14
ribbon, making selections from, 9
excluding items, Search Function, 195-196
exiting Excel, 12
expanding
fields in PivotTables, 289-290
subtotals, 239
Extend Selection option, 21
F
F2, editing data, 62
F4, referencing cells, 121
F9, evaluating formulas, 142
FALSE, 163
fields, PivotTables
removing, 285
renaming, 285
file-level protection, protecting workbooks, 227
file permissions, setting, 365
files
closing, 12
deleting from SkyDrive, 359
saving to local drives, 362
sharing between Excel versions, 231
sharing files online, 234
file size of pictures, reducing, 331
Fill Color, 98
Fill Effects dialog box, 99
fill handle, copying formulas, 124
filtering
by color, 200
by icon, 200
data in PivotTables, 292
clearing filters, 296
Label, Value, and Date special filters, 293-295
Top 10 option, 295
for one column, 192
grouped dates listing, 193-195
on protected sheets, allowing, 201-202
Advanced Filter Mode, 314
highlighting, 315
slicers, 315
with Search function, excluding items, 195-196
filters
Unique Records Only option, 206-207
applying to data sets, 190-191
clearing, 191
in PivotTables, 296
reapplying, 192
finding
fixed-width text, 57
fixing
sort problems, 187
folder permissions, setting, 363-365
folders, sharing, 362
creating new, 363
font color, changing, 79
font settings of cells, changing, 77-79
font size
adjusting row height, automatically, 77
decreasing, 78
increasing, 78
font typefaces, selecting, 78
footers
adding
page numbering, 224
format cells, number formats, 85
Accounting, 85
Currency, 85
Date, 86
Fraction, 87
General, 85
Number, 85
Percentage, 87
Scientific, 87
Special, 88
Text, 88
Time, 86
Format Cells dialog box, 80
Format Painter, 101
formatting
cell comments, 214
Cell Styles, 109
characters/words in cells, 79
chart elements, 262
dynamic cell formatting with conditional formatting, 101-102
applying multiple icon, 103-104
clearing conditional, 108
combining conditional, 107
editing conditional, 108
stopping further rules, 107
with Format Painter, 101
placeholder text, SmartArt, 323
subtotals, 241
tables with thick outer borders and thin inner lines, 97
titles, reports (Power View), 305
values, PivotTables, 298
form controls, running macros, 346-347
formula bar
changing size of, 13
editing data, 62
deleting, 136
editing, 136
copying to other cells, 123
columns, 125
Ctrl+Enter, 124
fill handle, 124
entering cell references, 118
inserting into tables, 128-129
relative versus absolute, 118
Table formulas, Table names, 129-130
tracking with Watch Window, 140-141
troubleshooting, 137
with Function Arguments dialog box, 174-175
using cells on other sheets, 119
using names to simplify references, 125-126
versus values, viewing, 115-116
Formulas tab, Calculation group, 115
Formula Wizard, 145
Fraction category, 87
Freeze First Column, 218
Freeze Panes, 218
clearing, 219
Freeze Top Row, 218
freezing multiple rows and columns, 218
Function Arguments dialog box, 146-147
troubleshooting formulas, 174-175
status bar for quick calculation results, 150-151
SUM rows and columns at the same time, 149-150
date and time, 166
calculating days between dates, 170
date calculation functions, 169-170
date conversion functions, 166-168
time conversion functions, 168
entering
with Function Arguments dialog box, 146-147
lookup functions, 152
overview, 144
Quick Analysis tool, 152
G
General format, 85
global names versus local names, 127-128
gridlines, 255
grouped dates, Search function, 196-197
grouped dates listing, filtering, 193-195
grouping
Grouping dialog box, 291
groups
adding to tab, 16
removing, 240
from tabs, 15
H
handles, 327
#Headers, 131
headers
adding
page numbering, 224
hidden information, removing, 231
cell comments, 216
sheets, 217
tabs in ribbon, 14
totals, PivotTables, 298
highlighting, Power View, 315
HOUR, 169
I
icons
filtering by, 200
icon sets, 102
dynamic cell formatting with conditional formatting, 103-104
images
adding to headers/footers, 223-224
inserting
Paste Special, 54
importance of laying data out properly, 114
increasing font size, 78
indenting cell contents, 82-83
Insert Function dialog box, 145
inserting
cell comments, 214
cells, 45
chart data visualizations, 306-307
columns, 41
images
map data visualizations, 308-309
modules, 337
ranges, 45
rows, 41
sheets, new, 37
table data visualizations, 305-306
tabs, new, 15
titles, reports (Power View), 305
installing add-ins, 22
interacting with sheets online, 366
IRM, restricting access, 230
italic, applying to finds, 78
items, excluding with Search function, 195-196
K
keyboard shortcuts for moving around on sheets, 20
L
Label Filter, PivotTables, 293-295
legends, 255
limitations of PivotTables, 279
limiting
data entry in cells, Data Validation, 71-72
user entry to selections from a list, 72-73
Line charts, 256
linking cells to SmartArt shapes, 324-325
listed items, filtering for, 192-193
List Range, 206
Advanced Filter, 203
lists
creating custom, 61
limiting user entry to selections from, 72-73
local drives, saving files to, 362
local names versus global names, 127-128
locking rows/columns in place, 218
lookup functions, 152
lost changes, recovering, 232-233
M
macro recorder, 338
avoiding the AutoSum button, 341-342
navigating while recording, 338
macro recording, relative references, 339-341
macros
running, 343
from form controls, text boxes, or shapes, 346-347
from the Quick Access Toolbar, 345-346
from the ribbon, 344
map data visualizations, inserting, 308-309
Mark as Final, 230
markers, spacing in sparklines, 273-274
mathematical operators, 122
order of operations, 123
matrix visualizations, sorting, 311
Max, 150
Merge Across, 80
Merge Cells, 80
Merge & Center, 80
Merge & Center drop-down, 80
Microsoft accounts, acquiring, 356-358
Microsoft online templates, workbooks, 33-34
Min, 150
minimizing ribbon to show only tab names, 13
mini toolbar, 12
Mini toolbar, 79
MINUTE, 169
modules, 337
inserting, 337
MONTH, 167
mouse, rearranging columns, 186-187
moving
cells, 46
Quick Access Toolbar, below the ribbon, 17
ranges, 46
visualizations, reports (Power View), 303
multicolumn lists, entering data, 50
multiple visualizations, combining, 309
N
#N/A, 138
Name box, selecting cells, 44
#NAME?, 138
named ranges, indicating data entry cells, 51
names
simplifying references, 125-126
navigating while recording, macro recorder, 338
replacing with Select Case, 350-353
NETWORKDAYS, 170
NETWORKDAYS.INTL, 170
next cell selection, controlling, 50
notes, adding to cells, 214
NOW(), 166
#NULL!, 138
#NUM!, 138
Number format, 85
cent (¢) symbol, 93
color and conditions, 91
decimals and thousands separator, 91
symbols, 92
Number Format drop-down, Number group, 95-96
number formats, applying with format cells, 85-88
numbers, 48
extending numerical series, 61
typing into cells, 48
O
online
creating new workbooks, 361-362
interacting with sheets, 366
sharing files, 234
opening
locally saved templates, 34-35
Sort dialog box, 178
in Excel, 361
Options button, 70
order of operations, mathematical operators, 123
orientation, changing the way text is oriented, 83
Orientation button, 83
outlining columns/row, 249-251
overtime, calculating, 171-172
P
Page Break Preview, 8
page breaks, setting with Page Break Preview, 225-226
Page Layout view, 8
page numbering, adding to headers/footers, 224
page setup, configuring, 220
repeating rows/columns on each printed page, 220-221
scaling data to fit printed pages, 221-222
paragraphs, reflowing text, 83-84
Paste, merging noncontiguous selection in rows and columns, 54
Paste Special
converting text to dates and times, 172
forcing numbers, 68
using with images and charts, 54
PDF (Portable Document Format), 33
Percentage format, 87
permissions
file permissions, 365
Picture Color Options, 329
Picture Corrections Options, 328
pictures
artistic effects, 329
color, 328
corrections, 328
file size, reducing, 331
PivotTable Field List, 280-281
PivotTables, 277
calculated fields, creating, 296-297
changing appearance on sheets, 286
changing calculation types of field values, 286
creating, 281
with Quick Analysis tool, 281
fields
removing, 285
renaming, 285
filtering data, 292
clearing filters, 296
Label, Value, and Date special filters, 293-295
Top 10 option, 295
limitations of, 279
slicers, 299
sorting, 287
by value, 289
quick sort, 287
Sort (Fieldname) dialog box, 288-289
totals, hiding, 298
values, formatting, 298
viewing recommended PivotTables, 282
viewing records used to calculate values, 290
placeholders, SmartArt (reordering), 322-323
placeholder text, SmartArt (formatting), 323
plot areas, 256
points, adding to sparklines, 273
pound sign (#), 91
PowerPivot, 278
Power View
colors, changing, 310
combining multiple visualizations, 309
Advanced Filter Mode, 314
highlighting, 315
slicers, 315
reports
changing values field calculation, 303
moving and resizing visualizations, 303
sharing, 315
titles, 305
undoing changes, 304
requirements, 302
sorting visualizations, 311
themes, 310
preparing
data
for charts, 254
data for filtering, 190
preselecting, data ranges, 51
preventing changes by marking files as final, 230
Project Explorer, Visual Basic Editor, 337
protected sheets, allowing filtering on, 201-202
protecting
workbooks from unwanted changes, 227
Q
QAT (Quick Access Toolbar), 8
question mark (?), 91
Quick Access Toolbar
customizing
for current workbook, 19
moving below the ribbon, 17
removing commands from, 19
Quick Access Toolbar (QAT), 8
Quick Analysis, column totals, 152
adding charts to sheets, 258-259
PivotTables, creating, 281
quick sort, PivotTables, 287
quick sort buttons, 182
quick sorting multiple columns, 182-183
R
R1C1 notation, referencing cells, 119-121
ranges
borders, adding, 96
Convert to Number, 68
deleting, 45
filling quickly with series, 60-61
inserting, 45
moving, 46
preselecting, 51
selecting noncontiguous, 44
selecting a range of cells, 20-21
reapplying filters, 192
rearranging columns
with Sort dialog box, 186
Recent Workbooks list, 31
records, used to calculate value (viewing (PivotTables), 290
recovering lost changes, 232-233
reducing file size of pictures, 331
referencing cells
F4, 121
#REF!, 138
reflowing text in paragraphs, 83-84
relative formulas versus absolute formulas, 118
relative references, macro recording, 339-341
relative referencing, 118
Remove duplicates, 207
removing
chart elements, 262
commands from Quick Access Toolbar, 19
duplicates from data sets, 207-208
fields from PivotTables, 285
groups, 240
from tabs, 15
hidden/confidential information, 231
subtotals, 240
renaming
fields in PivotTables, 285
sheets, 39
reordering placeholders, SmartArt, 322-323
repeating rows/columns on printed pages, 220-221
replacing nested IF statements with Select Case, 350-353
reports, Power View
changing values field calculation, 303
moving and resizing visualizations, 303
sharing, 315
undoing changes, 304
requirements
Excel Web App, 356
for Power View, 302
resizing
visualizations, reports (Power View), 303
restricting access, IRM, 230
ribbon
making selections from, 9
minimizing to show only tab names, 13
running macros, 344
rows
deleting, 42
freezing multiple, 218
inserting, 41
locking in place, 218
repeating on printed pages, 220-221
sorting by value, PivotTables, 289
switching in charts, 266
running macros, 343
from form controls, text boxes, or shapes, 346-347
from the Quick Access Toolbar, 345-346
from the ribbon, 344
S
Save AutoRecover, 232
saving
files to local drives, 362
templates, 35
to SkyDrive from Excel, 359
scaling data to fit printed pages, 221-222
Scatter charts, 257
Scientific category, 87
searches, wildcard searches, 71
Search field, 197
Search function
filtering for or excluding items, 195-196
SECOND, 169
security, VBA security, 334-335
Select Case, replacing nested IF statements, 350-353
selected layout, changing in SmartArt, 323
selecting
cells
controlling next cell selection, 50
with Name box, 44
noncontiguous, 44
chart elements, 262
font typefaces, 78
multiple sheets, 38
ranges, noncontiguous, 44
SmartArt diagrams, 321
selection, filtering by, 200-201
sending Excel files as attachments, 233-234
separating
data into a single column, Text to Columns, 54-55
subtotaled groups for distributed files, 248
subtotaled groups for print, 246
series
series name, 255
series values, 255
shapes
changing in SmartArt, 324
share options, deleting, 365
sharing
files
between Excel versions, 231
online, 234
folders, 362
creating new, 363
Power View reports, 315
themes, 112
UDFs, 350
workbooks, 362
sheets, 36
activating, 37
adding charts, 258
viewing available charts, 260-261
viewing recommended charts, 259
changing appearance of PivotTables, 286
clearing entire, 63
coloring sheet tabs, 40
deleting, 38
editing multiple sheets at one time, 63
hiding/unhiding, 217
inserting new, 37
interacting with online, 366
renaming, 39
selecting multiple, 38
using cells on other sheets in formulas, 119
showing
cell comments, 216
tabs in ribbon, 14
Silverlight, 302
simultaneous editing, sharing folders, 365-366
sizing
formula bar, 13
accessing, 358
deleting files from, 359
saving to from Excel, 359
uploading to, 359
slicers
PivotTables, 299
Power View, 315
SmartArt, 318
diagrams, selecting, 321
inserting
linking cells to shapes, 324-325
placeholders, reordering, 322-323
placeholder text, formatting, 323
selected layout, changing, 323
shapes
adding, 321
changing, 324
deleting, 322
Sort dialog box
opening, 178
rearranging columns, 186
Sort (Fieldname) dialog box, PivotTables, 288-289
sorting
charts, 311
data
fixing problems, 187
PivotTables, 287
by value, 289
quick sort, 287
Sort (Fieldname) dialog box, 288-289
preparations for, 178
quick sort buttons, 182
quick sorting multiple columns, 182-183
Sort dialog box, opening, 178
with custom sequences, 184-185
subtotals, 246
visualizations, 311
source data, 254
space, adding between subtotaled groups, 246-249
markers in sparklines, 273-274
sparklines, 253
adding points, 273
deleting, 275
Special category, 88
spellchecking, 69
stacked charts, 257
status bar, quick calculation results, 150-151
stock charts, creating, 269
Stock charts, 257
stopping rules from being processed, conditional formatting, 107
strikethrough, 79
subscript, 79
subtotal, separating groups for print, 246
subtotaling multiple columns, 245
subtotals
adding space between subtotaled groups, 246-249
applying different subtotal function types, 241-242
combining multiple subtotal results to one row, 243-244
copying to new locations, 240-241
expanding/collapsing, 239
formatting, 241
placing above the data, 239
removing, 240
sorting, 246
Subtotal tool
expanding/collapsing subtotals, 239
placing subtotals above the data, 239
removing subtotals/groups, 240
SUM, rows and columns at the same time, 149-150
SUMIF, 131
summarizing data with Subtotal tool, 237-240
sum_range, 160
superscript, 79
Surface charts, 257
switching rows and columns, charts, 266
symbols
Custom Number format, 92
T
Table, 64
table data visualizations, inserting, 305-306
Table formulas
writing outside the table, 130-132
Table names in Table formulas, 129-130
tables, 64
adding Total Row, 67
formatting with thick outer borders and thin inner lines, 97
writing Table formulas outside, 130-132
tabs, 36
adding groups, 16
inserting new, 15
removing groups from, 15
showing/hiding in ribbon, 14
templates
for creating workbooks, 33
customizing future workbooks, 35-36
Microsoft online templates, 33-34
opening locally saved templates to enter data, 34
opening locally saved templates to make changes to, 35
saving, 35
user-created templates, creating charts, 275
text, 48
aligning in cells, 79
centering text across a selection, 81
changing the way text is oriented, 83
indenting cell contents, 82-83
merging two or more cells, 80-81
centering across selection, 81
converting to dates and times, 172
converting to numbers, 69
extending series, 60
fixed-width text, 57
fixing numbers stored as, 67-68
Convert to Number, 68
Paste Special, 68
Text to Columns, 69
reflowing in paragraphs, 83-84
wrapping in cells, 81
text boxes, running macros, 346-347
Text category, 88
Text to Columns
converting text to dates and times, 172
fixed-width text, 57
separating data into a single column, 54-55
Text to Columns wizard, 69
themes, 109
applying new, 110
Power View, 310
sharing, 112
ThisWorkbook module, 337
@ (This Row), 131
thousands separator, Custom Number format, 91
tiles, adding to visualizations, 309
TIME, 168
time conversion functions, 168
Time format, 86
Time format separator, Custom Number format, 93-95
time serial numbers, 166
TIMEVALUE, 168
titles, reports (Power View), 305
Top 10 option, filtering PivotTables, 295
Total Row, adding to tables, 67
#Totals, 131
totals, hiding in PivotTables, 298
Trace Dependents, 139
Trace Precedents, 139
troubleshooting formulas, 139-140
tracking formulas, Watch Window, 140-141
trendlines, 256
troubleshooting
dates and times stored as strings, 172
Excel options, 11
formulas, 137
with Function Arguments dialog box, 174-175
sort problems, 187
TRUE, 162
two-color gradient, applying to cells, 98-99
types of data, 48
typing in cells
numbers, 48
U
UDFs (User Defined Functions), 347
how to use, 349
sharing, 350
(User-Defined Functions), 333
using Select Case to replace nested IF, 350-353
underline, applying to fonts, 78
undoing changes to reports (Power View), 304
unhiding sheets, 217
uninstalling add-ins, 22
Unique Records Only option, Advanced Filter, 206-207
unlocking cells, 228
Unmerge Cells, 80
uploading
to SkyDrive, 359
workbooks, 358
user-created templates, creating charts, 275
User-Defined Functions. See UDFs
user entry, limiting to selections from a list, 72-73
userforms, 337
users, allowing to edit specific ranges, 228-230
V
#VALUE!, 138
Value Filter, PivotTables, 293-295
values
converting formulas to, 136-137
formatting in PivotTables, 298
versus formulas, viewing, 115-116
values field, changing calculations (Power View reports), 303
VBA security, enabling, 334-335
viewing
formulas versus values, 115-116
PivotTables, recommended, 282
recommended charts, 259
records used to calculate value, PivotTables, 290
Visual Basic Editor, 336
inserting modules, 337
Project Explorer, 337
visualizations
adding tiles to, 309
combining multiple, 309
moving and resizing, Power View reports, 303
sorting, 311
W
Watch Window, tracking formulas on other sheets, 140-141
web queries, 59
WEEKDAY, 167
WEEKNUM, 168
wildcard searches, 71
windows, arranging so you can see multiple sheets at the same time, 9
wizards
Formula Wizard, 145
Text to Columns, 69
words, formatting in cells, 79
workbook-level protection, protecting workbooks, 227
workbooks
certifying with digital signatures, 230
closing, 33
coloring sheet tabs, 40
creating new, 28
creating with templates, 33
customizing future workbooks, 35-36
Microsoft online templates, 33-34
opening locally saved templates to enter data, 34
opening locally saved templates to make changes to, 35
saving, 35
customizing Quick Access Toolbar for, 19
customizing future workbooks, 35-36
downloading, 358
editing, allowing multiple users to edit at the same time, 216-217
extensions, 32
in Excel, 361
protecting from unwanted changes, 227
Recent Workbooks list, 31
sharing, 362
sheets
activating, 37
copying between workbooks, 39
copying within same workbook, 38
deleting, 38
inserting new, 37
moving between workbooks, 39
moving within same workbook, 38
renaming, 39
selecting multiple, 38
uploading, 358
WORKDAY, 169
WORKDAY.INTL, 170
wrapping text in cells, 81
writing Table formulas, outside the Table, 130-132
X
XLS, 32
XLSM, 33
XLSX, 33
XLSX extension, 32
Y
YEAR, 167
YEARFRAC, 170
Z
zooming, 10