Index

A

absolute cell addressing

keeping cell reference constant, 90–93

Accounting Number Format option

turning values into currency, 129

adding worksheets, 263–264

addressing cells, 2

relative cell addressing, formulas, 88

alignment

data in cells, 39–40, 114–121

data orientation, 116–118

Format Cell Alignment option, 117

horizontal alignment, 114–115

indenting data, 119

Merge and Center options, 120–121

vertical alignment, 115–116

wrapping text, 119

Alignment button group, fonts, 114

Alignment tab, Format Cells dialog, 117

Alt key

additional shortcut keys, 24–25

function key shortcuts, 21–22

key tips, 5

Analyze tab, Chart Tools

filtering data in PivotCharts with field buttons, 256

anti-macro-virus defense system, 337

Area chart type, 158, 166

arguments, functions, 84

Arrow keys, 23

navigating worksheets, 32

Auto Fill

auto filling numeric sequence, 48–49

copying values with, 48

customizing lists, 53–54

speeding up data entry, 48–54

using with text, 50

Auto Fill Option button, 50–53

Copy Cells option, 51, 52

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

functions, 78, 79

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

B

Backspace key, 23

Backstage, 7–8

exiting, 8

File tab, 7

Quick Access toolbar, 9

Print Backstage, 290, 294–300

banded columns, tables, 214

banded rows, tables, 210, 214

Bar chart type, 158, 166

Between rule, conditional formatting, 149

blank cells

function ranges containing, 79

bold (B) button, 107

books <i>see </i>workbooks

borders

adding to cells, 109–111

showing/hiding screen elements, 274

Borders drop-down menu, 109

Line Color/Style options, 111

breaks, printing

adding page breaks, 303–305

previewing page breaks, 305–308

button groups, 3

buttons

accessing with keyboard, 5–6

adding to Quick Access toolbar, 9

customizing Quick Access toolbar, 8

removing from Quick Access toolbar, 11

Select All button, 5

toggling, 107

C

calculations

calculating an average, 79–80

functions, 73–84

cell gridlines <i>see </i>gridlines

cell pointer, 3, 5

cell references

absolute cell addressing, 90–93

clarifying by naming range, 95–102

Name Manager, 101–102

range containing one cell, 100

range in Name Box, 95–98

range from data in worksheet, 99–100

copy-and-paste method, 88

keeping constant, 90–93

moving formulas, 89

relative cell addressing, 88

Cell Styles option

applying ready-made formats with styles, 141

customizing styles, 143

cells, 2

adding border to, 109–111

adding color to, 111–112

addresses, 2, 31

aligning data in, 39–40, 114–121

applying formats with styles, 141–142

Auto Fill speeding up data entry, 48–54

calculating an average, 79–80

changing data, 63–66

conditional formatting, 145–152

copying data, 67–68

data validation, 54–60

deleting contents, 66

deleting columns, 124

deleting rows, 124

determining formatting of, 108

entering data into selected range, 45–47

entering numerical data, 44

entering text and data, 38–39

Format Cell Alignment option, 117

Format Cells dialog box, 113–114

formatting, 34

formatting numbers/values, 129–137

inserting and deleting, 128–129

inserting columns, 122, 123

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 mouse, 34, 35

using Name Box, 37

selecting ranges for formulas, 76–77

showing formulas in, 274–275

sparklines, 189–192

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 axes, 185–188

adding axis title, 183

adding chart title, 181–182

adding data labels, 184

adding data tables, 185

adding legend, 184

adding plot area, 188

chart area, 157

data point, 157

data series, 156, 157

defining, 156–157

horizontal category (x) axis, 156

legend, 157, 184

plot area, 157, 188

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

Chart Styles option, 177, 178

filtering data in PivotCharts with field buttons, 256

Format context tab, 178

Chart Tools contextual tab, 7

chart types

Area, 158, 166

Bar, 158, 166

changing, 163–166

choosing, 157–158

Column, 158

Line, 158, 166

Other, 158

Pie, 158, 167

Scatter, 158

charts, 155–192

adding elements with Chart Layouts, 180–181

changing, 160–167

changing data, 162

changing default, 167

changing type, 163–166

choosing type, 157–158

creating column chart, 159–160

creating from PivotTables, 253–258

creating quickly, 160

defining elements, 156–157

deleting, 160

formatting <i>see </i>formatting charts

