Index
Symbols
#DIV/0! error, troubleshooting, 119-120
#### error, troubleshooting, 59
#NAME? error, troubleshooting, 120-122
#REF! error, troubleshooting, 124-126
#VALUE! error, troubleshooting, 122-123
A
Accounting format, 62
activating Fields pane (pivot tables), 199
active chart element, displaying, 144
adding
commands to Quick Access Toolbar, 10-12
groups to Quick Access Toolbar, 12
hidden commands to Quick Access Toolbar, 13
legends to charts, 150
Quick Print command to Quick Access toolbar, 192
report filter to pivot tables, 201-203
WordArt, 160
adjusting page margins, 177-178
Align Left command, 68
alignment
changing, 70
of chart axes, changing, 147
of wrapped text, 72
Alt+Tab toggle, switching between open workbooks, 19
angle of cell orientation, changing, 71
applying
cell references from other worksheets, 101-104
conditional formatting, 85
custom formatting styles, 84-85
data filters, 48
styles to shapes, 154
entering for functions, 107
Type argument, 110
Arrange All command, 20
assigning names to ranges, 99
Auto-Calculate feature, 104-105
formatting, 159
moving, 159
resizing, 159
resultant cells, 90
AVERAGE function, 91
axes (charts)
alignment, changing, 147
gridlines, formatting, 146-147
B
background color
of cells, changing, 57
of chart area, changing, 145
of charts, changing, 138
of plot area, changing, 143
printing options, 173
workbooks, closing, 17
bold formatting, applying, 65-66
borders
removing, 73
buttons
Chart Elements, 138
Close, 17
Page Break Preview, 174
C
calculating
cell average with AVERAGE function, 91
future value of investments with FV function, 115-117
largest cell amount with MAX function, 92
loan payments with PMT function, 107-110
smallest cell amount with MIN function, 93
cell references, applying from other worksheets, 101-104
background color, changing, 57
borders
removing, 75
comments
displaying, 51
editing, 51
printing, 188
counting, 94
error indicators, printing, 188
orientation, changing, 71
overwriting, 123
ranges
names, assigning, 99
names, deleting, 101
resultant cells, 90
row height, changing, 69
selecting for calculation, 91
text color, changing, 57
unmerging, 67
wrapping data in, 72
centering worksheets on a page, 185
changing
cells
background color, 57
text color, 57
charts, background color, 138
source data range of charts, 137
chart area
background color, changing, 145
Chart Elements button, 138
charts. See also pivot tables
active chart element, displaying, 144
axes
alignment, changing, 147
number formatting, customizing, 139
background color, changing, 138
chart area
background color, changing, 145
data
excluding, 150
data table, customizing, 141-151
formatting with Home tab commands, 146
incorrect source data, locating, 137
legends
adding, 150
formatting, 151
moving, 136
plot area
background color, changing, 144
source data
range of, changing, 137
type of, changing, 136
Chart Tools contextual tab, 133
circular references, troubleshooting, 126-127
Clear Contents command, 78
clearing
pivot tables filter, 211
clip art. See also graphics, 157
Close button, 17
closing workbooks, 17
color, applying
to shapes, 154
to cell background, 57
to cell text, 57
to worksheet tabs, 23
columns, 8
freezing, 38
headers
printing, 186
repeating, 189
repeating titles, printing, 189
width of
fitting to content, 56
Columns area (pivot tables), 195
commands
adding to Quick Access Toolbar, 10-13
Align Left, 68
Arrange All, 20
Clear Contents, 76
Comma Style, 58
Increase Decimal, 58
Percent Style, 59
Quick Print command, adding to Quick Access toolbar, 192
removing from Quick Access Toolbar, 13
commas, adding to numeric data, 58-87
Comma Style command, 58
comments
editing, 51
Compact form layout (pivot tables), 205
conditional formatting, applying, 85
Chart tools, 133
Format, 10
PivotTable Tools, 204
controlling visibility of workbook objects with SkyDrive, 221
converting numeric data
to text, 64
to percentages, 59
copying
objects, 169
versus moving, 26
worksheets between workbooks, 25-26
correcting typing mistakes, 32-33
COUNTA function, 94
COUNT function, 94
COUNTIF function, 115
counting number of cells, 94
creating
custom formatting styles, 84-85
new folders in SkyDrive, 219
workbooks, 16
Currency format, 62
customizing
number formatting, 139
pivot tables, field names, 206-207
cutting and pasting data, 37
cycling through Print Preview pages, 174
D
data
#### error, troubleshooting, 59
adding to pivot tables, 204-205
cell comments, printing, 187
editing, 33
entering, 32
excluding from charts, 150
finding, 46
grouping with Merge and Center command, 66-87
horizontal alignment, changing, 68-69
numeric data
Accounting format, 62
commas, adding, 58
converting to percentages, 59
Currency format, 62
Date format, 63
General format, 60
Number format, 61
Time format, 64
pivot tables
replacing, 47
vertical alignment, changing, 70
wrapping in a cell, 72
data filters, applying, 48
data table (charts), customizing, 141-151
Data tab (Ribbon), 5
Date format, 63
decimal places, increasing/decreasing in numeric data, 61
decreasing decimal places in numeric data, 61
default location for pivot tables, 197
deleted files, recovering in SkyDrive, 225
deleting
drawing objects, 155
objects, 171
range names, 101
worksheets from workbooks, 22
deletions, undoing, 171
diagrams
formatting with Layouts gallery, 164
dialog boxes
Find and Replace, 47
Open, 16
dialog launchers, 4
disabling Auto-Calculate feature, 105
displaying
active chart element, 144
cell error indicators, 188
print area with Page Break Preview view, 181-182
Print Preview, 174
downloading workbooks from SkyDrive, 222
dragging pivot table fields to other areas, 201
drawing objects
deleting, 155
modifying, 155
E
editing
comments, 51
data, 33
Hotmail, signing into SkyDrive, 216
multiple recipients, sending to, 223
workbooks, sharing, 223
embedded IF statements, 111
entering
data, 32
function arguments, 107
error indicators (cells), printing, 188
errors
#### error, troubleshooting, 59
#DIV/0! error, troubleshooting, 119-120
#NAME? error, troubleshooting, 122-123
#REF! error, troubleshooting, 124-126
#VALUE! error, troubleshooting, 122-123
troubleshooting, 118
Excel Web App, limitations of, 221
excluding, data from charts, 150
F
Fields pane (pivot tables), activating, 199
fields (pivot tables)
dragging to other areas, 201
files, recovering from SkyDrive, 225
File tab (Ribbon), 6
filters, applying, 48
Filters area (pivot tables), 195
Find and Replace dialog box, 47
finding
data, 46
name of fonts, 54
fitting
columns to content, 56
rows to content, 69
folders, creating in SkyDrive, 219
fonts
footers
Format as Table feature, 81-82
Format contextual tabs, 10
Format dialog box, opening, 168-171
Format Painter
persistent mode, enabling, 83
formatting
AutoShapes, 159
axes, gridlines, 139
cells, 55
charts
legends, 151
with Home tab commands, 146
custom styles, applying, 84-85
diagrams with Layouts gallery, 164
Format as Table feature, 81-82
horizontal alignment, changing, 68-69
numeric data
Date format, 63
General format, 62
Number format, 61
Text format, 64
Time format, 64
pivot tables, applying numeric format, 207-208
text with WordArt, 160
formulas, 89
order of operation, 96
references, checking for, 128-131
resultant cells, 90
Formulas tab, 5
freezing columns/rows, 38
functions, 89
applying cell references from other worksheets, 101-104
arguments
entering, 107
Type argument, 110
Auto-Calculate feature, 104-105
AVERAGE, 91
COUNTA, 94
COUNT function, 94
COUNTIF function, 115
Insert Function command, 105-117
MAX, 92
MIN, 93
SUMIF113-115
future value of investments, calculating with FV function, 115-117
G
General format (numeric data), 60
drawing objects, modifying, 155
objects
copying, 169
deleting, 171
resizing, 170
pictures, layering, 158
shapes, 154
text boxes, 155
WordArt, inserting, 160
gridlines
charts, formatting, 139
printing, 186
grouping data with Merge and Center command, 66-87
H
headers
height of inserted objects, resizing, 170
height of rows, changing, 69
hidden commands, adding to Quick Access Toolbar, 13
hidden elements, printing, 77
hiding
Home tab, formatting charts, 146
horizontal alignment, changing, 68-69
horizontally arranging workbooks, 22
horizontally centering worksheets on a page, 185
Hotmail, signing into SkyDrive, 216
I
identifying
errors, 118
incorrect source data in charts, locating, 137
Increase Decimal command, 58
increasing
column width, 74
decimal places in numeric data, 61
inserted page breaks, 182
Insert Function command, 105-117
inserting
objects in worksheets, 164-165
WordArt, 160
worksheets in workbooks, 22
Insert Pictures task pane, 156-171
Insert tab (Ribbon), 4
italic formatting, applying, 65-66
Items in the Workbook option (SkyDrive), 219
J-K
keyboard shortcuts, 19, 227-230
L
landscape orientation, 184-185
largest cell amount, calculating, 92
layering pictures, 158
layout of pivot tables, changing, 205
Layouts gallery, 164
legal size paper, changing to letter size, 183
legends
adding to charts, 150
formatting, 151
letter size paper, changing to legal size, 183
limitations of Excel Web App, 221
loan payments, calculating with PMT function, 107-110
locating
data, 46
incorrect source data in charts, 137
logical test functions, performing with IF function, 110-112
lost passwords, recovering, 27
M
managing
overlapping pictures, 158
with Backstage view, 15
from SkyDrive, 224
MAX function, 92
Merge and Center command, 66-67
MIN function, 93
minimizing the Ribbon, 7
modifying
drawing objects, 155
object properties, 165
mouse wheel, zooming in with, 34
moving
AutoShapes, 159
charts, 136
inserted objects, 166
objects to another worksheet, 169
Quick Access Toolbar, 11
versus copying, 26
versus cutting and pasting, 45
worksheets within workbooks, 24
multiple email recipients, sending to, 223
multiple objects, selecting, 166
multiple workbooks, viewing onscreen, 20
N
name of fonts, finding, 54
names (functions)
assigning to ranges, 99
natural versus inserted page breaks, 182
new workbooks, creating, 16
Number format, 61
number of cells, counting, 94
numeric data
Accounting format, 62
commas, adding, 58
converting to percentages, 59
Currency format, 62
Date format, 63
Number format, 61
Text format, 64
Time format, 64
General format, 60
replacing, 47
numeric format, applying
to charts, 139
O
objects
copying, 169
deleting, 171
inserting in worksheets, 164-165
properties, modifying, 165
resizing, 170
Online Pictures button, 156-171
Open dialog box, 16
opening
workbooks, 16
workbooks from SkyDrive, 220
order of operation, 96
organization charts, inserting, 162-171
orientation
of cells, changing, 71
Outline form layout (pivot tables), 205
overlapping pictures, arranging, 158
overwriting, cells, 123
P
Page Break Preview button, 176
Page Break Preview view, 183-184
page breaks
adding rows as, 180
natural versus inserted page breaks, 182
Page Layout tab (Ribbon), 5
page margins, adjusting, 177-178
Page Setup dialog box, entering specific margins, 178-192
paging through Print Preview, 174
paper size, changing, 183
password protecting workbooks, 26-27
passwords
recovering, 27
worksheets, unprotecting, 29
percentages, converting numeric data to, 59
Percent Style command, 59
persistent Format Painter mode, enabling, 83
Picture button, 157
pictures, inserting from file, 157-158
Picture Tools Format tab, 157-171
areas of, 195
data
default location for, 197
dragging fields to other areas, 201
field names, customizing, 206-207
filters, clearing, 211
layout, changing, 205
numeric format, applying, 207-208
report filter, adding, 201-203
summary calculations, changing, 209-210
PivotTable Tools contextual tab, 204
plot area (charts)
background color, changing, 143
print area
displaying with Page Break Preview view, 181-182
printing
cell comments, 187
cell error indicators, 188
centering worksheets on a page, 185
column headers, 186
gridlines, 186
hidden elements, 77
landscape orientation, 184-185
Page Break Preview view, 181-182
page breaks
natural versus inserted page breaks, 182
print area
displaying with Page Break Preview view, 181-182
Quick Print command, adding to Quick Access toolbar, 192
returning worksheets to default scale, 183
row headers, 186
rows, adding as page breaks, 180
scale of worksheets, changing, 182-192
worksheets, 191
printing options, Backstage view, 173
Print Preview, 174
displaying, 174
page margins, adjusting, 177-178
zooming in worksheets, 175
properties of objects, modifying, 165
proportionally resizing objects, 170
protecting worksheets, 28
publishing workbooks to the web, 218-219
Q
commands
removing, 13
groups, adding, 12
hidden commands, adding, 13
moving, 11
Quick Print command, adding, 192
Quick Print command, adding to Quick Access toolbar, 192
R
ranges
names, deleting, 101
names, assigning, 99
rearranging pivot tables, 199-201
recognizing circular references, 126-127
recovering
deleted files from SkyDrive, 225
lost passwords, 27
references, checking for, 128-131
referencing names in a function, 100-101
refreshing pivot table data, 203-204
removing
borders, 73
commands from Quick Access Toolbar, 13
data filter drop-downs, 48
split bars, 39
renaming worksheets, 23
repeating columns headings, 189
repeating titles, printing, 186, 189
replacing data, 47
report filter, adding to pivot tables, 201-203
repositioning
charts, 136
resizing
AutoShapes, 159
objects, 170
resultant cells, 90
Review tab (Ribbon), 6
Ribbon, 3
dialog launchers, 4
groups, 3
minimizing, 7
Orientation command, 185
Quick Access toolbar, 3
tabs, 3
Data tab, 5
Formulas tab, 5
Home tab, 4
Insert tab, 4
Page Layout tab, 5
Review tab, 6
View tab, 6
rotating cells, 71
rows
adding as page breaks, 180
fitting content of, 69
freezing, 38
headers, printing, 186
height of, changing, 69
repeating titles, printing, 189
Rows area (pivot tables), 195
S
scale of worksheets, changing, 182-183
security
password protecting workbooks, 26-27
worksheets, unprotecting, 29
selecting
multiple objects, 166
specific cells for calculation, 91
setting the print area, 175-176
shapes
color, applying, 154
styles, applying, 154
sharing workbooks with SkyDrive, 223
Alt+Tab toggle, switching between open workbooks, 19
signing into SkyDrive, 217-218
signing up for SkyDrive, 215
size of fonts, changing, 54-55
SkyDrive
deleted files, recovering, 225
Excel Web App, limitations of, 221
Items in the Workbook option, 219
new folders, creating, 219
registering for, 215
visibility of workbook objects, controlling, 219
workbooks
downloading, 222
managing, 224
opening, 220
sharing, 223
smallest cell amount, calculating with MIN function, 93
SmartArt Tools Format tab, 162
sorting
source data, updating charts, 147-149
source data range (charts), changing, 137
specific margins, entering (Page Setup dialog box), 178-192
splitting worksheets, 39
styles
applying to shapes, 154
summary calculations, changing in pivot tables, 209-210
switching between open workbooks, 18-19
switching between open worksheets, 20
T
tables
data filters, applying, 48
Format as Table feature, 81-82
tabs, 3
coloring, 23
Data tab, 5
File tab, 6
Formulas tab, 5
Home tab, 4
Insert tab, 4
Page Layout tab, 5
Picture Tools Format tab, 157-171
Review tab, 6
SmartArt Tools Format tab, 133, 162
Tabular form layout (pivot tables), 205
text
converting numeric data to, 64
editing, 33
horizontal alignment, changing, 68-69
replacing, 47
WordArt, inserting, 160
wrapped text, aligning, 72
text boxes, 155
Text format, 64
Time format, 64
titles, printing, 189
troubleshooting
#### error, 59
turning off Auto-Calculate feature, 105
Type argument, 110
typing mistakes, correcting, 32, 35
U
undoing deletions, 171
unhiding
unmerging cells, 67
unprotecting worksheets, 29
updating chart source data, 147-149
user interface, 3
V
Values area (pivot tables), 195
vertical alignment, changing, 70
vertically arranging workbooks, 22
vertically centering worksheets on a page, 185
viewing
multiple workbooks onscreen, 20
print area with Page Break Preview view, 181-182
visibility of workbook objects, controlling with SkyDrive, 219
W
width
of columns, fitting to content, 56
of inserted objects, resizing, 170
Windows Taskbar, switching between open workbooks, 18-19
WordArt, inserting, 160
closing, 17
creating, 16
downloading from SkyDrive, 222
managing
with Backstage view, 15
from SkyDrive, 224
saving, 17
sharing with SkyDrive, 223
worksheets
adding, 8
deleting, 22
inserting, 22
moving, 24
cell comments, printing, 187
cell error indicators, printing, 188
centering on a page, 185
charts, moving, 136
column references, 8
columns
freezing, 38
copying between workbooks, 25-26
deleting from workbooks, 22
footers
Format as Table feature, 81-82
hidden elements, printing, 77
inserting in workbooks, 22
landscape orientation, 184-185
moving within workbooks, 24
name references, 103
objects, moving to another worksheet, 169
page breaks
protecting, 28
renaming, 23
returning to default scale, 183
row references, 9
rows
adding as page breaks, 180
freezing, 38
splitting, 39
switching between, 20
tabs, coloring, 23
unprotecting, 29
zooming in, 34
wrapping data in a cell, 72