Index
Symbols
#### error, fixing, 104
A
adding
cell comments, 48–49
commands to Quick Access Toolbar, 9
data to charts, 124
footers, 161
headers, 161
hidden commands to Quick Access Toolbar, 10–11
legends ot charts, 125
pivot table data, 172
report filters, 170–171
adjusting page margins, 148–149
altering
charts, options, 118–119
data, charts, 123
drawing objects, 129
source data range, charts, 117
applied filters, clearing, 177
assigning names to cells or ranges, 90
Auto-Calculate, 94
formatting, 132
AutoSum, 82
AVERAGE, 83
axis scale, formatting, 122
B
Backstage view, 12–13
Backstage view, print options, 142
bold, applying, 61
borders, changing, 68
C
calculating loan payments (PMT), 96–97
cell backgrounds, changing, 54
cell comments, 48–49
cell orientation, changing, 66
cell references, checking for, 111
cells, 7
assigning names to, 90
comments, printing, 158
counting, 86
deleting, 39
error indicators, printing, 159
finding largest cell amount, 84
finding smallest cell amount, 85
indenting entries, 69
inserting, 38
Merge and Center feature, 62
selecting specific, 83
wrapping data, 67
cell text, changing color, 54
centering worksheets on pages, 156
redoing, 33
undoing, 33
changing
cell backgrounds, 54
cell orientation, 66
color of cell text, 54
column width, 53
fonts, 52
pivot table layout, 173
summary calculations, 176
chart area, formatting, 121
charts
adding
data to, 124
legends, 125
altering original data, 123
changing type, 116
creating, 114–115
formatting, axis scale, 122
moving, 115
options, altering, 118–119
plot area, formatting, 119
printing, 119
source data range, altering, 117
chart type, changing, 116
checking for formula references, 110
circular references, recognizing, 109
clearing
applied filters, 177
formatting, 70
clip art, inserting, 130
closing workbooks, 15
color, cell background, 54
coloring worksheet tabs, 22
column headers, printing, 157
column references, 7
columns
deleting, 41
freezing, 36
hiding/unhiding, 72
inserting, 41
titles, printing, 160
column width, changing, 53
commands, adding to Quick Access Toolbar, 9
conditional formatting, 78–79
contextual tabs, 6
copying
data, 34–35
formatting, 75
formulas, 89
worksheets between workbooks, 24
COUNT, 86
counting number of cells, 86
Currency format, 58
customizing
field names, 174
Quick Access Toolbar, 8
D
data
adding to charts, 124
copying and pasting, 34–35
deleting, 31
editing, 31
entering, 30
finding, 43
formatting, 50–51
bold, italic, and underline, 61
borders, 68
cell orientation, 66
clearing, 70
Currency format, 58
Date format, 59
Format As Table, 74
General format, 56
horizontal data alignment, 63
Number format, 57
row height, 64
Text format, 60
vertical data alignment, 65
moving, 42
numeric data, formatting, 55
replacing, 44
sorting, 46–47
wrapping in cells, 67
zooming into, 32
data fields, applying numeric formats to, 175
data filters, applying, 45
data items, showing/hiding, 177
Data tab, 5
Date format, 59
deleting
cells, 39
columns, 41
data, 31
objects, 141
rows, 40
worksheets, 20
dependents, checking for cell references, 111
diagrams, inserting, 135
#DIV/p! error, fixing, 105
drawing objects, altering, 129
drawing tools, 128–129
E
editing
data, 31
formulas, 88
functions, 88
entering
data, 30
formulas, 87
errors
#DIV/p! error, 105
#### error, 104
#NAME? error, 106
#REF! error, 108
#VALUE! error, 107
F
field names, customizing, 174
File tab, 4
Backstage view, 12–13
finding
data, 43
functions, 95
future value of an investment (FV), 102–103
largest cell amount, 84
smallest cell amounts, 85
fixing
#DIV/p! error, 105
#### error, 104
#NAME? error, 106
#VALUE! error, 107
fonts, 52
footers, adding, 161
Format As Table, 74
formatting
AutoShapes, 132
axis scale, 122
chart area, 121
clearing, 70
conditional formatting, 78–79
copying, 75
data, 50–51
bold, 61
borders, 68
cell orientation, 66
clearing, 70
Currency format, 58
Date format, 59
Format As Table, 74
General format, 56
horizontal data alignment, 63
italic, 61
Number format, 57
row height, 64
Text format, 60
underline, 61
vertical data alignment, 65
numeric data, 55
objects, 138
plot area, 120
charts, 119
WordArt, 133
formatting style, creating, 76–77
formula references, checking for, 110
formulas, 80
AutoSum, 82
copying, 89
editing, 88
entering, 87
Formula tab, 5
freezing
columns, 36
rows, 36
functions, 80
AVERAGE, 83
COUNT, 86
editing, 88
finding, 95
IF, 98–99
MAX, 84
MIN, 85
referencing names, 91
SUMIF, 100–101
using across worksheets, 92–93
FV (future value of an investment), 102–103
G
General format, 56
graphics
AutoShapes, 132
clip art, inserting, 130
diagrams, inserting, 135
drawing tools, 128–129
pictures, inserting from files, 131
SmartArt, 134
WordArt, inserting, 132
gridlines, printing, 157
H
headers, adding, 161
hidden commands, adding to Quick Access Toolbar, 10–11
hiding
columns, 72
data items, 177
rows, 71
worksheets, 73
Home tab, 4
horizontal data alignment, 63
I
IF, 98–99
indenting entries in cells, 69
inserting
cells, 38
clip art, 130
columns, 41
diagrams, 135
objects, 136
page breaks, 150–151
pictures from files, 131
rows, 40
WordArt, 132
worksheets, 20
Insert tab, 4
italic, applying, 61
L
landscape orientation, 155
layout, changing pivot table layout, 173
Layout gallery, 135
legends, adding to charts, 125
loan payments (PMT), calculating, 96–97
logical test function, performing, 98–99
M
MAX, 84
Merge and Center feature, cells, 62
Microsoft Word, Microsoft Word workspace, 127
MIN, 85
mouse wheel, zooming, 145
moving
charts, 115
data, 42
objects, 139
worksheets within workbooks, 23
N
#NAME? error, fixing, 106
names
assigning to cells or ranges, 90
referencing in functions, 91
Number format, 57
numeric data, formatting, 55
numeric formats, applying to data fields, 175
O
objects
deleting, 141
formatting, 138
inserting, 136
moving, 139
resizing, 140
selecting, 137
opening workbooks, 14
P
Page Break Preview mode, 152–153
page breaks
inserting, 150–151
removing, 153
Page Layout tab, 4
page margins, adjusting, 148–149
page order, 162
password protecting workbooks, 25
pasting data, 34–35
performing logical test function, 98–99
pictures, inserting from files, 131
Picture Tools Format tab, 130
Pivot Table Field List, 167
pivot tables, 164
adding
data, 172
report filters, 170–171
applying numeric formats to data fields, 175
changing layout, 173
creating, 166–167
field names, customizing, 174
rearranging, 168
showing/hiding data items, 177
sorting, 178
summary calculations, changing, 176
plot area, formatting, 120
PMT, 96–97
portrait orientation, 155
precedents, 110
print area, setting, 146–147
printing
Backstage View, 142–143
cell comments, 158
cell error indicators, 159
charts, 119
column headers, 157
gridlines, 157
Page Break Preview mode, 152–153
page margins, adjusting, 148–149
page order, 162
print area, setting, 146–147
Print Preview, 144–145
in portrait or landscape orientation, 155
repeating row and column titles, 160
row headers, 157
worksheets, 162–163
on one page, 154
Print Preview, 144–145
protecting worksheets, 26–27
Q
Quick Access Toolbar, 2
adding
commands, 9
hidden commands, 10–11
customizing, 8
QuickCharts, creating, 114
R
ranges
assigning names to, 90
summing, conditionally, 100–101
rearranging pivot tables, 168
recognizing
circular references, 109
#REF! error, 108
redoing changes, 33
#REF! error, 108
references
cell references, checking for, 111
circular references, recognizing, 109
formula references, checking for, 110
referencing names in functions, 91
refreshing pivot tables, 169
removing page breaks, 153
renaming worksheets, 21
replacing data, 44
report filters, adding, 170–171
resizing objects, 140
Review tab, 5
Ribbon, 2–5
tabs, 4–5
row headers, printing, 157
row height, 64
row references, 7
rows
column/titles, printing, 160
deleting, 40
freezing, 36
hiding/unhiding, 71
inserting, 40
titles, printing, 160
S
saving workbooks, 16
selecting objects, 137
shortcut keys, 180–183
showing data items, 177
sizing objects, 140
SmartArt, 134
sorting
data, 46–47
pivot tables, 178
source data range, altering, 117
splitting worksheets, 37
SUMIF, 100–101
summary calculations, changing, 176
summing ranges, conditionally, 100–101
switching
between open workbooks, 17
between worksheets, 18
T
tables, pivot tables, 164
adding
data, 172
report filters, 170–171
applying numeric formats to data fields, 175
changing layout, 173
creating, 166–167
field names, customizing, 174
rearranging, 168
showing/hiding data items, 177
sorting, 178
summary calculations, changing, 176
tabs
contextual tabs, 6
Data tab, 5
File tab, 4
Backstage view, 12–13
Formula tab, 5
Home tab, 4
Insert tab, 4
Page Layout tab, 4
Review tab, 5
Ribbon, 4–5
View tab, 5
worksheet tabs
coloring, 22
text boxes, 129
Text format, 60
tools, drawing tools, 128–129
U
underline, applying, 61
undoing changes, 33
unhiding
columns, 72
rows, 71
worksheets, 73
V
#VALUE! error, fixing, 107
vertical data alignment, 65
viewing multiple workbooks, 19
View tab, 5
W
WordArt
formatting, 133
inserting, 132
Word (Microsoft) workspace, 127
closing, 15
copying worksheets between, 24
moving worksheets within, 23
opening, 14
password protecting, 25
saving, 16
switching between open, 17
viewing multiple, 19
centering on pages, 156
copying between workbooks, 24
deleting, 20
hiding/unhiding, 73
inserting, 20
moving within workbooks, 23
printing, 162–163
on one page, 154
protecting, 26–27
renaming, 21
splitting, 37
switching between, 18
using functions across, 92–93
worksheet tabs, coloring, 22
wrapping data in cells, 67
X-Y-Z
zooming, 32
with mouse wheel, 145