moving, 160, 161

PivotCharts, 253–258

resizing, 161

selecting entire chart, 161

sparklines, 189–192

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

clipboard, 69–71

copying data, 67, 68

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

adding to cells, 111–112

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

creating, 159–160

column headings (A, B, C, ...), 3

printing, 319, 320

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 numbers in, 74–76

adding to tables, 213

altering width manually, 41

banded columns, tables, 214

changing font size, 106

deleting, 124

effect on formulas, 125

Freeze Panes option, 276–279

hiding/unhiding, 125–127

inserting, 122, 123

effect on formulas, 124

widening/narrowing, 41–43

auto-fit feature, 42–43

Comma Style button, 132

command tabs, ribbon, 3

commands

macros, 323–338

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

contextual tabs, 6–7

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

data, 67–68

moving data, 69

storing copied/cut data, 69–71

formats with Format Painter, 140–141

formulas, 87–95

copy-and-paste method, 88–89

formula result only, 93–95

values with Auto Fill, 48

worksheets, 266

Count Numbers function

AutoSum button, 79

Create Names from Selection dialog box, 99, 100

Ctrl key shortcuts, 19–21

additional key combinations, 23–25

function key combinations, 21–23

currency

positioning of currency symbol, 132

turning values into, 129–132

Currency format option, 132

Current Selection button group

formatting charts, 171

Custom AutoFilter dialog box

number filters, 208

text filters, 206, 207

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

Format Cells dialog, 138–139

Custom Sort option

Sort & Filter drop-down menu, 200

cutting data, 69

D

dashboards, Excel, 262

data

adding column of numbers, 74–76

aligning in cells, 39–40, 114–121

changing, 63–66

deleting cell contents, 66

Redo button, 66

Undo button, 64

charting, 155–192

color-coordination using chart styles, 177

copying, 67–68

customizing worksheets with formulas, 84–95

entering, 39

filtering, 202–208

formatting, 103

Freeze Panes option, 276–279

functions, 73–84

grouping, 219

indenting, 119

moving, 69

orientation of, 116–118

PivotTables, 219–258

protecting worksheets, 281

sorting, 196–202

storing copied/cut data, 69–71

wrapping text, 119

data aggregation <i>see </i>PivotTables

Data Bars, conditional formatting, 152

data entry, 38–39

adding instructions with input messages, 60–61

data validation, 54–60

into selected range, 45–47

navigating worksheets, 31

numerical data, 44

speeding up with Auto Fill, 48–54

data labels

adding using Layout tab, 184

data point, charts, 157

data series, charts, 156, 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

data validation, 54–60

changing rule, 57

customizing prompt, 60

error alerts, 59–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

filtering data, 202–208

Format as Table button, 213

sorting data, 196–202

Date & Time function group, 83

Date button, Header dialog, 317

dates

formatting, 134–137

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

E

Edit Custom Lists button

customizing auto fill lists, 53

Editing button group, 74

Sort & Filter drop-down menu, 197

editing data

changing data, 63–66

redoing undone edit, 66

undoing edit, 64

elements, screen

showing/hiding, 273–276

End key/mode, 24

Enter key, 24

entering text and data, 38

navigating worksheets, 32

equals sign

writing formulas, 85–86

error alerts

customizing data validation prompt, 60

data validation errors, 59–60

Esc key, 24

Excel 2010, 27

Slicer feature, 242–247

Excel dashboards, 262

F

F1, F2, …

function key shortcuts, 21–23

field buttons

filtering data in PivotCharts, 256–258

removing from PivotCharts, 258

Field List button

creating PivotTables, 226

fields

filtering data, 202–208

filtering multiple fields, 208

sorting data, 196–202

sorting by two fields, 199–201

fields, PivotTables

grouping related items using two fields, 234–237

removing fields from areas, 229, 237

File tab, 7, 9

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

filtering data, 202–208

AutoFilter, 207

clearing filters, 205

Filter option, 6

multiple fields, 208

number filters, 207–208

tables, 210

text filters, 206–207

turning filter off, 205

filtering data, PivotTables

creating report worksheet for items in filter, 231–232

in PivotCharts with field buttons, 256–258

Report Filter area, 229–231

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 drop-down menu, 104, 105

Font tab, Format Cells dialog, 113

fonts

aligning data, 114–121

Alignment button group, 114

changing, 104–105

