absolute cell addressing
keeping cell reference constant, 90–93
Accounting Number Format option
turning values into currency, 129
addressing cells, 2
relative cell addressing, formulas, 88
alignment
Format Cell Alignment option, 117
indenting data, 119
Merge and Center options, 120–121
wrapping text, 119
Alignment button group, fonts, 114
Alignment tab, Format Cells dialog, 117
Alt key
additional shortcut keys, 24–25
key tips, 5
Analyze tab, Chart Tools
filtering data in PivotCharts with field buttons, 256
anti-macro-virus defense system, 337
arguments, functions, 84
Arrow keys, 23
navigating worksheets, 32
Auto Fill
auto filling numeric sequence, 48–49
copying values with, 48
using with text, 50
Auto Fill Option button, 50–53
Fill Formatting option, 52
Fill Without Formatting option, 52, 53
AutoComplete feature, 80
AutoFilter, 207
auto-fit feature
altering column width using, 42–43
automatic recalculation, 75
formulas, 86
PivotTables, 241
AutoSum button
adding column of numbers, 74
Average function, 79
Count Numbers function, 79
double-clicking, 75
Formulas tab, 77
Max/Min functions, 79
selecting ranges for formulas, 76
using ranges on other sheets in formulas, 272
viewing and editing formulas, 78
AutoSum function group, 83
Average function, 79
axes
adding using Layout tab, 185–188
Format Axis dialog box, charts, 175–176
axis title
adding using Layout tab, 183
Backspace key, 23
exiting, 8
File tab, 7
Quick Access toolbar, 9
banded columns, tables, 214
Between rule, conditional formatting, 149
blank cells
function ranges containing, 79
bold (B) button, 107
books <i>see </i>workbooks
borders
showing/hiding screen elements, 274
Borders drop-down menu, 109
Line Color/Style options, 111
breaks, printing
previewing page breaks, 305–308
button groups, 3
buttons
adding to Quick Access toolbar, 9
customizing Quick Access toolbar, 8
removing from Quick Access toolbar, 11
Select All button, 5
toggling, 107
calculations
cell gridlines <i>see </i>gridlines
cell references
absolute cell addressing, 90–93
clarifying by naming range, 95–102
range containing one cell, 100
range from data in worksheet, 99–100
copy-and-paste method, 88
moving formulas, 89
relative cell addressing, 88
Cell Styles option
applying ready-made formats with styles, 141
customizing styles, 143
cells, 2
aligning data in, 39–40, 114–121
applying formats with styles, 141–142
Auto Fill speeding up data entry, 48–54
conditional formatting, 145–152
deleting contents, 66
deleting columns, 124
deleting rows, 124
determining formatting of, 108
entering data into selected range, 45–47
entering numerical data, 44
Format Cell Alignment option, 117
Format Cells dialog box, 113–114
formatting, 34
formatting numbers/values, 129–137
inserting and deleting, 128–129
inserting rows, 123
Merge and Center options, 120–121
moving data, 69
protecting part of worksheet, 282–284
referring to cells in other worksheets, 270–271
selecting multiple cells, 34–37
reasons for, 34
selecting all cells, 36
using keyboard, 36
using Name Box, 37
selecting ranges for formulas, 76–77
spreadsheets, 29
widening and narrowing columns, 41–43
wrapping text, 119
Center on page option, printing, 299
centered overlay
adding chart title using Layout tab, 182
centering cell content
Merge and Center options, 120–121
Change Chart Type dialog
changing chart type, 164
changing default chart, 167
Changes button group
protecting workbooks, 287
protecting worksheets, 280
changing data <i>see </i>editing data
chart area, 157
changing chart type, 164
deleting charts, 160
reformatting, 172
chart elements
adding elements with Layout tab, 181–188
adding axis title, 183
adding data labels, 184
adding data tables, 185
adding legend, 184
adding plot area, 188
chart area, 157
data point, 157
horizontal category (x) axis, 156
vertical value (y) axis, 156, 157
Chart Layouts button group
adding chart elements with, 180–181
chart styles
color-coordination using, 177
Chart Styles option, Chart Tools, 177, 178
chart title
adding using Layout tab, 181–182
Chart Tools
filtering data in PivotCharts with field buttons, 256
Format context tab, 178
Chart Tools contextual tab, 7
chart types
Column, 158
Other, 158
Scatter, 158
adding elements with Chart Layouts, 180–181
changing data, 162
changing default, 167
creating column chart, 159–160
creating from PivotTables, 253–258
creating quickly, 160
deleting, 160
formatting <i>see </i>formatting charts
resizing, 161
selecting entire chart, 161
switching rows with columns, 168–169
using too many adornments, 156
Charts button group, 157
Clear command, filters, 205
Clear Filter button, Slicer, 246
Clear Print Area option, 301
Clear Rules option
conditional formatting, 152
click-the-tick approach
entering text and data, 39
emptying, 71
Clipboard button group, 69
copy-and-paste method, formulas, 88
copying formula result only, 94
Clipboard dialog box, 70
Collated option, printing, 296
color
color-coordination using chart styles, 177
Fill Color button, 111
Font Color button, 112
formatting charts, 172
Line Color option, borders, 111
recoloring worksheet tabs, 266
Column chart type, 158
column headings (A, B, C, ...), 3
showing/hiding screen elements, 273
Column Labels area, PivotTables, 229
grouping data, 239
grouping related items using two fields, 237
removing fields from areas, 237
columns
adding to tables, 213
altering width manually, 41
banded columns, tables, 214
changing font size, 106
deleting, 124
effect on formulas, 125
effect on formulas, 124
Comma Style button, 132
command tabs, ribbon, 3
commands
Compact layout option, PivotTables, 251, 252
conditional formatting, 145–152
Between rule, 149
Data Bars, 152
Greater Than rule, 146
Highlight Cells Rules option, 146
More Rules option, 149
sorting by cell format, 201
Top/Bottom Rules option, 150
turning off, 152
Conditional Formatting drop-down menu, 146
conditions
displaying values based on, 80–82
context menu
changing chart type, 164
formatting charts, 171
changing chart type, 164
Convert to Range option, tables, 217
Copy Cells option
Auto Fill Option button, 51, 52
Copy Level button
sorting by cell format, 202
copy-and-paste method, formulas, 88–89
copying
moving data, 69
storing copied/cut data, 69–71
formats with Format Painter, 140–141
values with Auto Fill, 48
worksheets, 266
Count Numbers function
AutoSum button, 79
Create Names from Selection dialog box, 99, 100
additional key combinations, 23–25
function key combinations, 21–23
currency
positioning of currency symbol, 132
Currency format option, 132
Current Selection button group
formatting charts, 171
Custom AutoFilter dialog box
number filters, 208
Custom Footer button
Page Setup dialog box, 316
Custom Header button
Page Setup dialog, 316
Custom Lists dialog box
customizing auto fill lists, 54
Custom Margins option, printing, 298
Custom option, Number tab
Custom Sort option
Sort & Filter drop-down menu, 200
cutting data, 69
dashboards, Excel, 262
data
adding column of numbers, 74–76
aligning in cells, 39–40, 114–121
deleting cell contents, 66
Redo button, 66
Undo button, 64
color-coordination using chart styles, 177
customizing worksheets with formulas, 84–95
entering, 39
formatting, 103
grouping, 219
indenting, 119
moving, 69
protecting worksheets, 281
storing copied/cut data, 69–71
wrapping text, 119
data aggregation <i>see </i>PivotTables
Data Bars, conditional formatting, 152
adding instructions with input messages, 60–61
navigating worksheets, 31
numerical data, 44
speeding up with Auto Fill, 48–54
data labels
adding using Layout tab, 184
data point, charts, 157
formatting charts, 172
Pie chart type, 167
switching rows with columns, 168
Data tab
accessing with key tips, 6
filtering data, 202
sorting data, 198
data tables
adding chart elements with Chart Layouts, 181
adding using Layout tab, 185
changing rule, 57
customizing prompt, 60
Information error alert, 59
personalizing drop-down menu, 57–59
Stop error alert, 59
text length rule, 57
two-character rule, 55
Warning error alert, 59
databases
adding records to, 209
converting into table, 209–216
converting table to, 217
definition of, 195
Format as Table button, 213
Date & Time function group, 83
Date button, Header dialog, 317
dates
grouping PivotTable data, 239
decimal points
formatting, 133
formatting PivotTable, 249
Define Name option
clarifying cell references, 97
Delete Cells command, 128
Delete key, 23
deleting columns, 124
effect on formulas, 125
deleting data
cutting data compared, 69
deleting cell contents, 66
deleting rows, 124
effect on formulas, 125
deleting worksheets, 265
Design tab, Chart Tools
changing chart type, 164
switching rows with columns, 168
dialog box launchers, 5
dollar sign
absolute cell addressing, 92, 93
Double Underline option, 108
drop-down menus, personalizing, 57–59
Edit Custom Lists button
customizing auto fill lists, 53
Editing button group, 74
Sort & Filter drop-down menu, 197
editing data
redoing undone edit, 66
undoing edit, 64
elements, screen
End key/mode, 24
Enter key, 24
entering text and data, 38
navigating worksheets, 32
equals sign
error alerts
customizing data validation prompt, 60
Esc key, 24
Excel 2010, 27
Excel dashboards, 262
F1, F2, …
field buttons
filtering data in PivotCharts, 256–258
removing from PivotCharts, 258
Field List button
creating PivotTables, 226
fields
filtering multiple fields, 208
sorting by two fields, 199–201
fields, PivotTables
grouping related items using two fields, 234–237
removing fields from areas, 229, 237
Fill Color button, 111
Fill Formatting option, 52
fill handles
Auto Fill speeding up data entry, 48
auto filling numeric sequence, 49
copy-and-paste method, formulas, 89
copying values with Auto Fill, 48
Fill tab, Format Cells dialog, 148
Fill Without Formatting option, 52, 53
AutoFilter, 207
clearing filters, 205
Filter option, 6
multiple fields, 208
tables, 210
turning filter off, 205
filtering data, PivotTables
creating report worksheet for items in filter, 231–232
in PivotCharts with field buttons, 256–258
Financial function group, 83
Fit All Columns on One Page option, printing, 300
Font button group, 104
determining cell formatting, 108
Fill Color button, 111
Font Color button, 112
Format Cells dialog box, 113–114
Font Color drop-down menu, 112
Font tab, Format Cells dialog, 113
fonts
Alignment button group, 114
strikethrough effect, 113
using bold/italics/underline, 107–108
footers
adding custom footers, 316
Format Axis dialog box, charts, 175–176
Format Cell Alignment option, 117
Format Cells dialog box, 113–114
Alignment tab, 117
Fill tab, 148
Font tab, 113
Number tab, 135
Protection tab, 283
Locked check box, 284
Format context tab, Chart Tools, 178
Format Data Series dialog box, charts, 173
format dialog boxes, charts, 172–175
Format Painter
Format Text button, Header dialog, 317
cells
determining formatting, 108
sorting by cell format, 201–202
charts <i>see</i> formatting charts
conditional formatting, 145–152
copying formats with Format Painter, 140–141
Font button group, 104
fonts
using bold, italics, and underline, 107–108
general format, 134
mini-toolbar options, 170
customizing number formats, 137–139
decimal points, 133
percentages, 132
punctuating values, 132
Special formats option, 137–138
turning values into currency, 129–132
select-then-do principle, 104
adding chart elements
color-coordination using chart styles, 177
context menu, 171
Current Selection button group, 171
Format Data Series dialog, 173
formatting text elements with WordArt, 179
mini-toolbar, 170
reformatting vertical axis, 175–177
restoring default formatting settings, 175
Formula AutoComplete feature, 80
formula bar, 3
changing data, 63
revealing text in cells, 40
showing/hiding screen elements, 273
viewing and editing formulas, 77–79
absolute cell addressing, 90–93, 100
adding column of numbers, 75
automatic recalculation, 75, 86
clarifying cell references by naming range, 95–102
customizing worksheets with, 84–95
effect of deleting columns/rows, 125
effect of inserting columns/rows, 124
hiding worksheets, 267
mathematical operators, 85
moving, 89
referring to cells in other worksheets, 270–271
relative cell addressing, 88
tables, 212
using ranges on other sheets in, 271–273
function groups, 83
Function Library
types (groups) of functions, 82
adding column of numbers, 74–76
arguments, 84
displaying values based on conditions, 80–82
Formula AutoComplete feature, 80
locating in Function Library, 82–84
parentheses in, 82
ranges containing blank cells, 79
selecting ranges for formulas, 76–77
structure of, 82
functions, list of
Average, 79
Count Numbers, 79
Max/Min, 79
Sum, 75
general format, 134
Gradient fill
formatting charts, 174
Greater Than rule, conditional formatting, 146
grid
size of, 31
gridlines
showing/hiding screen elements, 273, 274
group of cells <i>see </i>ranges of cells
grouping data, PivotTables, 219, 239–240
related items using two fields, 234–236
with row and column value areas, 237
hash marks (UK)
viewing numerical data, 44
header rows
changing PivotTable headers, 250
printing titles, 309
sorting data, 198
tables, 210
headers
adding custom headers, 316
Print Titles compared, 312
headings
printing actual row/column headings, 319, 320
showing/hiding screen elements, 273
heightening rows, 116
Hidden check box, Protection tab
hiding rows and columns, 125–126
clearing filters, 205
hiding worksheets, 267
Highlight Cells Rules, conditional formatting, 146
Between rule, 149
Greater Than rule, 146
More Rules option, 149
Home key, 24
Home tab
formatting, 104
horizontal category (x) axis, charts, 156
adding axes using Layout tab, 185
adding axis titles using Layout tab, 183
switching rows with columns, 168
Horizontally center page option, printing, 299
arguments, 84
Ignore Print Area option, 302
indenting data, 119
Information error alert
data validation, 59
input messages
adding data entry instructions with, 60–61
insert buttons, Header dialog, 317
Insert Cells command, 128
effect on formulas, 124
inserting rows, 123
effect on formulas, 124
italics (I) button, 107
accessing Data tab with, 6
keyboard
Arrow keys, 23
Backspace key, 23
Delete key, 23
End key, 24
Enter key, 24
Esc key, 24
Home key, 24
Page Down key, 25
Page Up key, 25
Spacebar key, 25
keyboard shortcuts <i>see </i>shortcuts
Landscape option, printing, 297
layout options, PivotTables, 251–253
plus/minus option, 252
Layout tab
adding chart elements using, 181–188
axis title, 183
data labels, 184
data tables, 185
legend, 184
plot area, 188
left-alignment
aligning data in cells, 39
legend, charts, 157
adding data table using Layout tab, 185
adding using Layout tab, 184
Line Color/Style options
Borders drop-down menu, 111
lists
personalizing drop-down menu, 57–59
Locked check box, Protection tab
Format Cells dialog, 284
Logical function group, 83
logical tests
If function, 81
Lookup & Reference function group, 83
Macro Options dialog
activating macros with keyboard shortcut, 336
activating with keyboard shortcut, 335–337
anti-macro-virus defense system, 337
deleting, 332
exposing VBA worksheet, 333–334
making available to all workbooks, 330
relative references in, 328–330
saving to Personal Macro Workbook, 330–331
VBA programming language, 323
Margins option, printing, 297
Math & Trig function group, 83
mathematical operators, formulas, 85
Max function
AutoSum button, 79
Merge and Center options, 120–121
Microsoft Excel 2010 <i>see </i>Excel 2010
Min function
AutoSum button, 79
mini-toolbar
accessing gradient and other options, 175
applying styles quickly, 144
formatting charts, 170
formatting options, 170
More Functions function group, 83
More Rules option, conditional formatting, 149
moving data, 69
moving formulas, 89
moving worksheet tabs, 264, 266
Name Box, 3
clarifying cell references by naming range in, 95–98
navigating worksheets, 33
selecting multiple cells, 37
going to cell A1 (home), 32
moving cell pointer rapidly, 32
scroll buttons, 32
summary of options, 33
using Name Box, 33
Normal button
Workbook Views button group, 314
Number button group, 129
filtering multiple fields, 208
Number Format button
formatting PivotTable, 248
Number Format drop-down menu, 135
Number of Pages button, Header dialog, 317
Number tab, Format Cells dialog, 135
numbers
<i>see also </i>values
aligning data in cells, 39
auto filling numeric sequence, 48–49
entering numerical data, 44
formatting decimal points, 133
percentages, 132
punctuating values, 132
turning values into currency, 129–132
Office 2007 button, 7
operations
order of operations in formulas, 86–87
operators
mathematical operators, formulas, 85
order of operations in formulas, 86
Orientation drop-down menu, 117
Orientation option, printing, 297
Outline layout option, PivotTables, 251, 252
Page Break Preview, 306
page breaks, printing
Page Down key, 25
Page Layout tab, 302
clearing Print Area, 301
Print Gridlines and Headings options, 319
printing titles, 310
setting Print Area, 301
Page Layout view
adding headers and footers, 312–314
Page Number button, Header dialog, 317
Page Setup dialog box
adding headers and footers, 314–315
margin options, 298
printing titles, 310
Sheet tab, 310
Page Setup group
Page Up key, 25
Pages option
deciding pages to print, 295
paintbrush, Format Painter, 140, 141
Paper Size option, printing, 297
parentheses
functions, 82
order of operations in formulas, 86
passwords
protecting workbooks, 288
protecting worksheets, 281
unprotecting worksheets, 282
Paste Values option
copying formula result only, 94, 95
pasting data
copy-and-paste method, formulas, 88–89
copying and pasting data, 68
Percent Style button, 132
percentages
formatting numbers/values, 132
Personal Macro Workbook
PgDn/PgUp keys
navigating worksheets, 32
Phone Number option
formatting numbers, 138
PivotChart Tools contextual tab, 255
creating with PivotTable, 258
deleting, 258
filtering data with field buttons, 256–258
removing field button from, 258
PivotTable areas, 227
removing fields from, 229
PivotTable Field List, 225, 226
creating PivotTable, 226
moving field data, 228
removing fields from areas, 229
PivotTable Tools contextual tab, 225
removing fields from areas, 238
accessing button group, 250
adding records to, 241
automatic recalculation, 241
changing data, 240
changing headers, 250
choosing data to work on, 227–228
collapsing/expanding, 253
Column Labels area, 229
grouping related items using two fields, 237
Compact layout option, 251, 252
creating with PivotChart, 258
filtering using Report Filter area, 229–231
related items using two fields, 234–237
plus/minus option, 252
Outline layout option, 251, 252
pivoting data sideways, 229
refreshing, 240
removing fields from areas, 229, 237
creating report worksheet for items in filter, 231–232
Row Labels area, 226, 227, 228, 234–236
Tabular layout option, 251, 252
Undo command, 224
grouping with row/column value areas, 237
selecting mathematical operations, 238
viewing filtered records, 242–247
plot area, charts, 157
adding using Layout tab, 188
plus/minus layout option, PivotTables, 252
Portrait option, printing, 297
pound signs (US)
viewing numerical data, 44
precedence
order of operations in formulas, 86–87
presentation of worksheets
showing/hiding screen elements, 273
previewing page breaks, printing, 305–308
previewing printing <i>see </i>print preview
Print Active Sheets option, 290, 291, 292, 295
Print Area
clearing, 301
Ignore Print Area option, 302
Print Gridlines and Headings options, 319
print preview
Fit All Columns on One Page option, 300
page size affecting, 294
positioning print on page, 299
previewing page breaks, 305–308
Print Selection option, 293
headers compared, 312
printing
actual row/column headings, 319, 320
adding headers and footers, 312–319
collating copies, 296
deciding pages to print, 295
deciding what to print, 289–294
default Print Active Sheets option, 295
fitting all columns on one page, 300
page centering, 299
Page Layout tab, 302
previewing page breaks, 305–308
printing entire worksheet, 289–290
printing part of worksheet, 290–294
scaling, 300
selecting printer, 295
setting number of copies, 294
Center on page, 299
Clear Print Area, 301
Collated, 296
Copies, 294
Custom Margins, 298
Fit All Columns on One Page, 300
Landscape, 297
Margins, 297
Orientation, 297
Pages, 295
Paper Size, 297
Portrait, 297
Print Area, 301
Printer button, 295
scaling, 300
Set Print Area, 301
Uncollated, 297
Protect Sheet dialog box, 280
protecting worksheets, 279–282
Protection tab, Format Cells dialog, 283
Locked check box, 284
punctuating values, 132
Quick Access toolbar, 4
adding button to, 9
removing button from, 11
ranges of cells
changing font, 104
changing font size, 106
clarifying cell references by naming, 95–102
range containing one cell, 100
ranges from data in worksheet, 99–100
converting table to, 217
deleting cell contents, 66
entering data into selected range, 45–47
function ranges containing blank cells, 79
Merge and Center options, 120–121
moving data, 69
printing part of worksheet, 290–294
scope of, 98
using on other sheets in formulas, 271–273
Recently Used function group, 83
Record Macro dialog box
activating macros with keyboard shortcut, 335
records
adding to PivotTable, 241
counting, PivotTables, 232–233
databases, 195
finding/removing duplicates, 216–217
viewing filtered records, PivotTable, 242–247
Redo button
changing data, 66
Refresh button, PivotTables, 241
relative cell addressing, formulas, 88
relative references in macros, 328–330
relocating data, 69
Remove Duplicates feature, 216, 217
renaming worksheet tabs, 266
Report Filter area, PivotTables, 229–232
reports <i>see </i>PivotTables
ribbon, 3
command tabs, 3
row headings (1, 2, 3, ...), 2
showing/hiding screen elements, 273
Row Labels area, PivotTables
choosing data to work on, 227
creating PivotTable, 226
grouping data, 239
grouping related items using two fields, 234–236
moving field data, 228
Row values area, PivotTables
PivotCharts, 255
rows
changing font size, 106
deleting, 124
effect on formulas, 125
header rows sorting data, 198
heightening, 116
inserting, 123
effect on formulas, 124
Total Row selection, tables, 214
rules <i>see </i>conditional formatting
scaling options, printing, 300
Scatter chart type, 158
scope
ranges of cells, 98
screen elements
scroll buttons, 4
navigating worksheets, 32
scrolling
security
anti-macro-virus defense system, 337
Select All button, 5
selecting worksheets, 269
select-then-do principle, formatting, 104
Set Print Area option, 301
shape styles
Sheet Options button group
Print Gridlines and Headings options, 319
Sheet tab, Page Setup dialog
printing titles, 310
sheets <i>see </i>worksheets
Shift key shortcuts
additional key combinations, 23–25
control key combinations, 19
function key combinations, 21–23
arrow keys, 23
Backspace key, 23
copying data, 68
creating table, 210
cutting data, 69
Delete key, 23
End key, 24
Enter key, 24
Esc key, 24
hiding ribbon, 275
Home key, 24
Page Down key, 25
Page Up key, 25
pasting data, 68
printing entire worksheet, 290
redoing undone edit, 66
selecting all cells, 36
showing formulas in cells, 275
Spacebar key, 25
to home cell (A1), 32
undoing edit, 65
Slicer feature, PivotTables, 242–247
Clear Filter button, 246
resizing, 247
restyling, 246
Social Security Number option
formatting numbers, 137
Sort & Filter button group
filtering data, 202
sorting data, 198
Sort & Filter drop-down menu, 197
sorting by two fields, 200
Sort A to Z option, 197
Spacebar key, 25
deleting, 192
grouping of, 191
modifying, 191
representing binary values with, 192
Special option, Number tab
Format Cells dialog box, 137–138
<i>see also </i>workbooks; worksheets
automatic recalculation, 75
status bar, 5
Stop error alert
data validation, 59
strikethrough effect, fonts, 113
structure
protecting workbooks, 286
styles
applying formats with, 141–142
applying styles quickly, 144
color-coordination using chart styles, 177
formatting charts using shape styles, 178–179
Line Style option, borders, 111
styling reports, PivotTables, 249–253
Table Style Options button group, 214
Table Styles button group, 213
Sum function
adding column of numbers, 75
selecting ranges for formulas, 76
Tab key
navigating worksheets, 32
Table Style Options button group, 214
Table Styles button group, 213
Table Tools context tab, 213
Table Tools Design tab, 211
tables
adding columns to, 213
adding records to, 209
banded columns, 214
converting to range, 217
filtering data, 210
finding/removing duplicate records, 216–217
Format as Table button, 213
formulas in, 212
header row, 210
naming, 211
Total Row selection, 214
turning database into, 209–216
tabs, worksheets, 4
File tab, 7
grouping sheets, 269
moving between sheets, 262
recoloring, 266
renaming, 266
selecting sheets, 269
viewing, 265
Tabular layout option, PivotTables, 251, 252
text
aligning in cells, 39
formatting chart elements with WordArt, 179
revealing in cells, 40
using Auto Fill with, 50
wrapping, 119
Text Filters drop-down menu, 206
Text function group, 83
Time button, Header dialog, 317
titles
adding with Merge and Center, 120–121
toggling
formatting buttons, 107
toolbars
Quick Access toolbar, 4
Top 10 AutoFilter, 208
Top/Bottom Rules, conditional formatting, 150
Total Row selection, tables, 214
Trust Center
anti-macro-virus defense system, 337
Uncollated option, printing, 297
underline (U) button, 107, 108
Undo button
changing data, 64
Undo command, PivotTables, 224
ungrouping worksheets, 269
unhiding rows and columns, 127
unhiding worksheets, 267
Value Field Settings dialog box
selecting mathematical operations, 238
values
<i>see also </i>numbers
auto filling numeric sequence, 48–49
copying formula result only, 93–95
copying with Auto Fill, 48
displaying based on conditions, 80–82
formatting decimal points, 133
percentages, 132
punctuating, 132
turning into currency, 129–132
using Auto Fill with text, 50
Values and Number Formatting option, 95
Values and Source Formatting option, 95
Values area, PivotTables
choosing data to work on, 227
creating PivotTable, 227
formatting PivotTable, 247
grouping with row and column value areas, 237
moving field data, 228
selecting mathematical operations, 238
VBA programming language
exposing VBA worksheet, 333–334
macros, 323
vertical (value) axis major gridlines, charts, 157
vertical value (y) axis, charts, 156
adding axes using Layout tab, 186
adding axis titles using Layout tab, 183
Column chart type, 159
reformatting vertical axis, 175–177
Vertically center page option, printing, 299
View tab
showing/hiding screen elements, 273–276
Workbook Views button group, 275
viruses
anti-macro-virus defense system, 337
Warning error alert
data validation, 59
window arrangement of worksheets
protecting, 286
WordArt
formatting text-based chart elements with, 179
Workbook Views button group, 275, 306
Page Break Preview button, 306
Page Layout button, 312
workbooks, 2
<i>see also </i>spreadsheets; worksheets
creating PivotTable, 224
deleting worksheets, 265
hiding worksheets, 267
making macros available to all, 330
moving between worksheets, 262
printing entire workbook, 301
protecting worksheets, 279–282
recording macros, 323–325, 328
referencing other worksheets, 270–273
saving macros to Personal Macro Workbook, 330–331
scope of ranges, 98
unprotecting, 288
worksheet tabs, 4
<i>see also </i>spreadsheets; workbooks
adding column of numbers, 74–76
adding headers and footers, 312–319
applying formats with styles, 141–142
Auto Fill speeding up data entry, 48–54
automatic recalculation, 75
button groups, 3
cell addresses, 31
cell pointer, 5
cells and addresses in, 261
changing default number of, 262
changing font size, 106
column headings (A, B, C, ...), 3
command tabs, 3
conditional formatting, 145–152
copying, 266
customizing with formulas, 84–95
deleting, 265
deleting columns, 124
deleting rows, 124
dialog box launchers, 5
editing macros in VBA, 333
entering data into selected range, 45–47
entering numerical data, 44
Excel dashboards, 262
exposing VBA worksheet, 333–334
formatting numbers/values, 129–137
formula bar, 3
grid, 31
hiding, 267
hiding/unhiding rows and columns, 125–127
inserting and deleting cells, 128–129
inserting rows, 123
moving between sheets, 262
moving charts between, 161
moving data, 69
Name Box, 3
naming ranges from data in, 99–100
Print Active Sheets option, 290, 291, 292
Print Selection option, 293
printing entire worksheet, 289–290
protecting window arrangement of, 286
Quick Access toolbar, 4
reasons for multiple sheets, 261
recoloring sheet tabs, 266
referring to cells in other sheets, 270–271
renaming sheet tabs, 266
ribbon, 3
row headings (1, 2, 3, ...), 2
scope of ranges, 98
scroll buttons, 4
Select All button, 5
selecting, 269
selecting multiple cells, 34–37
selecting ranges for formulas, 76–77
showing formulas in cells, 274–275
showing/hiding screen elements, 273–276
status bar, 5
ungrouping, 269
unhiding, 267
unprotecting, 282
using ranges on other sheets in formulas, 271–273
viewing and editing formulas, 77–79
viewing sheet tabs, 265
widening and narrowing columns, 41–43
Wrap Text option, 119
wrapping text, 119
x-axis <i>see </i>horizontal category (x) axis, charts
y-axis <i>see </i>vertical value (y) axis, charts