changing font size, 106–107

strikethrough effect, 113

using bold/italics/underline, 107–108

footers

adding, 312–319

Page Layout view, 312–314

Page Setup dialog, 314–315

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

Custom option, 138–139

Special option, 137–138

Protection tab, 283

Hidden check box, 285, 286

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

copying formats with, 140–141

Format Text button, Header dialog, 317

formatting, 103–152

cells

adding border to, 109–111

adding color to, 111–112

determining formatting, 108

Format Cells dialog, 113–114

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

changing font, 104–105

changing font size, 106–107

using bold, italics, and underline, 107–108

general format, 134

mini-toolbar options, 170

numbers/values, 129–137

Custom format option, 138–139

customizing number formats, 137–139

dates, 134–137

decimal points, 133

percentages, 132

punctuating values, 132

Special formats option, 137–138

turning values into currency, 129–132

PivotTables, 247–249

select-then-do principle, 104

styles, 141–144

formatting charts, 169–188

adding chart elements

with Chart Layouts, 180–181

with Layout tab, 181–188

color-coordination using chart styles, 177

context menu, 171

Current Selection button group, 171

Format Axis dialog, 175–176

Format Data Series dialog, 173

formatting text elements with WordArt, 179

mini-toolbar, 170

reformatting vertical axis, 175–177

restoring default formatting settings, 175

shape styles, 178–179

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

formulas, 73, 75

absolute cell addressing, 90–93, 100

adding column of numbers, 75

automatic recalculation, 75, 86

clarifying cell references by naming range, 95–102

copy-and-paste method, 88–89

copying, 87–95

copying result only, 93–95

customizing worksheets with, 84–95

dollar sign in, 92, 93

effect of deleting columns/rows, 125

effect of inserting columns/rows, 124

hiding, 284–286

hiding worksheets, 267

mathematical operators, 85

moving, 89

order of operations in, 86–87

referring to cells in other worksheets, 270–271

relative cell addressing, 88

selecting ranges for, 76–77

showing in cells, 274–275

tables, 212

using ranges on other sheets in, 271–273

viewing and editing, 77–79

writing, 85–86

Freeze Panes option, 276–279

Full Screen option, 275, 276

function groups, 83

function key shortcuts, 21–23

Function Library

locating functions in, 82–84

types (groups) of functions, 82

functions, 73–84

adding column of numbers, 74–76

arguments, 84

AutoSum button, 78, 79

calculating an average, 79–80

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

If, 80, 81

Max/Min, 79

Sum, 75

G

general format, 134

Gradient fill

formatting charts, 174

Greater Than rule, conditional formatting, 146

grid

size of, 31

gridlines

printing, 319–320

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

grouping worksheets, 268–269

H

hash marks (UK)

viewing numerical data, 44

header rows

changing PivotTable headers, 250

Freeze Panes option, 276–279

printing titles, 309

sorting data, 198

tables, 210

headers

adding, 312–319

Page Layout view, 312–314

Page Setup dialog, 314–315

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

Format Cells dialog, 285, 286

hiding formulas, 284–286

hiding ribbon, 275–276

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 alignment, 114–115

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

I, J

If function, 80, 81

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

inserting columns, 122, 123

effect on formulas, 124

inserting rows, 123

effect on formulas, 124

inserting worksheets, 263–264

italics (I) button, 107

K

key tips, 5, 6

accessing Data tab with, 6

keyboard

accessing buttons with, 5–6

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

L

Landscape option, printing, 297

layout options, PivotTables, 251–253

plus/minus option, 252

Layout tab

adding chart elements using, 181–188

axes, 185–188

axis title, 183

chart title, 181–182

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 chart type, 158, 166

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

M

Macro Options dialog

activating macros with keyboard shortcut, 336

macros, 323–338

activating with keyboard shortcut, 335–337

anti-macro-virus defense system, 337

deleting, 332

editing in VBA, 332–334

exposing VBA worksheet, 333–334

making available to all workbooks, 330

playing back, 327, 328

recording, 323–325, 328

relative references in, 328–330

saving to Personal Macro Workbook, 330–331

saving workbook with, 326–327

security, 337–338

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

N

Name Box, 3

clarifying cell references by naming range in, 95–98

navigating worksheets, 33

selecting multiple cells, 37

Name Manager, 101–102

navigating worksheets, 31–34

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

number filters, 207–208

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

Custom option, 138–139

Special option, 137–138

numbers

<i>see also </i>values

adding column of, 74–76

aligning data in cells, 39

auto filling numeric sequence, 48–49

customizing formats, 137–139

dates, 134–137

entering numerical data, 44

formatting decimal points, 133

formatting values, 129–137

percentages, 132

punctuating values, 132

turning values into currency, 129–132

O

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

orientation, data, 116–118

Outline layout option, PivotTables, 251, 252

P

Page Break Preview, 306

page breaks, printing

adding, 303–305

previewing, 305–308

Page Down key, 25

Page Layout tab, 302

Breaks options, 303–305

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

printing titles, 308–311

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

saving macros to, 330–331

PgDn/PgUp keys

navigating worksheets, 32

Phone Number option

formatting numbers, 138

Pie chart type, 158, 167

PivotChart Tools contextual tab, 255

PivotCharts, 253–258

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

PivotTables, 219–258

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

counting records, 232–233

creating, 224–227

creating charts from, 253–258

creating with PivotChart, 258

filtering using Report Filter area, 229–231

formatting, 247–249

grouping data, 239–240

related items using two fields, 234–237

layout options, 251–253

plus/minus option, 252

moving field data, 228–229

Outline layout option, 251, 252

pivoting data sideways, 229

refreshing, 240

removing fields from areas, 229, 237

Report Filter area, 229–231

creating report worksheet for items in filter, 231–232

Row Labels area, 226, 227, 228, 234–236

Slicer feature, 242–247

styling reports, 249–253

Tabular layout option, 251, 252

Undo command, 224

Values area, 227, 228

changing calculation, 237–238

grouping with row/column value areas, 237

selecting mathematical operations, 238

viewing filtered records, 242–247

playing back macros, 327, 328

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

setting, 300–302

Print Backstage, 290, 294–300

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

Print Titles, 308–310

headers compared, 312

printing

actual row/column headings, 319, 320

adding headers and footers, 312–319

adding page breaks, 303–305

collating copies, 296

customizing, 302–311

deciding pages to print, 295

deciding what to print, 289–294

default Print Active Sheets option, 295

fitting all columns on one page, 300

gridline, 319–320

margins, 297, 298, 302

options, 294–300

orientation, 297, 302

page centering, 299

Page Layout tab, 302

paper size, 297, 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

setting Print Area, 300–302

titles, 308–311

printing options, 294–300

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 workbooks, 286–288

protecting worksheets, 279–282

hiding formulas, 284–286

part of worksheet, 282–286

Protection tab, Format Cells dialog, 283

Hidden check box, 285, 286

Locked check box, 284

punctuating values, 132

Q

Quick Access toolbar, 4

adding button to, 9

customizing, 8–12

removing button from, 11

R

ranges of cells

calculating an average, 79–80

changing font, 104

changing font size, 106

clarifying cell references by naming, 95–102

Name Manager, 101–102

range containing one cell, 100

range in Name Box, 95–98

ranges from data in worksheet, 99–100

converting table to, 217

copying data, 67–68

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

selecting for formulas, 76–77

sparklines, 189, 190

using on other sheets in formulas, 271–273

Recently Used function group, 83

Record Macro dialog box

activating macros with keyboard shortcut, 335

recording macros, 323–325

records

adding to PivotTable, 241

adding to tables, 209–216

counting, PivotTables, 232–233

databases, 195

filtering data, 202–208

finding/removing duplicates, 216–217

sorting data, 196–202

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

hiding, 275, 276

row headings (1, 2, 3, ...), 2

printing, 319, 320

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

banded rows, tables, 210, 214

changing font size, 106

deleting, 124

effect on formulas, 125

Freeze Panes option, 276–279

header rows sorting data, 198

heightening, 116

hiding/unhiding, 125–127

inserting, 123

effect on formulas, 124

Total Row selection, tables, 214

rules <i>see </i>conditional formatting

S

scaling options, printing, 300

Scatter chart type, 158

scope

ranges of cells, 98

screen elements

showing/hiding, 273–276

scroll buttons, 4

navigating worksheets, 32

scrolling

Freeze Panes option, 276, 278

security

anti-macro-virus defense system, 337

macros, 337–338

Select All button, 5

selecting worksheets, 269

select-then-do principle, formatting, 104

Set Print Area option, 301

shape styles

formatting charts, 178–179

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

shortcuts, 18–25

activating macros, 335–337

arrow keys, 23

Backspace key, 23

copying data, 68

creating table, 210

Ctrl key, 19–21

cutting data, 69

Delete key, 23

End key, 24

Enter key, 24

Esc key, 24

function keys, 21–23

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

sorting data, 196–202

by cell format, 201–202

by two fields, 199–201

Spacebar key, 25

sparklines, 189–192

deleting, 192

grouping of, 191

modifying, 191

representing binary values with, 192

Special option, Number tab

Format Cells dialog box, 137–138

spreadsheets, 28–29

<i>see also </i>workbooks; worksheets

automatic recalculation, 75

macros, 323–338

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

customizing, 142–144

formatting charts using shape styles, 178–179

formatting with, 141–144

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

T

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

banded rows, 210, 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

PivotTables, 219–258

Total Row selection, 214

turning database into, 209–216

tabs, worksheets, 4

contextual tabs, 6–7

File tab, 7

grouping sheets, 269

moving, 264, 266

moving between sheets, 262

recoloring, 266

renaming, 266

selecting sheets, 269

viewing, 265

Tabular layout option, PivotTables, 251, 252

text

aligning in cells, 39

entering, 38–39

formatting chart elements with WordArt, 179

revealing in cells, 40

using Auto Fill with, 50

wrapping, 119

text filters, 206–207

Text Filters drop-down menu, 206

Text function group, 83

Time button, Header dialog, 317

titles

adding with Merge and Center, 120–121

printing, 308–311

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

U

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

V

validation, data, 54–60

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

dates, 134–137

displaying based on conditions, 80–82

formatting decimal points, 133

formatting, 129–137

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

changing calculation, 237–238

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

editing macros in, 332–334

exposing VBA worksheet, 333–334

macros, 323

vertical (value) axis major gridlines, charts, 157

vertical alignment, 115–116

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

W

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

Normal button, 308, 314

Page Break Preview button, 306

Page Layout button, 312

workbooks, 2

<i>see also </i>spreadsheets; worksheets

adding worksheets, 263–264

creating PivotTable, 224

deleting worksheets, 265

grouping worksheets, 268–269

hiding worksheets, 267

making macros available to all, 330

moving between worksheets, 262

printing entire workbook, 301

protecting, 286–288

protecting worksheets, 279–282

recording macros, 323–325, 328

referencing other worksheets, 270–273

saving macros to Personal Macro Workbook, 330–331

saving with macro, 326–327

scope of ranges, 98

unprotecting, 288

worksheet tabs, 4

worksheets, 1–5

<i>see also </i>spreadsheets; workbooks

adding, 263–264

adding column of numbers, 74–76

adding headers and footers, 312–319

aligning data in cells, 39–40

applying formats with styles, 141–142

Auto Fill speeding up data entry, 48–54

automatic recalculation, 75

button groups, 3

calculating an average, 79–80

cell addresses, 31

cell pointer, 5

cells and addresses in, 261

changing data, 63–66

changing default number of, 262

changing font size, 106

column headings (A, B, C, ...), 3

command tabs, 3

conditional formatting, 145–152

copying, 266

copying data, 67–68

customizing with formulas, 84–95

data validation, 54–60

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

entering text and data, 38–39

Excel dashboards, 262

exposing VBA worksheet, 333–334

filtering data, 202–208

formatting numbers/values, 129–137

formula bar, 3

Freeze Panes option, 276–279

functions, 73–84

grid, 31

grouping, 268–269

hiding, 267

hiding formulas, 284–286

hiding/unhiding rows and columns, 125–127

inserting and deleting cells, 128–129

inserting columns, 122, 123

inserting rows, 123

moving between sheets, 262

moving charts between, 161

moving data, 69

moving sheet tabs, 264, 266

Name Box, 3

naming ranges from data in, 99–100

navigating, 31–34

Print Active Sheets option, 290, 291, 292

Print Selection option, 293

printing entire worksheet, 289–290

printing part of, 290–294

protecting, 279–282

protecting part of, 282–286

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

sorting data, 196–202

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

writing formulas, 85–86

Wrap Text option, 119

wrapping text, 119

X

x-axis <i>see </i>horizontal category (x) axis, charts

Y, Z

y-axis <i>see </i>vertical value (y) axis, charts