Index

Special Characters & Numbers

& calculation operator, 169, 173

% calculation operator, 168, 173

* calculation operator, 167, 173

/ calculation operator, 168, 173

/Applications/Solver/ folder, 345

^ calculation operator, 173

~/Library/Application Support/Microsoft/Office/Chart Templates/ folder, 251

+ button, 93, 112

+ calculation operator, 167, 173

<> calculation operator, 168, 173

< (Previous) button, 85

= calculation operator, 168, 173

=SUM( ) function, 179

> calculation operator, 168, 173

> (Next) button, 85

< calculation operator, 168, 173

>= calculation operator, 168, 173

<= calculation operator, 168, 173

3-D Clustered Column chart type, 232

3-D Format category, Format dialog box, 250

3-D Rotation category, Format dialog box, 250

A

ABS mathematical and trigonometric function, 213

Absolute reference, 166

Accept All button, Accept or Reject Changes dialog box, 402

Accept All Mine button, Resolve Conflicts dialog box, 401

Accept All Others button, Resolve Conflicts dialog box, 401

Accept button, Accept or Reject Changes dialog box, 402

Accept or Reject Changes dialog box, 402

Accept Other button, Resolve Conflicts dialog box, 400

Accounting number format, 127

ACCRINT financial function, 202

ACCRINTM financial function, 202

ACOS mathematical and trigonometric function, 213

ACOSH mathematical and trigonometric function, 213

Actions pop-up button, Ribbon, 118

active cell, changing, 18–19

Active Sheets option button, Print dialog box, 385

ActiveWindow.ActivatePrevious statement, 426

ActiveWorkbook.Close statement, 426

ActiveWorkbook.Save statement, 426

Add a Toolbar dialog box, 70, 419

Add alias to Clip Gallery option button, Import dialog box, 280

Add All button, 27

Add button, 27, 41–42

Add Constraint dialog box, 346–347

Add from Clipboard option, 41

Add-ins available list box, Add-Ins dialog box, 345

Add-Ins dialog box, 344–345

Add option button, 139

Add Scenario dialog box, 335–336, 338

Add to Basket link, 282

Add To Sidebar check box, 95

Add View dialog box, 52

ADDRESS lookup and reference function, 212

Adjust column width check box, 315

Advanced button, 26

Advanced tab, Share Workbook dialog box, 398

Advanced Text Import Settings dialog box, 26

After pressing RETURN, move selection check box, 60

AGGREGATE mathematical and trigonometric function, 213

Alignment group, Ribbon, 124, 133–134, 150

alignment, of cell content, 133–134

Alignment tab, Format Cells dialog box, 133–134

All category, 3, 85

All check box, 272

All Clips item, Import dialog box, 279

All Commands item, 67

All except borders option button, 139

All item, Highlight Changes dialog box, 396

All Open Workbooks item, Macro dialog box, 417

All option button, 57, 139

All Pictures In This File option button, Reduce File Size dialog box, 295

All references list box, Consolidate dialog box, 405

All Symbols in the pop-up menu, 45

All using Source theme option button, 139

Allow multiple filters per field check box, PivotTable Options dialog box, 372

Allow online access check box, 15

Allow pop-up menu, 146

Allow users of this sheet to box, 395

Always Add Clipping Selected dialog box, 42

Always Add Copy option, 42

Always calculate before saving workbook check box, 222

Always create backup check box, 88

AMORDEGRC financial function, 202

AMORLINC financial function, 202

An open workbook item, Macro dialog box, 418

AND logical function, 209

And option button, 28

Angle knob, Format dialog box, 248

Any pop-up menu, 94

Any value item, 147

Appearance pop-up menu, 80

Apple iPhoto, inserting pictures in workbooks from, 284–285

Apple Mac file system, inserting pictures in workbooks from, 285–286

AppleScript Editor utility, 410

Applications folder, 410

Applications/Utilities folder, 410

Applies to column, 145

Apply Styles button, 110

Apply to area, Reduce File Size dialog box, 295

Archive.xlsx workbook, 415–416

Area Chart Category, 231

AREAS lookup and reference function, 212

Arguments pane, 190

Arrange pop-up menu, 49

Arrange submenu, context menu, 301

Arrange Windows dialog box, 48–49

arranging workbook windows, 48–49

As box, 38

Ascending button, 323, 377

ASIN mathematical and trigonometric function, 213

ASINH mathematical and trigonometric function, 213

Ask to update automatic links check box, 61

Assign button, 68

Assign Macro context menu item, 419, 422

Assign Macro dialog box, 419–420, 422

ATAN mathematical and trigonometric function, 213

ATAN2 mathematical and trigonometric function, 213

Author box, 91

Author field, 91

Authoring area, Excel Preferences dialog box, 84, 123, 252

Authors property, 95

AutoCorrect, 29–33

creating entries for, 32–33

exceptions for, 31–32

options for, 29–33

AutoCorrect actions button, 317

AutoCorrect Exceptions dialog box, 31–32

AutoCorrect pane, 33

AutoFill, 33–37

built-in capabilities of, 33–35

custom lists for, 36–37

using with formulas, 174–175

Automatic item, Format dialog box, 248

Automatic option button, PivotTable Options dialog box, 375

Automatic styles check box, 110

Automatically convert date system check box, 61

Automatically every option button, Share Workbook dialog box, 399

Automatically except for data tables option button, 221

Automatically Expand Tables As I Type check box, Tables preferences pane, 317

Automatically option button, 221

automating tasks, 409–428

macros

editing in Visual Basic Editor tool, 423–428

recording, 411–414

running, 416–422

options for, 410–411

Automator application, 410

AutoRecover, 64–65

AutoSum button, 187, 189

AutoSum pop-up menu, inserting functions with, 187–188

Average function, 366, 404

AVERAGE statistical function, 217

axes

adding labels from a separate range, 244

changing scale or numbering of, 240–243

of charts, 229

Axes dialog box, 273

Axis labels box, Format Axis dialog box, 243

axis titles, 229–230

B

Background color pop-up menu, Format dialog box, 249

BAHTTEXT text function, 218

Banded Columns check box, 310

Banded Columns item, Row & Column pop-up menu, 370

Banded Rows check box, 309

Banded Rows item, Row & Column pop-up menu, 370

Bar Chart Category, 230

Base box, Format Axis dialog box, 242

Before sheet list box, 100, 416

Begin a group check box, 73, 76, 421

BESSELI engineering function, 199

BESSELJ engineering function, 199

BESSELK engineering function, 199

BESSELY engineering function, 199

Best for printing (220 ppi) item, Reduce File Size dialog box, 296

Best for sending in e-mail (96 ppi) item, Reduce File Size dialog box, 295

Best for viewing on screen (150 ppi) item, Reduce File Size dialog box, 295

BIN2DEC engineering function, 199

BIN2HEX engineering function, 199

BIN2OCT engineering function, 199

Blanks pop-up menu, Ribbon, 369

Book pop-up menu, Merge Scenarios dialog box, 340

Border Options item, Borders pop-up menu, 137

Border tab, 137, 142

Borders button, 136

borders, formatting, 136–137

Borders pop-up menu, Ribbon, 124, 136–137

Bottom row check box, 105

Breaks pop-up menu, 383

brightness, adjusting, 291–292

Bring Forward command, Arrange submenu, 301

Bring to Front command, Arrange submenu, 301

Bubble Chart Category, 231

Built-in Menus item, 73

buttons, changing appearance of, 71

By Changing Cell text box, Goal Seek dialog box, 343

By Changing Cells box, Solver Parameters dialog box, 346

By color pop-up menu, AutoFilter window, 323

By label pop-up menu, sorting and filtering window, 378

by N page(s) tall box, Print dialog box, 385

By value pop-up menu, sorting and filtering window, 378

C

Calc Now button, 222

Calc Sheet button, 222

Calculate sheets box, 221–222

Calculate sheets options, 220

calculating worksheets, controlling when to, 221–222

Calculation icon, Excel Preferences dialog box, 128, 220

calculation operator, 167–168, 173

Calculation preferences pane, 220, 222–223

Cancel button, 149

Capitalize first letter of sentences check box, 31

Capitalize names of days check box, 31

Cascade option button, 49

Case Sensitive check box, 320

Categories button, Clip Gallery application, 283

Categories dialog box, 283

categories, in charts, 229

Categories list box, 67, 70–71, 73, 75, 419

Categories list, Customize Toolbars and Menus dialog box, 339

Categories tab, Properties dialog box, 280

Category list box

Clip Gallery application, 277

Format Cells dialog box, 128

Category (X) axis labels text box, 244

CEILING mathematical and trigonometric function, 213

CEILINGPRECISE mathematical and trigonometric function, 213

CELL information function, 210

[cell or range reference] [space][cell or range reference] calculation operator, 169

Cell Reference box, Add Constraint dialog box, 347

[cell reference] [cell reference] calculation operator, 168

Cell Styles panel, 151, 155

cells

active, changing, 18–19

deleting, 119–120

formatting, 122–138

alignment of cell content, 133–134

borders and fills, 136–137

fonts, 135

of numbers, 125–132

protecting cells, 137–138

and themes, 133

three tools for, 123–125

inserting, 119

positioning graphical objects relative to, 299–301

protecting, 137–138

referring to in formulas, 164–165

selecting, 19–21

Cells group, Ribbon, 118

Center section box, 157

Change Chart Type group, Ribbon, 234

Change PivotTable Data Source dialog box, 376

Change SmartArt Graphic group, Ribbon, 298

Change Type group, Ribbon, 272

Changing Cells text box, Add Scenario dialog box, 335–336

CHAR text function, 218

chart area, 229

chart axes, 229

Chart data range box, Select Data Source dialog box, 236

Chart Elements pop-up menu, 249

Chart icon, Excel Preferences dialog box, 252

Chart Layout tab, 6, 256, 259

Chart (linked to Excel data) option button, 258

Chart Menu Bar check box, 76

Chart pane, Excel Preferences dialog box, 252

Chart preferences pane, Excel Preferences dialog box, 252

Chart Quick Layouts group, Ribbon, 236

Chart ScreenTips area, Excel Preferences dialog box, 253

Chart Styles group, Ribbon, 238

Chart Title placeholder, 239

chart titles, 229–230

Charting category, 67

charts, 225–259

choosing types, 230–231

components of, 227–230

categories and data series, 229

chart area and plot area, 229

chart axes, 229

chart title and axis titles, 229–230

data markers, gridlines, and data labels, 230

creating

changing chart types, 234

changing from embedded charts to chart sheets, 233–234

changing source data, 235–236

overview, 231–232

switching rows and columns, 235

custom types, 251

embedded, 225–227

formatting

copying to another chart, 250–251

individual chart elements, 249–250

walls and floors, 246–249

laying out

adding axis labels from a separate range, 244

adding separate data series, 237–238

applying styles, 238

axis titles, 239–240

changing scale or numbering of axes, 240–243

choosing gridlines to display, 245–246

data labels, 244–245

legends, 243–244

titles, 239

Preferences dialog box, 252–253

sheets, 225–227

using in Word documents and PowerPoint presentations, 254–259

creating charts in embedded workbooks, 255–257

pasting charts from Microsoft Excel, 257–259

Charts tab, Ribbon, 231–232, 234, 236, 238, 254–256, 259, 271

Check compatibility with pop-up menu, 96

Check documents for compatibility check box, 64

Check for Errors button, 176, 180

Choose a Database dialog box, 22

Choose a File dialog box, 22, 41

Choose a Folder, Microsoft Excel dialog box, 62, 80

Choose a Picture dialog box, 249, 285–286

Choose a Workbook dialog box, 405

Choose an Add-In dialog box, 345

Choose button, 3, 23, 41, 62, 80, 84–85

CHOOSE lookup and reference function, 212

Choose One pop-up menu, 323

Choose Picture button, Format dialog box, 249

Choose where to place the PivotTable area, Create PivotTable dialog box, 356

Circular readout, status bar, 183

circular references, troubleshooting formulas, 182–183

CLEAN text function, 218

Clear button, 274

Clear Filter button

AutoFilter window, 324

sorting and filtering window, 379

clearing conditional formatting, 145

clip art, 276–283

Clip Art Browser pane, 276–277

Clip Gallery application, 277–283

adding pictures to, 278–282

downloading clip art items from Microsoft Office web site, 282–283

organizing with categories, 283

Clip Gallery Images item, Import dialog box, 279

Clip Gallery Movies item, Import dialog box, 279

Clip Gallery Packages item, Import dialog box, 279

Clip Gallery Sounds item, Import dialog box, 279

Close All command, 11

Close button, 13, 44–45

Close command, 11

Close Effect pop-up menu, 14

Close Full Screen toolbar, 46

Close method, 426

Close option button, 14

CODE text function, 218

Code window, Visual Basic Editor application, 424–425

cog button, 77, 79

collaborating with other PCs, 381–407

exporting data to CSV files, 388–389

workbooks

documenting, 389–392

merging, 402

sharing, 393–402

worksheets

consolidating, 403–407

printing, 381–385

Collapse Dialog button

Add Scenario dialog box, 336

Change PivotTable Data Source dialog box, 376

Consolidate dialog box, 405

Create PivotTable dialog box, 356

Goal Seek dialog box, 343

Highlight Changes dialog box, 396

Select Data Source dialog box, 236, 244

Solver Parameters dialog box, 346

Collapse option button, 14

Collapse Ribbon button, 8

collapsible worksheets, 107–113

changing settings for outlining, 110

creating outline automatically, 108–109

creating outline manually

grouping rows or columns, 111

ungrouping rows or columns, 112

expanding and collapsing outline, 112

removing outline, 113

updating outline after adding or deleting rows or columns, 112–113

collapsing Ribbon, 8–9

Color and transparency area, Format dialog box, 248

Color drop-down panel, 293

Color pop-up menu, Format dialog box, 248

Color pop-up palette, 58

Color Scale panel, 266

color scales, 261–274

Color Scales panel, Conditional Formatting panel, 141

colors, adjusting, 291–292

Colors pop-up menu, 133

Column Area, PivotTable Builder window, 358

Column button, Ribbon, 232, 271–272

Column Chart Category, 230

Column data format box, 25

Column Headers item, Row & Column pop-up menu, 370

Column headings, 16

Column Input Cell box, Data Table dialog box, 330

Column Labels box, PivotTable Builder window, 358, 360, 364–365

Column Level button, 112

COLUMN lookup and reference function, 212

Column-oriented data table, 329

Column pop-up menu, 319

column sparkline, 270

Column Width dialog box, 121–122

Column Width item, context menu, 121–122

Column widths option button, 139

columns

deleting, 119

in formulas, making one column refer to another, 166–167

freezing of, 50–51

hiding, 122

inserting, 118

setting width of, 121–122

switching data to rows, with paste command, 38–39

switching with rows, 235

Columns box, 321

COLUMNS lookup and reference function, 212

Columns option button, 111–112

COMBIN mathematical and trigonometric function, 213

comma-separated values files. See CVS files

Command Properties dialog box, 71, 73, 75–76, 420–421

Command Properties item, context menu, 76

Commands box, 71, 73

commands, four ways to give to Excel, 4

Commands list box, 67, 71, 75, 339, 419

Commands tab, Customize Toolbars and Menus dialog box, 70–71, 73, 339, 419

Commas option button, 40

Comment text box, Add Scenario dialog box, 336

comments, 390–392

deleting, 392

displaying, 57

viewing, 391–392

Comments and indicators option button, 57

Comments option button, 139, 391

Comparison pop-up menu, 142

Comparisons category, 152

Compatibility preferences pane, 63–64, 86

Compatibility Report palette, 13, 96

COMPLEX engineering function, 199

compressing pictures, 295–296

CONCATENATE text function, 218

conditional formatting, 140–145

applying

changing order of, 144–145

overview, 142–143

clearing, 145

creating custom, 143–144

preset types of, 140–141

Conditional Formatting panel, 140, 142

Confirm Password dialog box, 88

Conflicting change between users area, Share Workbook dialog box, 399

conflicts, resolving in shared worksheets, 400–401

Connection area, PivotTable Options dialog box, 375

Consolidate dialog box, 404–407

Constraint box, Add Constraint dialog box, 347

Contents tab, 90–91

Contextual ScreenTips check box, PivotTable Options dialog box, 371

contrast, adjusting, 291–292

controls, on Ribbon, 7–8

CONVERT engineering function, 199

Convert Text to Table dialog box, 40

Copies & Pages item, Presets pop-up menu, 384

Copy button, Standard toolbar, 39, 258

Copy cell styles from an open workbook list box, 154

Copy command, 39, 175, 250, 277, 426

Copy into Clip Gallery option button, Import dialog box, 279

Correct TWo INitial CApitals check box, 31

Corrections panel, 291

COS mathematical and trigonometric function, 213

COSH mathematical and trigonometric function, 213

COUNT( ) function, 185, 366

Count function, 404

Count Numbers function, 404

COUNT statistical function, 217

COUNTBLANK statistical function, 217

COUNTIF statistical function, 217

COUPDAYBS financial function, 202

COUPDAYS financial function, 203

COUPDAYSNC financial function, 203

COUPNCD financial function, 203

COUPNUM financial function, 203

COUPPCD financial function, 203

Create a copy check box, 100, 416

Create button, 110

Create links to source data check box, Consolidate dialog box, 405–406

Create Names dialog box, 105

Create names in box, 105

Create PivotTable dialog box, 355–356

Criteria button, 312

Crop button, Ribbon, 293–294

Crop pop-up arrow button, 294

Crop tool, 294

cropping pictures, 293–294

Cross option button, Format Axis dialog box, 242–243

CSV (comma-separated values) files

exporting data to, 388–389

importing data from, 23–26

using paste with, 40

CSV file option button, 22

CUMIPMT financial function, 203

CUMPRINC financial function, 203

Currency format, 170

Currency number format, 127, 328, 331

Current formula readout, 17

Current keys list box, 67

Current Selection box, Ribbon, 240

Current Selection group, Ribbon, 247, 249

Custom area, Styles panel, 153

Custom AutoFilter window, 323

Custom Button, Customize Toolbars and Menus dialog box, 419

Custom item, 147

Custom List dialog box, 319

Custom Lists dialog box, 36–37

custom lists, for AutoFill, 36–37

Custom Lists item, 36

Custom Menu Item, Customize Toolbars and Menus dialog box, 419

Custom number format, 127

Custom section, Quick Styles panel, 369

Custom tab

Properties dialog box, 90

properties for workbooks on, 91–93

custom views, 51–52

Custom Views dialog box, 51–52

Customer column, 352

Customer field, 365

Customize Footer button, 156

Customize Header button, 156

Customize Keyboard dialog box, 66–68

Customize Ribbon Tab Order item, context menu, 79

Customize Toolbars and Menus dialog box, 13, 68–71, 73–74, 76, 339, 419, 422

Customize Toolbars and Menus item, context menu, 68, 74

Cut, copy, and sort objects with cells check box, 61

D

data

adding separate series to charts, 237–238

entering in tables, 310–312

importing from FileMaker Pro databases, 317

labels, 230, 244–245

markers, 230

refreshing, from databases, 316

series, 229

source, changing, 235–236

Data and Model category, 152

Data area, PivotTable Options dialog box, 375

data bars, 261–274

creating, 262–265

in different cells than data, 263–265

in same cells as data, 262–263

overview, 261–262

Data Bars panel, 141, 262

data-entry form dialog box, 311

data-entry forms, entering data using, 311–312

data input, 21–45

connecting worksheet to external data sources, 29

importing data, 21–29

from comma-separated values file, 23–26

from FileMaker Pro Database, 26–28

from HTML file, 29

from text file, 23–26

of symbols, 44–45

using AutoCorrect, 29–33

creating entries for, 32–33

exceptions for, 31–32

options for, 29–33

using AutoFill, 33–37

built-in capabilities of, 33–35

custom lists for, 36–37

using find and replace, 42–44

using paste, 37–40

with comma-separated data, 40

switching data from rows to columns, 38–39

with table from word, 39

using Scrapbook, 40–42

adding item to, 41–42

deleting item from, 42

inserting item from, 42

opening, 40–41

Data Labels pop-up menu, 244

Data layout box, 315

Data pop-up menu, 147

Data preview box, 23–24

Data tab, 6, 322, 374–375

Data Table dialog box, 330–331

data tables, 328–331

one variable, 328–331

two variables, 331

data validation, 146–150, 392

Data Validation dialog box, 146, 150, 392

database functions, 195–196

databases, creating using tables, 305–326

in Microsoft Excel, 305–324

using functions with tables, 324–326

date and time functions, 197–198

DATE date and time function, 197

Date item, 147

Date number format, 127

Date option button, 25

DATEVALUE date and time function, 197

DAVERAGE( ) function, 196, 325

Day column, 352

DAY date and time function, 197

days box, Share Workbook dialog box, 398

DAYS360 date and time function, 197

DB financial function, 203

DCOUNT database function, 196

DCOUNTA database function, 196

DDB financial function, 204

Debug toolbar, Visual Basic Editor application, 425

DEC2BIN engineering function, 199

DEC2HEX( ) function, 190, 200

DEC2OCT engineering function, 200

Decimal item, 147

Decimal separator pop-up menu, 26

Decrease Font Size button, Ribbon, 124

default folder, for opening workbooks, 62

default format, for saving workbooks, 63–64

Default Style item, 72, 421

Define Name dialog box, 102, 106–107, 333

DEGREES mathematical and trigonometric function, 213

Delete All option, 42

Delete button, 32–33, 42, 93, 118, 423

Delete command, 392

Delete Command item, context menu, 76

Delete Comment command, 392

Delete dialog box, 119–120

Delete drop-down button, 42

Delete item, context menu, 99, 119

Delete pop-up button, Ribbon, 119

Delete Visible option, 42

deleting

named ranges, 106

worksheets, 99

Delimiters box, 24

DELTA engineering function, 200

Descending button, 323, 377

Description area, 67

Description box, Macro Options dialog box, 419

Description of this clip box, Properties dialog box, 280

Description tab, Properties dialog box, 280

Description text box, Record Macro dialog box, 413, 415

Design group, 367–369

Detail view, 41

Developer check box, 411

Developer tab, Ribbon, 409, 411, 414

DGET database function, 196

Direction box, 110

Direction pop-up menu, 60, 248

disabling Ribbon, 77–80

DISC financial function, 204

Display area, 144, 270

Display items with no data area, PivotTable Options dialog box, 372

Display tab, 371–372

Display this number of decimal places check box, 61

Display units pop-up menu, Format Axis dialog box, 242

Divide option button, 139

DMAX database function, 196

DMIN database function, 196

Do not import column (Skip) option button, 25

Dock check box, 69

Dock column, 69

Dock Toolbar in Window check box, Formatting toolbar context menu, 10

docking Formatting toolbar, 10–11

DOLLAR text function, 218

DOLLARDE financial function, 204

DOLLARFR financial function, 204

Don't Ask Me Again check box, Launch Browser dialog box, 282

Don't capitalize after text box, 32

Don't keep change history option button, Share Workbook dialog box, 398

Don't Move Or Size With Cells option button, Format dialog box, 300

Don't Save button, 389

Don't Show Again item, 97

Double-click allows editing directly in the cell check box, 59

Doughnut Chart Category, 231

Download button, 282

Download link, 282

Downloads folder, 283–284

DPRODUCT database function, 196

dragging contents, of Ribbon, 79

DSTDEV database function, 196

DSTDEVP database function, 196

DSUM database function, 196

duplicate records, identifying and removing from databases, 320–322

DURATION financial function, 204

DVAR database function, 196

DVARP database function, 196

E

EDATE date and time function, 198

Edit button, 257, 259, 338, 424

Edit Comment command, 392

Edit Formatting Rule dialog box, 145, 264–267, 269–270

Edit group, Ribbon, 138

Edit icon, 34

Edit pane, 34

Edit preferences pane, 59

Edit Rule button, 145, 264, 266, 269

Edit Scenario dialog box, 335–336, 338

Edit Scenarios check box, Protect The Sheet And Contents Of Locked Cells dialog box, 338

Edit Sparklines dialog box, 272

Edit toolbar, Visual Basic Editor application, 425

editing, allowing in shared worksheets, 397–399

Editing tab, Share Workbook dialog box, 398

Editor button, 411

Editor interface, 424–425

EFFECT financial function, 204

Effects drop-down panel, Ribbon, 288

elements, of user interface, 15–17

embedded charts

changing to chart sheets, 233–234

overview, 225–227

embedded workbooks, creating charts in, 255–257

Empty cells as check box, PivotTable Options dialog box, 371

Empty cells plotted as box, Excel Preferences dialog box, 253

Enable background error checking check box, 176

Enable fill handle and cell drag-and-drop check box, 34, 60

Enable pop-up menu, Import dialog box, 279

Enable show details check box, PivotTable Options dialog box, 375

End time box, 147

engineering functions, 199–202

Enter button, Formula bar, 171, 173, 193

Entire column option button, 120

Entire row option button, 120

Entire Workbook option button, Print dialog box, 385

EOMONTH date and time function, 198

ERF engineering function, 200

ERFC engineering function, 200

Error Alert tab, Data Validation dialog box, 150, 392

Error Checking icon, 175

Error Checking pane, 175

Error Checking preferences pane, 180

error dialog box, 149–150

Error values as check box, PivotTable Options dialog box, 371

ERROR.TYPE information function, 210

EVEN mathematical and trigonometric function, 213

Everyone but me item, Highlight Changes dialog box, 396

Everyone item, Highlight Changes dialog box, 396

EXACT text function, 218

Excel 97-2003 Workbook format, 64

Excel 97-2004 Workbook format, 86, 96

Excel 97-2004 (.xlt) option, 115

Excel Binary Workbook format, 64

Excel Cannot Calculate a Formula dialog box, 182

Excel Chart (entire workbook) option button, 258

Excel Keyboard Shortcuts topic, 12

Excel Macro-Enabled Template (.xltm) option, 115

Excel Macro-Enabled Workbook format, 64

Excel, Microsoft. See Microsoft Excel

Excel startup folder, 81

Excel Template (.xltx) option, 114

Excel Workbook format, 63–64, 95–96

Excel Workbook Gallery dialog box, 3, 56, 81, 83–85, 113, 307

Excel Workbook icon, 3

Excel Workbook item, 84

exceptions, for AutoCorrect, 31–32

Exceptions link, 31

Executive Overview worksheet, 390

Existing sheet option button, 314

Existing worksheet option button, 356

EXP mathematical and trigonometric function, 213

Expand/collapse triangles check box, PivotTable Options dialog box, 371

Expand Ribbon button, 9

Expand ribbon when workbook opens check box, 79

Explanation box, 97

exporting data, to CSV files, 388–389

Extend data range formats and formulas check box, 60

External Data Range Properties dialog box, 314, 316

external data sources, connecting worksheets to, 29

Extra arguments, 186

F

FACT mathematical and trigonometric function, 213

FACTDOUBLE mathematical and trigonometric function, 213

Fade option button, 14

FALSE logical function, 209

Field captions and filters check box, PivotTable Options dialog box, 371

Field name list box, PivotTable Builder window, 357–362, 365

Field Name text box, 366–367

File category, 70

File origin pop-up menu, 23

FileMaker Pro Database, importing data from, 26–28, 317

FileMaker Pro Import Wizard, 26–29, 317

Fill category, Format dialog box, 247, 249

Fill Colors pop-up menu, 137

Fill down formulas in columns adjacent to data check box, 315

Fill item, Format dialog box, 248

Fill Months item, 35

Fill pane, Format dialog box, 248

Fill panel, Ribbon, 288

Fill tab, Format Cells dialog box, 137, 142

fills, formatting, 136–137

Filter area, sorting and filtering window, 378

Filter button, Ribbon, 322, 324

Filter fields per column pop-up menu, PivotTable Options dialog box, 373

Filter settings check box, Share Workbook dialog box, 399

filtering tables, 322–324

Financial Contribution column, 334

financial functions, 202–209

find command, 42–44

Find dialog box, 43, 322

Find entire cells only check box, 43

Find feature, 322

Find Next button, 43, 312, 322

Find Prev button, 312

FIND text function, 218

Find what box, 43

Find What button, 322

Finder icon, 93

finding workbooks, by properties, 93–95

Finish button, 26, 28

First check box, 272

First Column check box, 309

First Letter tab, 31–32

Fit to N page(s) wide box, Print dialog box, 385

Fix All item, 97

Fix button, 97

Fix drop-down button, 97

FIXED text function, 218

Fixed width option button, 23

Flag errors by using this color pop-up menu, 177

flat-file database, 306

Floor crosses at minimum value check box, Format Axis dialog box, 242

FLOOR mathematical and trigonometric function, 214

FLOOR.PRECISE mathematical and trigonometric function, 214

floors, of charts, 246–249

folders, for opening workbooks, 62

Followed Hyperlink style, 152

Font category, Format dialog box, 250

Font dialog box, 157, 159

Font group, Ribbon, 120, 124, 135–137, 150

Font Size button, Ribbon, 120

Font tab, Format Cells dialog box, 135, 142

fonts, formatting, 135

Fonts pop-up menu, 133

Footer dialog box, 156–157

Footer pop-up menu, 156

Footer preview box, 157

footers, 155–160

directly on worksheet, 158

formatting of, 155–160

using Page Setup dialog box, 155–158

For comments, View preferences pane, 57

For objects, View preferences pane, 57

Foreground color pop-up menu, Format dialog box, 249

Form button, 312

Form dialog box, 312, 317

Form view, 312

Format Axis context menu item, 240

Format Axis dialog box, 240, 243

Format button

New PivotTable Quick Style dialog box, 369

Ribbon, 118

Format Cells dialog box, 125–126, 128–129, 133, 135, 137, 142, 150, 153

Format Cells item, context menu, 125

Format command, context menu, 290

Format context menu item, 249

Format dialog box, 247, 249, 290, 300–301

Format group, Ribbon, 140, 273

Format Picture command, context menu, 301

Format Picture dialog box, 300

Format Picture tab, Ribbon, 259, 291, 293, 295

Format pop-up menu, 86, 96, 114–115, 388

Format Selection button

Chart Elements pop-up menu, 249

Ribbon, 240, 247, 249

Format tab, Ribbon, 240, 288, 298–299

Format Text button, 157, 159

Format Title dialog box, 239

Format Walls dialog box, 247

Format with pop-up menu, 142

formats, for saving workbooks, 63–64

Formats option button, 139, 251

formatting, 122–138, 369–370

alignment of cell content, 133–134

borders and fills, 136–137

conditional formatting, 140–145

applying, 142–143

changing order of, 144–145

clearing, 145

creating custom, 143–144

preset types of, 140–141

fonts, 135

headers and footers, 155–160

directly on worksheet, 158

using Page Setup dialog box, 155–158

of numbers, 125–132

PivotTable styles

applying, 369

choosing options for, 369–370

protecting cells, 137–138

with styles, 150–155

applying, 152

available styles, 151–152

copying from workbook to another, 154

creating custom, 152–153

deleting, 155

table formatting, 150

and themes, 133

three tools for, 123–125

using Paste Special command, 138–140

Formatting toolbar, docking and undocking, 10–11

Formula bar, 16, 34, 171, 173, 193–194, 308

Formula Builder, inserting functions with, 188–192

Formula Builder list, 217

Formula Builder palette, 13

Formula Builder pane, Toolbox, 187

Formula Builder window, 190

formula list, Formula Builder window, 190

Formula tab, Ribbon, 187

formulas, 163–183

complex, 172–174

breaking up into separate steps, 174

nesting parts of, 174

and order of evaluation, 172–174

components of, 167–169

vs. functions, 163–164

making one row or column refer to another, 166–167

preferences for error checking of, 175–178

referring to cells in, 164–165

referring to ranges in, 166

simple, 169–172

troubleshooting, 178–183

displaying all formulas in worksheet, 181

removing circular references, 182–183

seeing details of error, 180

seeing which cells formula uses, 181–182

tracing error to its source, 180

using AutoFill with, 174–175

using named ranges in, 105–106

Formulas and Lists area, 36, 128, 175, 220, 317

Formulas and number formats option button, 139

Formulas option button, 139

Formulas tab, 6, 187

Fraction number format, 127

freezing rows and columns, in workbook windows, 50–51

Full Screen view, 46

Function group, Ribbon, 187

Function pop-up menu, Consolidate dialog box, 404, 406

functions, 185–223

components of, 185–186

database functions, 195–196

date and time functions, 197–198

engineering functions, 199–202

financial functions, 202–209

vs. formulas, 163–164

information functions, 210–211

inserting, 186–194

with AutoSum pop-up menu, 187–188

with Formula Builder, 188–192

with Insert pop-up menu, 192–193

by typing, 193–194

logical functions, 209

lookup and reference functions, 212

mathematical and trigonometric functions, 213–216

nesting of, 194–195

preferences for, 220–223

Calculation preferences pane, 223

controlling iteration of calculations, 222

when to calculate worksheets, 221–222

statistical functions, 217

text functions, 218–220

FV function, 204, 328

FVSCHEDULE financial function, 204

fx button, 187, 189, 193

G

GCD mathematical and trigonometric function, 214

General area, 78–80

General icon, 54, 84, 123

General number format, 126

General option button, 25

General pane, 84, 123

General preferences pane, 54–55, 62, 65, 80, 90, 97, 391

General tab, Properties dialog box, 90–91

Generalized Reduced Gradient (GRG), 347

GESTEP engineering function, 200

Get Data button, 23, 356

GETPIVOTDATA lookup and reference function, 212

Glow and Soft Edges category, Format dialog box, 250

Goal Seek feature, 342–344

Gradient Fill section, Data Bars panel, 263

Gradient tab, Format dialog box, 248

graphical objects

arranging to control which is visible, 301

positioning

overview, 290

relative to cells, 299–301

rotating, 290

GRG (Generalized Reduced Gradient), 347

gridlines

choosing which to display, 245–246

overview, 230

Group button, 111

Group command, 111

Group dialog box, 111

Growth Trend item, AutoFill context menu, 35

H

Header dialog box, 156–157

Header/Footer tab, Page Setup dialog box, 155

Header pop-up menu, 156

Header Row check box, 309

headers, formatting of, 155–160

directly on worksheet, 158

using Page Setup dialog box, 155–158

Height box, Ribbon, 299

height of rows, setting, 120

Help window, 183, 191

HEX2BIN engineering function, 200

HEX2DEC engineering function, 200

HEX2OCT( ) function, 190, 200

Hidden check box, 91, 137

Hidden rows, columns and filter settings check box, 52

Hide check box, Add Scenario dialog box, 336, 338

Hide group titles check box, 80

Hide item, context menu, 122

hiding columns, and rows, 122

High check box, 272

High option button, Format Axis dialog box, 243

Highlight Cells Rules panel, Conditional Formatting panel, 141

Highlight Changes dialog box, 395–396

Highlight changes on screen check box, Highlight Changes dialog box, 396

HLOOKUP lookup and reference function, 212

Home tab, Ribbon, 118, 120, 124, 126, 133, 136–138, 150, 250

Horizontal option button, 49

horizontal split box, 46

Horizontal Title item, 240

HOUR date and time function, 198

HTML file, importing data from, 29

HTML file option button, 22

HTML (HyperText Markup Language), 21

HYPERLINK lookup and reference function, 212

Hyperlink style, 152

HyperText Markup Language (HTML), 21

I

Icon and Text item, Standard toolbar context menu, 10

Icon Only item, Standard toolbar context menu, 10

icon sets, 261–274

overview, 261–267

representing data graphically with, 267–270

Icon Sets panel, 141, 268

icons and text, on toolbars, 10

IF logical function, 209

IFERROR logical function, 209

Ignore All item, 97

Ignore blank check box, 148

Ignore button, 97

Ignore drop-down button, 97

IMABS engineering function, 200

Image and Text item, 72, 421

IMAGINARY engineering function, 200

IMARGUMENT engineering function, 200

IMCONJUGATE engineering function, 200

IMCOS engineering function, 201

IMDIV engineering function, 201

IMEXP engineering function, 201

IMLN engineering function, 201

IMLOG10 engineering function, 201

IMLOG2 engineering function, 201

Import button, 22, 37, 278, 283

Import Cell Styles dialog box, 154

Import dialog box, 22, 278–279, 283

Import HTML table(s) only check box, 315

Import these fields list box, 26

Import Wizard, 23

importing data, 21–29

from comma-separated values file, 23–26

from FileMaker Pro Database, 26–28

from HTML file, 29

IMPOWER engineering function, 201

IMPRODUCT engineering function, 201

IMREAL engineering function, 201

IMSIN engineering function, 201

IMSQRT engineering function, 201

IMSUB engineering function, 201

IMSUM engineering function, 201

In-cell drop-down check box, 147

In compact form, indent row labels pop-up menu, PivotTable Options dialog box, 373

In Menu check box, 94

Include field names check box, 315

Include Row Numbers check box, 315

Increase Font Size button, Ribbon, 124

INDEX lookup and reference function, 212

Indicators only, and comments on hover option button, 57

INDIRECT lookup and reference function, 212

INFO information function, 210

information functions, 210–211

Information style, 150

INitial CAps tab, 31–32

Input Message tab, 147–148

Input message text box, 392

Insert button

Choose a Picture dialog box, 286

Clip Gallery application, 277

Format dialog box, 249

Ribbon, 118

Insert Chart group, Ribbon, 232, 254–255

Insert Chart icon, PowerPoint, 254

Insert dialog box, 119

Insert item, context menu, 118

Insert Page Break option, 383

Insert pop-up menu, inserting functions with, 192–193

Insert Sheet button, 62, 98

Insert Sheet item, context menu, 98

Insert SmartArt Graphic box, 296

Insert Sparklines dialog box, 271

inserting

cells, 119

columns, 118

functions, 186–194

with AutoSum pop-up menu, 187–188

with Formula Builder, 188–192

with Insert pop-up menu, 192–193

by typing, 193–194

rows, 118

Inside option button, Format Axis dialog box, 242–243

Install Solver application, 344

INT mathematical and trigonometric function, 214

internal rate of return (IRR), 208

Interpolated option button, Excel Preferences dialog box, 253

INTRATE financial function, 204

Introduction worksheet, 390

iODBC Data Source Chooser dialog box, 313

iPhoto, Apple. See Apple iPhoto, inserting pictures in workbooks from

IPMT financial function, 205

IRR financial function, 205

IRR (internal rate of return), 208

ISBLANK information function, 210

ISERR information function, 210

ISERROR information function, 210

ISEVEN information function, 210

ISLOGICAL information function, 210

ISNA information function, 210

ISNONTEXT information function, 210

ISNUMBER information function, 211

ISODD information function, 211

ISPMT financial function, 205

ISREF information function, 211

ISTEXT information function, 211

Item labels when no fields are on the table check box, PivotTable Options dialog box, 372

Iteration box, Calculation preferences pane, 222

Iteration options, 220

iterations, controlling for calculations, 222

J

jellybean button, 11

Just see other users' changes option button, Share Workbook dialog box, 399

K

Keep change history for option button, Share Workbook dialog box, 398

Keep current resolution item, Reduce File Size dialog box, 295

Keep Solver solution option button, Solver Results dialog box, 348

Keep Source Formatting option button, 258

keyboard shortcuts

in Excel, 12

preferences, 66–68

running macros using, 418–419

Keywords pane, 42

Keywords tab, Properties dialog box, 281

L

labels

adding to axes from separate range, 244

data, 230, 244–245

Large Preview, 41

Last check box, 272

Last Column check box, 310

Launch Browser dialog box, 282

launching, Excel, 3

Layout area, PivotTable Options dialog box, 372

layout of workbooks, tips for, 100–101

Layout pop-up menu, Ribbon, 367

Layout tab, 5, 46, 372–374, 377, 382

Layouts box, Ribbon, 236

Layouts option button, 26

LCM mathematical and trigonometric function, 214

Left column check box, 105, 405–406

Left section box, 157

LEFT text function, 218

legends, 243–244

LEN text function, 219

Limit iteration check box, 222

Line button, Ribbon, 271–272

Line category, Format dialog box, 250

Line Chart Category, 230

Line check box, PivotTable Builder window, 360

Line column, 352

Line field, 360, 365

Line panel, Ribbon, 288

line sparkline, 270

Link to Content check box, 93

Link to File check box, Choose a Picture dialog box, 285–286

List changes on a new sheet check box, Highlight Changes dialog box, 396

List entries box, 36

List item, 147

List view, 41

LN mathematical and trigonometric function, 214

Location text box

Change PivotTable Data Source dialog box, 376

Create PivotTable dialog box, 356

Lock text check box, Format dialog box, 300

Locked check box, 137, 300

LOG mathematical and trigonometric function, 214

LOG10 mathematical and trigonometric function, 214

Logarithmic scale check box, Format Axis dialog box, 242

logical functions, 209

lookup and reference functions, 212

LOOKUP lookup and reference function, 212

Low check box, 272

Low option button, Format Axis dialog box, 243

LOWER text function, 219

M

Mac file system, Apple. See Apple Mac file system

Macro dialog box, 411–412, 417–419, 423–424

Macro name list box

Assign Macro dialog box, 420

Macro dialog box, 418, 423

Macro name text box, Record Macro dialog box, 412, 415

Macro Options dialog box, 418–419

Macro Recorder, 410–414, 416

macros

editing in Visual Basic Editor tool, 423–428

Editor interface, 424–425

hiding Personal Macro Workbook, 428

opening, 423–424

returning to Microsoft Excel from Editor, 427

sample code, 425–426

saving changes, 427

testing, 427

recording

example, 415–416

overview, 411–414

running, 416–422

deleting macros, 423

from Macro dialog box, 417–418

from objects in worksheets, 422

from toolbar buttons or menu items, 419–422

using keyboard shortcuts, 418–419

Macros button, 411

Macros in pop-up menu

Assign Macro dialog box, 420

Macro dialog box, 417–418, 423

Macros item, Customize Toolbars and Menus dialog box, 419

Mail PDF command, PDF pop-up menu, 387

Major and Minor Gridlines item, 246

Major Gridlines item, 245

Major Tick Mark Type box, Format Axis dialog box, 242

Major unit check box, Format Axis dialog box, 241

Make Unconstrained Variables Non-Negative check box, Solver Parameters dialog box, 347

Manage Rules dialog box, 144–145, 263, 265–267, 269–270

Manually option button, 222

Margins pop-up menu, 383

Margins tab, Page Setup dialog box, 383

Markers dialog box, 272

Markers group, Ribbon, 272

Mask to Shape item, Crop pop-up arrow button, 294

Match case check box, 43

MATCH lookup and reference function, 212

mathematical and trigonometric functions, 213–216

Max function, 404

Max option button

PivotTable Options dialog box, 375

Solver Parameters dialog box, 346

MAX statistical function, 217

Maximum box, 147, 222, 241

Maximum column, 267

Maximum iterations text box, 222

MDETERM mathematical and trigonometric function, 214

MDURATION financial function, 205

Media Browser window, 44, 276, 284–286, 288

Media button, Standard toolbar, 284, 286

MEDIAN statistical function, 217

menu bar

overview, 11

preferences for, 75–77

menus

adding to toolbars, 73

preferences for, 74–75

running macros from menu items, 419–422

Merge & Center pop-up menu, 134

Merge and center cells with labels check box, PivotTable Options dialog box, 373

Merge button, Scenario Manager dialog box, 340

Merge cells check box, Format Cells dialog box, 134

Merge conditional formatting option button, 139

Merge Scenarios dialog box, 340

Merge Workbooks command, 402

Message tab, Data Validation dialog box, 392

Microsoft Excel

creating databases in, 305–326

abilities and limitations of tables, 305–306

connecting tables to external data sources, 312

connecting to databases, 312–316

creating tables and entering data, 306–312

filtering tables, 322–324

identifying and removing duplicate records, 320–322

importing data from FileMaker Pro databases, 317

resizing tables, 317

sorting tables, 318–320

pasting charts from, 257–259

returning from Visual Basic Editor to, 427

Microsoft Office 2011 folder, 3

Microsoft Office Online Files item, Import dialog box, 279

Microsoft Office web site, downloading clip art items from, 282–283

Microsoft PowerPoint presentations, using charts in, 254–259

creating charts in embedded workbooks, 255–257

pasting charts from Excel, 257–259

Microsoft Word

tables from, using paste, 39

using charts in documents, 254–259

creating charts in embedded workbooks, 255–257

pasting charts from Excel, 257–259

MID text function, 219

Midpoint column, 267

Min function, 404

Min option button, Solver Parameters dialog box, 346

MIN statistical function, 217

Minimum box, 147

Minimum check box, Format Axis dialog box, 241

Minimum column, 267

Minor Gridlines item, 245

Minor tick mark type box, Format Axis dialog box, 243

Minor unit check box, Format Axis dialog box, 242

MINUTE date and time function, 198

MINVERSE mathematical and trigonometric function, 214

MIRR financial function, 205

Mixed reference, 167

MMULT mathematical and trigonometric function, 214

MOD mathematical and trigonometric function, 214

MODE statistical function, 217

Modify Cell Style dialog box, 153

Month column, 352

MONTH date and time function, 197

Month field, 365

Monthly Rent column, 333

More Functions item, 187, 189, 309

More help on this function link, 191

Move And Size With Cells option button, Format dialog box, 300

Move But Don't Size With Cells option button, Format dialog box, 300

Move Chart context menu item, 234

Move Chart dialog box, 233–234

Move Down button, 27, 145

Move into Clip Gallery option button, Import dialog box, 280

Move or Copy context menu item, 416

Move or Copy dialog box, 99, 416

move to end item, Move or Copy dialog box, 416

Move Up button, 27, 145

MROUND mathematical and trigonometric function, 214

MULTINOMIAL mathematical and trigonometric function, 214

Multiply option button, 139

My Data Has Headers check box, Sort dialog box, 320

My Templates folder, 115

N

N information function, 211

NA information function, 211

Name box, 73, 76, 92, 421

Name pop-up menu, Formula bar, 308

Name property, Visual Basic Editor application, 425

Name text box

Command Properties dialog box, 421

New PivotTable Quick Style dialog box, 369

Select Data Source dialog box, 237

named ranges, 102–107

assigning names to, 102–104

creating automatically, 104–105

deleting, 106

modifying, 106–107

using in formulas, 105–106

Names in workbook list box, 106

Names In Workbook text box, Define Name dialog box, 333

naming worksheets, 98

navigating worksheets, 17–18

Negative check box, 272

nesting

formulas, 174

functions, 194–195

net present value (NPV), 208

NETWORKDAYS date and time function, 198

New button

Customize Toolbars and Menus dialog box, 419

Ribbon, 307, 312

New Category button, 281, 283

New Category dialog box, 281, 283

New Cell Style dialog box, 153

New Finder Window item, context menu, 93

New Formatting Rule dialog box, 142–145

New Keyword button, Properties dialog box, 282

New Keyword dialog box, 282

New Menu item, 75–76

New PivotTable Quick Style dialog box, 369

New sheet option button, 234, 314

New sheet text box, Move Chart dialog box, 234

New workbook item, Record Macro dialog box, 413

New Worksheet button, 38, 356

Next (>) button, 85

Next to Axis option button, Format Axis dialog box, 243

No button, 149

No Chart Title item, 239

No comments or indicators option button, 57

No Fill item, Format dialog box, 248

No Gridlines item, 245

No option button, 92

NOMINAL financial function, 205

None option button, Format Axis dialog box, 242–243

Normal style, 155

Normal view, 45–46, 383

NOT logical function, 209

Not plotted (leave gaps) option button, Excel Preferences dialog box, 253

Not yet reviewed item, Highlight Changes dialog box, 396

Nothing - hide objects option button, 58

NOW date and time function, 198

NPER financial function, 205

NPV financial function, 205

NPV (net present value), 208

Number Format pop-up menu, 126

Number group, Ribbon, 124, 126, 150

Number number format, 126

Number tab, Format Cells dialog box, 126, 128

numbers, formatting of, 125–132

O

Object positioning box, Format dialog box, 300

objects, displaying, 57–58

OCT2BIN engineering function, 201

OCT2DEC engineering function, 201

OCT2HEX engineering function, 202

ODBC (Open Database Connectivity) drivers, installing, 313

ODD mathematical and trigonometric function, 214

ODDFPRICE financial function, 206

ODDFYIELD financial function, 206

ODDLPRICE financial function, 206

ODDLYIELD financial function, 206

Office web site, Microsoft, 282–283

OFFSET lookup and reference function, 212

OLAP (Online Analytical Processing), 372

On columns check box, PivotTable Options dialog box, 372

On rows check box, PivotTable Options dialog box, 372

Online Analytical Processing (OLAP), 372

Online button, 282

Online Templates category, Excel Workbook Gallery dialog box, 113

Online Templates item, 85

Open at Login item, 80

Open command, 40

Open Database Connectivity (ODBC) drivers, installing, 313

Open dialog box, 40, 62, 81, 115

Open Excel Workbook Gallery when application opens check box, 56, 84

Open method, 426

Open, Microsoft Excel dialog box, 85–86, 114, 416

Open pop-up menu, 86, 114

Open Recent submenu, 389

Operating Costs column, 333

Operation area, Paste Special dialog box, 139

operators, for formulas, 167–169

Optional argument, 186

Options area, 143

Options button

Macro dialog box, 418

PivotTable Field dialog box, 367

Solver Parameters dialog box, 347

Options dialog box, 347

options, for AutoCorrect, 29–33

Options submenu, 80

OR logical function, 209

Or option button, 28

order of evaluation, of formulas, 172–174

Order pop-up menu, 319

Orientation area, 158

Orientation pop-up menu, 134, 383

Other pane, 251

Other pop-up menu, 232

outlines. See also collapsible worksheets

changing settings for, 110

creating automatically, 108–109

creating manually

grouping rows or columns, 111

ungrouping rows or columns, 112

expanding and collapsing, 112

removing, 113

updating after adding or deleting rows or columns, 112–113

Outside option button, Format Axis dialog box, 242–243

Overlap Title at Top item, 239

Overview screen, 56

P

Page Breaks, 382–384

Page Layout, 382–384

Page Layout view, 45–46, 57, 155, 158, 382

Page Layout View button, 382

Page, row and column headings check box, PivotTable Options dialog box, 372

Page Setup dialog box, formatting headers and footers using, 155–158

Page Setup group, Ribbon, 383

panel button, 308

Paper Size pop-up menu, 158

Password dialog box, 89

password protecting, workbooks, 87–90

Password text box, 338, 394–395

Password to modify text box, 88–89

Password to open text box, 88–89

Paste as Picture option button, Word, 258

Paste button, Standard toolbar, 37, 258

Paste command, 37–40

switching data from rows to columns, 38–39

using comma-separated data, 40

using table from word, 39

Paste Link button, Paste Special dialog box, 140

Paste List button, 106

Paste Name dialog box, 106

Paste name list box, 106

Paste Options button, 37, 258

Paste Options pop-up menu, 138, 258

Paste pop-up button, 37, 39, 42

Paste pop-up menu, 138

Paste Special command, 37, 138–140, 391

Paste Special dialog box, 37–38, 138–140, 154, 250–251

Pattern box, Format dialog box, 249

Pattern tab, Format dialog box, 249

Pause button, Goal Seek Status dialog box, 344

PDF button, Print dialog box, 386

PDF pop-up menu, Print dialog box, 386–387

PDF (Portable Document Format) files, sharing worksheets as, 386–387

Percent style, 170

Percentage number format, 127, 328

Personal Macro Workbook

hiding, 428

Macro dialog box, 417

Record Macro dialog box, 413, 415

Unhide dialog box, 423

Photos Browser pane, 284

Photos button, 284

PI mathematical and trigonometric function, 214

Picture of Chart (smaller file size) option button, PowerPoint, 258

Picture or Texture tab, Format dialog box, 249

Picture Quality pop-up menu, Reduce File Size dialog box, 295

Picture Styles group, Ribbon, 292

pictures

adding to Clip Gallery application, 278–282

adjusting, 291–296

applying styles, 292

compressing, 295–296

cropping, 293–294

sharpness, brightness, contrast, and colors, 291–292

inserting in workbooks, 284–286

from Apple iPhoto, 284–285

from Apple Mac file system, 285–286

Pie Chart Category, 230

PivotTable Builder window, 354–357, 366

PivotTable button, Ribbon, 354

PivotTable Field dialog box, 366–367

PivotTable framework, 356

PivotTable option button, 314

PivotTable Options dialog box, 370–372, 374–375, 377

PivotTable reports, 351–379

capabilities of, 351–353

creating, 353–367

automatically, 353–355

changing functions used to summarize fields, 366–367

changing to show different data, 362–366

manually, 355–362

Design group, 367–369

formatting, 369–370

naming and setting options for, 370–376

changing sources of PivotTables, 376

Data tab, 374–375

Display tab, 371–372

Layout tab, 372–374

refreshing data in PivotTables, 375–376

renaming PivotTables, 371

sorting and filtering, 376–379

PivotTable Styles group, Ribbon, 369

PivotTable tab, Ribbon, 354, 367, 369

Placeholders option button, 58

plot area, 229

Plot visible cells only check box, Excel Preferences dialog box, 253

PMT financial function, 206

Portable Document Format (PDF) files, sharing worksheets as, 386–387

positioning graphical objects, 290

POWER mathematical and trigonometric function, 214

PowerPoint presentations, Microsoft. See Microsoft PowerPoint presentations

PPMT financial function, 206

preferences, 53–81

editing options, 59–62

for error checking of formulas, 175–178

for functions, 220–223

Calculation preferences pane, 223

controlling iteration of calculations, 222

when to calculate worksheets, 221–222

keyboard shortcuts, 66–68

for menu bar, 75–77

for menus, 74–75

Preferences dialog box, 53–54

for Ribbon, 77–80

disabling, 77–80

by dragging contents, 79

using Ribbon preferences pane, 79–80

for toolbars, 68–73

adding items to, 70–71

adding menus to, 73

changing button appearance, 71–73

creating new, 70

opening Customize Toolbars and Menus dialog box, 68–69

removing items from, 71

repositioning items on, 71

for windows, 55–59

displaying comments, 57

displaying objects, 57–58

view options for, 57–59

for workbooks, 62–65

default folder for opening, 62

default format for saving, 63–64

displaying Properties dialog box when saving, 65

number of worksheets when creating, 62

opening automatically, 80

saving layout of as workspace, 81

setting AutoRecover for, 64–65

Preferences dialog box, 252–253

Preferred file location box, 62

Preferred view for new sheets pop-up menu, 57

Preserve cell formatting on update check box, PivotTable Options dialog box, 372

Preserve display of dates entered with four-digit years check box, 61

Presets pop-up menu, 384

Press new shortcut key text box, 67

Prevent Changes check box, Add Scenario dialog box, 336, 338

Preview application, 384

Preview button, 7, 384

Previous (<) button, 85

Price column, 352

PRICE financial function, 206

PRICEDISC financial function, 207

PRICEMAT financial function, 207

Print a worksheet option, 384

Print area

PivotTable Options dialog box, 372

setting, 382

Print button

Print dialog box, 385

Standard toolbar, 384

Print dialog box, 384, 386–387

Print expand/collapse triangles check box, PivotTable Options dialog box, 371

Print group, 7

Print multiple worksheets option, 384

Print object check box, Format dialog box, 300

Print Settings check box, 52, 399

Print Setup dialog box, 7

Print What area, Print dialog box, 385

Printer pop-up menu, Print dialog box, 384, 386

problems, solving multivariable with Solver add-in, 345–349

Product check box, PivotTable Builder window, 365

Product column, 352

Product field, 362–365, 377

Product function, 404

PRODUCT mathematical and trigonometric function, 214

Project Explorer window, Visual Basic Editor application, 424

Prompt for file name on refresh check box, 315

Prompt for workbook properties check box, General preferences pane, 65

PROPER text function, 219

Properties button, Returning External Data to Microsoft Excel dialog box, 314

Properties command, context menu, 300

Properties context menu item, 420

Properties dialog box, displaying when saving workbook, 65

properties, for workbooks, 90–93

on Custom tab, 91–93

finding workbooks by, 93–95

on Summary tab, 91

Properties item, Format dialog box, 300

Properties list box, 93

Properties pane, Format dialog box, 300

Properties window, Visual Basic Editor application, 425

Property Address column, 333

Property Profit column, 334

Property Profitability column, 334

Protect structure check box, 393

Protect The Sheet And Contents Of Locked Cells dialog box, 338

Protect the sheet and contents of locked cells dialog box, 394

Protect windows check box, 394

protecting cells, 137–138

Protection area, Add Scenario dialog box, 336

Protection tab, Format Cells dialog box, 137

Provide feedback with animation check box, 61

PV financial function, 207

Q

Quantity column, 352

Query definition box, 314

Quick Layout panel, 236

Quick Look button, 95

Quick Styles box, 292, 298, 369

Quick Styles panel, 369

QUOTIENT mathematical and trigonometric function, 215

R

Radar Chart Category, 231

RADIANS mathematical and trigonometric function, 215

RAND mathematical and trigonometric function, 215

RANDBETWEEN mathematical and trigonometric function, 215

ranges, referring to in formulas, 166

RATE financial function, 207

Read Only button, 89

Read-Only check box, 91

Read-only recommended feature, 88

rearranging worksheets, 99–100

Recalculate Total button, 422

RECEIVED financial function, 207

Recent Files submenu, 81

Record button, Ribbon, 411, 414

Record Macro dialog box, 411–412, 414–416

Recording readout, 414, 416

Reduce File Size dialog box, 295–296

Reference area, 16, 103, 105

Reference box, Consolidate dialog box, 406

Reference Tools palette, 13, 15

Refers to box, 104, 107

Refresh All command, 316

Refresh control box, 315

Refresh Data command, 316

Refresh data on file open check box, 315

Refresh data when opening file check box, PivotTable Options dialog box, 375

Refresh menu, 316

refreshing data, in PivotTables, 375–376

Reject All button, Accept or Reject Changes dialog box, 402

Reject button, Accept or Reject Changes dialog box, 402

relational database, 306

Relative Reference button, 411, 414

Remove All button, 27

Remove Arrows button, 182

Remove button, 27, 145

Remove Cropped Picture Regions check box, Reduce File Size dialog box, 296

Remove Dependent Arrows item, 182

Remove Duplicates button, 320–321

Remove external data from worksheet before saving check box, 315

Remove Keyword button, Properties dialog box, 282

Remove Precedent Arrows item, 182

Repeat row labels on each page check box, PivotTable Options dialog box, 372

Replace All button, 43

Replace box, 32–33

Replace button, 43, 90

replace command, 42–44

Replace dialog box, 43–44

Replace feature, 21

Replace Internet and network paths with hyperlinks check box, 30

Replace text as you type check box, 31

REPLACE text function, 219

Replace with box, 43

Report Filter area

PivotTable Builder window, 358, 362

PivotTable Options dialog box, 373

Report Filter box, PivotTable Builder window, 358–359, 365

Report type box, Scenario Summary dialog box, 341

reports, creating scenarios from, 341

repositioning items, on toolbars, 71

REPT text function, 219

Required argument, 186

Reset All Issues button, 64

Reset button, 79

Reset Ignored Errors button, 176

Reset Ignored Issues button, 64

resizing tables, 317

Resolve Conflicts dialog box, 400–401

Restore Original option button, Solver Results dialog box, 348

Result cells text box, Scenario Summary dialog box, 341

Result readout, Function Builder window, 192

Results box, 96–97

Results.xlsx workbook, 165

Resume button, Goal Seek Status dialog box, 344

Retain items deleted from the data source check box, PivotTable Options dialog box, 375

Retry button, 149

Return Data button, 314

Return to Solver Parameters Dialog check box, Solver Results dialog box, 348

Returning External Data to Microsoft Excel dialog box, 28, 314, 316

Reverse icon order check box, 143, 269

Review tab, 6

Ribbon, 5–9

collapsing, 8–9

controls on, 7–8

preferences for, 77–80

disabling, 77–80

by dragging contents, 79

using Ribbon preferences pane, 79–80

tabs on, 5–6

Ribbon icon, 78, 411

Ribbon Preferences item, context menu, 77, 79

Ribbon preferences pane, 77, 79–80, 411

Right column check box, 105

Right section box, 157

RIGHT text function, 219

ROMAN mathematical and trigonometric function, 215

Rotated Title item, 240

rotating graphical objects, 290

ROUND mathematical and trigonometric function, 215

ROUNDDOWN mathematical and trigonometric function, 215

ROUNDUP mathematical and trigonometric function, 215

Row & Column pop-up menu, 369

Row Area, PivotTable Builder window, 358

Row Headers item, Row & Column pop-up menu, 369

Row headings, 16

Row Height dialog box, 120–121

Row Height item, context menu, 120

Row Input Cell box, Data Table dialog box, 330

Row Labels box, PivotTable Builder window, 358–359, 362–365

Row Level button, 112

ROW lookup and reference function, 212

Row-oriented data table, 329

rows

deleting, 119

in formulas, making one row refer to another, 166–167

freezing of, 50–51

hiding, 122

inserting, 118

setting height of, 120

switching data to columns, with paste command, 38–39

switching with columns, 235

ROWS lookup and reference function, 212

Rows option button, 111–112

Rules box, 177

Run button, Macro dialog box, 418

Run queries in the background check box, PivotTable Options dialog box, 375

S

Sale column, 352

Salesperson check box, PivotTable Builder window, 359, 365

Salesperson column, 352

Salesperson field, 359, 363, 365

Sample box, Format Cells dialog box, 128

Satisfaction field, 325

Save Active Sheet button, 388

Save As box, Save dialog box, 386

Save As command, 114

Save As dialog box, 62, 86–87, 89, 95–96, 114, 251, 388, 415

Save as PDF item, PDF pop-up menu, 386

Save As text box, 96, 251

Save AutoRecover info every N minutes check box, 65

Save AutoRecover information after this number of minutes check box, 65

Save Chart Template dialog box, 251

Save command, 115

Save dialog box, 386

Save external link values check box, 223

Save method, 426

Save my changes and see others' changes option button, 399

Save Options dialog box, 87, 89–90

Save password check box, 314

Save password with file check box, PivotTable Options dialog box, 375

Save preferences pane, 65

Save preview picture with this document check box, 91

Save query definition check box, 314

Save source data with file check box, PivotTable Options dialog box, 375

Save with Document check box, Choose a Picture dialog box, 285–286

Saved Searches folder, 95

saving

templates, 114–115

workbooks, 86, 95–97

Scale category, Format Axis dialog box, 240

Scale pane, Format Axis dialog box, 242

Scaling check box, Print dialog box, 385

Scatter Chart Category, 231

Scenario Manager dialog box, 334

Scenario name text box, Add Scenario dialog box, 335

Scenario PivotTable option button, Scenario Summary dialog box, 341

Scenario pop-up menu, Standard toolbar, 339

Scenario Summary dialog box, 341

Scenario summary option button, Scenario Summary dialog box, 341

Scenario Values dialog box, 336–337

scenarios

creating, 335–337

creating reports from, 341

creating worksheets for, 332–334

editing and deleting, 338

merging into single worksheet, 339–340

protecting, 338

Scenario Manager dialog box, 334

switching among, 339

Scenarios list box, Scenario Manager dialog box, 337–339

Scientific number format, 127

Scrapbook, 40–42

adding item to, 41–42

deleting item from, 42

inserting item from, 42

opening, 40–41

Scrapbook button, 40

Scrapbook palette, 13, 41–42

Scroll buttons, 17

Search bar, 93

Search box, 189, 378

Search field, 43

Search For list, 95

Search mode, 93

Search pop-up menu, 43

Search Results category, Clip Gallery application, 277

SEARCH text function, 219

Search window, 95

SECOND date and time function, 198

Security Options button, Save dialog box, 387

security, protecting workbooks and worksheets, 393–395

Select a data range for the sparklines text box, Insert Sparklines dialog box, 271

Select a search attribute dialog box, 94

Select a Solving Method pop-up menu, Solver Parameters dialog box, 347

Select All button, 17, 21

Select All check box, 321, 378

Select button

Add-Ins dialog box, 345

Consolidate dialog box, 405

Select Changes to Accept or Reject dialog box, 401

Select command, 426

Select Data context menu item, 237

Select Data Source dialog box, 235–238, 244

Select File to Merge into Current Workbook dialog box, 402

Select where to place sparklines text box, Insert Sparklines dialog box, 271

Selected chart area, Excel Preferences dialog box, 253

selecting cells, 19–21

Selection Basket link, 282

Selection of cells option, 384

Selection option button, Print dialog box, 385

Send Backward command, Arrange submenu, 301

Send to Back command, Arrange submenu, 301

Series list box, Select Data Source dialog box, 237

SERIESSUM mathematical and trigonometric function, 215

Set cell text box, Goal Seek dialog box, 343

Set Objective box, Solver Parameters dialog box, 346

Set precision as displayed check box, 223

Set Print Area command, 382

Set Ribbon Preferences button, 77, 79

Settings area, 57

Settings dialog box, 110

Settings tab, Data Validation dialog box, 146

Setup button, 7

Shadow category, Format dialog box, 250

Shape Browser, 286

Shape Styles box, 288, 299

Shape Styles group, Ribbon, 288, 290

shapes, adding to worksheets and formatting, 286–288

Shapes Browser pane, Media Browser window, 287

Shapes button, Media Browser window, 287

Share Workbook dialog box, 397, 399

Shared, Spreads folder, 165

Sharing and Privacy area, 411

Sharing and Privacy category, 78

sharpness, adjusting, 291–292

Sheet list box, Merge Scenarios dialog box, 340

Sheets in new workbook box, General preferences pane, 97

Sheets("Sheet1").Copy statement, 426

Sheets("Sheet1").Select statement, 426

Shift cells down option button, 119

Shift cells left option button, 119

Shift cells right option button, 119

Shift cells up option button, 120

Shortcut key box

Macro Options dialog box, 418

Record Macro dialog box, 413, 415

Show All button, 54, 56

Show All icon, 54

Show area, PivotTable Options dialog box, 371

Show button, 52, 339

Show chart names on hover check box, Excel Preferences dialog box, 253

Show check box, 69

Show column, 69, 419

Show data bar only check box, 265

Show empty cells as pop-up menu, Select Data Source dialog box, 236

Show error alert after invalid data is entered check box, 392

Show for Columns item, Ribbon, 367

Show formatting rules for pop-up menu, 144

Show formula bar by default check box, 57

Show formulas check box, 58

Show Formulas menu item, 181

Show function ScreenTips check box, 59

Show gridlines check box, 58

Show horizontal scroll bar check box, 59

Show icon only check box, 269

Show outline symbols check box, 58

Show page breaks check box, 58

Show Panels list box, 15

Show Paste Options smart buttons check box, 62

Show pop-up menu, 181

Show row and column headings check box, 58

Show sheet tabs check box, 59

Show status bar check box, 57

Show vertical scroll bar check box, 59

Show zero values check box, 58

Shrink to fit check box, Format Cells dialog box, 134

SIGN mathematical and trigonometric function, 215

Simplex method, 347

SIN mathematical and trigonometric function, 215

Since date item, Highlight Changes dialog box, 396

Since I last saved item, Highlight Changes dialog box, 396

SINH mathematical and trigonometric function, 215

Size group, Ribbon, 299

Size pop-up menu, 56, 123, 383

Skip blanks check box, 140

SLN financial function, 207

Smart Folder, 95

SmartArt diagrams, 296–298

SmartArt Graphic Styles group, Ribbon, 298

SmartArt tab, Ribbon, 296, 298

Solid Fill section, Data Bars panel, 263

Solid tab, Format dialog box, 248

Solve button, Solver Parameters dialog box, 348

Solver add-in, 344–349

downloading and installing, 344–345

solving multiple-variable problems with, 345–349

Solver Parameters dialog box, 345–346, 348

Solver Results dialog box, 348

Solver.pkg file, 344

Solver.Xlam item

Add-ins available list box, 345

Choose an Add-In dialog box, 345

Sort & Filter group, Ribbon, 322, 324

Sort A to Z option button, PivotTable Options dialog box, 374

Sort area

AutoFilter window, 323

PivotTable Options dialog box, 374, 377

Sort button, 318

Sort by data source order option button, PivotTable Options dialog box, 374

Sort by row, 319

Sort dialog box, 318–320

Sort left to right option button, 320

Sort On pop-up menu, 319

Sort Options dialog box, 320

Sort pop-up menu, 318

sorting and filtering window, 378

sorting tables, 318–320

by multiple fields, 318–320

by a single field, 318

Source box, 147

source data, changing, 235–236

Source pop-up menu, 93

Spaces feature, 15

Sparkline panel, 273

sparklines, 261–274

overview, 261–269

showing data trends with, 270–274

formatting sparklines, 271–274

inserting sparklines, 271

Sparklines group, Ribbon, 271

Sparklines tab, Ribbon, 271–273

Special number format, 127

Split boxes, 17

Split button, 46

splitting workbook windows, 46–47

SQRT mathematical and trigonometric function, 215

SQRTPI mathematical and trigonometric function, 215

Stack and scale with N Units/Picture option button, Format dialog box, 249

Stack option button, Format dialog box, 249

Staff column, 325

Standard font menu, 123

Standard font pop-up menu, 56

Standard toolbar, 4, 10, 43, 50, 74, 114, 126, 187, 192, 425

Start date box, 147

Start import at row box, 23

Start time box, 147

startup folder, Excel, 81

statistical functions, 217

Statistical section, Formula Builder list, 217

Statistics tab, 90–91

StdDev function, 404

StdDevp function, 405

Step button, Goal Seek Status dialog box, 344

Stock Chart Category, 231

Stop if true column, 145

Store macro in pop-up menu, Record Macro dialog box, 413, 415

Stretch option button, Format dialog box, 249

Style name box, 153

Style pop-up menu, 142–143, 149–150, 248, 265, 267

styles, 150–155

applying

to charts, 238

overview, 152

to pictures, 292

available styles, 151–152

copying from workbook to another, 154

creating custom, 152–153

deleting, 155

Styles box, 238, 273

Styles panel, 152–153, 238, 273

Sub keyword, 426

Subject to the Constraints box, Solver Parameters dialog box, 346

Subject to the Constraints list box, Solver Parameters dialog box, 347

SUBSTITUTE text function, 219

SUBTOTAL mathematical and trigonometric function, 215

Subtotals pop-up menu, Ribbon, 367

Subtract option button, 139

SUM( ) function, 185–186, 366

SUM mathematical and trigonometric function, 216

SUMIF mathematical and trigonometric function, 216

SUMIFS mathematical and trigonometric function, 216

Summarize by list box, 366

Summary button, Scenario Manager dialog box, 341

Summary columns to right of detail check box, 110

Summary rows below detail check box, 110

Summary tab

Properties dialog box, 90

properties for workbooks on, 91

Summary worksheet, 390

SUMPRODUCT mathematical and trigonometric function, 216

SUMSQ mathematical and trigonometric function, 216

SUMX2MY2 mathematical and trigonometric function, 216

SUMX2PY2 mathematical and trigonometric function, 216

SUMXMY2 mathematical and trigonometric function, 216

Surface Chart Category, 231

Switch Reference button, 167

Switch Row/Column button, Select Data Source dialog box, 236

SYD financial function, 207

Symbol Browser pane, Media Browser, 44

Symbol dialog box, 44

symbols, data input of, 44–45

T

T text function, 219

Tab Color item, 98

tab color, of worksheets, 98

Tab Color window, 98

Tab Order button, 77, 79

Tab-separated values (TSV), 21

Table AutoExpansion feature, 317, 325

Table element list box, New PivotTable Quick Style dialog box, 369

table formatting, 150

Table Options group, Ribbon, 309

Table Styles box, Ribbon, 308

Table Styles panel, 308

tables

creating databases using, 305–326

in Microsoft Excel, 305–324

using functions with tables, 324–326

from Word, using paste, 39

Tables icon, Formulas and Lists area, 317

Tables option button, 26

Tables preferences pane, 317

Tables tab, 5, 308–309

tabs, on Ribbon, 5–6

TAN mathematical and trigonometric function, 216

TANH mathematical and trigonometric function, 216

TBILLEQ financial function, 207

TBILLPRICE financial function, 208

TBILLYIELD financial function, 208

templates, 113–115

creating based on existing workbook, 114

creating new workbooks from, 85

saving, 114–115

Templates area, 251

Text File option button, 22

text functions, 218–220

Text Import Wizard, 23–26, 40

Text length item, 147

Text number format, 127

Text Only (Always) item, 72, 421

Text Only (in Menus) item, 72, 421

Text option button, 25

Text Pane, 297

Text Styles group, Ribbon, 299

TEXT text function, 219

Texture pop-up palette, Format dialog box, 249

The changes being saved win option button, Share Workbook dialog box, 399

The selected style has formatting for the following properties area, 153

The Selection Is Not Valid dialog box, 105

Themed Cell Styles category, 152

themes, 133

Themes group, Ribbon, 133

Themes panel, 133

Then By line, 320

This Selection Is Not Valid dialog box, 39

This workbook item, Record Macro dialog box, 413

Thousands separator pop-up menu, 26

Ticks pane, Format Axis dialog box, 242

Tiled option button, 49

TIME date and time function, 197

Time item, 147

Time number format, 127

TIMEVALUE date and time function, 197

Title Above Chart item, 239

Title property, 94

Title text box, 392

titles

axes, 229–230, 239–240

charts, 229–230, 239

Titles and Headings category, 152

To area, Solver Parameters dialog box, 346

To book pop-up menu, 100, 416

To Value text box, Goal Seek dialog box, 343

TODAY date and time function, 198

TODAY( ) function, 185, 190

toolbar buttons, running macros from, 419–422

toolbars, 9–11

changing appearance of, on toolbars, 73

choosing which to display, 9

displaying icons and text on, 10

Formatting toolbar, docking and undocking, 10–11

preferences for, 68–73

adding items to, 70–71

adding menus to, 73

changing button appearance, 71–73

creating new, 70

opening Customize Toolbars and Menus dialog box, 68–69

removing items from, 71

repositioning items on, 71

Toolbars and Menus tab, 68, 70, 74, 77, 419

Toolbars submenu, View menu, 9

Toolbox, 13–15

Toolbox button, 13, 40, 192

Toolbox Settings button, 14

Toolbox Settings palette, 14–15

Tools item, Customize Toolbars and Menus dialog box, 339

Top/Bottom Rules panel, Conditional Formatting panel, 141

Top row check box, 105, 405–406

Total Price column, 352

Total Price field, 361

Total Row check box, 309

Totals pop-up menu, Ribbon, 367

Trace Dependents command, 182

Trace Precedents button, 181

Trace Precedents command, 182

Track Changes area, Share Workbook dialog box, 398

Track Changes feature, 395, 402

Track changes while editing check box, Highlight Changes dialog box, 395

tracking changes, in shared worksheets

overview, 395–397

reviewing, 401–402

Transparency slider, Format dialog box, 248

Transpose check box, 140

Transpose command, 38–39

TRANSPOSE lookup and reference function, 212

Treat consecutive delimiters as one check box, 24

Treat picture layers as separate objects check box, Choose a Picture dialog box, 286

TRIM text function, 219

troubleshooting, 178–183

displaying all formulas in worksheet, 181

formulas, 178–183

removing circular references, 182–183

seeing details of error, 180

seeing which cells formula uses, 181–182

tracing error to its source, 180

TRUE logical function, 209

TRUNC mathematical and trigonometric function, 216

TSV (Tab-separated values), 21

Turn on the ribbon check box, 78

TYPE information function, 211

Type pop-up menu, 92, 267

Type text box, 129

U

undocking Formatting toolbar, 10–11

Ungroup button, 112

Ungroup dialog box, 111–112

Unhide dialog box, 49, 423

Unhide item, context menu, 122

Unhide workbook list box, Unhide dialog box, 423

Unicode Text item, 38

untitled dialog box, 112

Update changes area, Share Workbook dialog box, 399

UPPER text function, 220

Use a table or a range in this workbook option button, 355

Use an external data source option button, 356

Use custom lists when sorting check box, PivotTable Options dialog box, 374

Use Destination Theme option button, 258

Use labels in area, Consolidate dialog box, 405–406

Use R1C1 reference style option, 55

Use Table check box, 315

Use the 1904 date system check box, 128, 223

user interface, elements of, 15–17

User name box, 56

V

Validation option button, 139

Value axis scale area, Format Axis dialog box, 241

Value Of option button, Solver Parameters dialog box, 346

Value text box, 92

VALUE text function, 220

Values and number formats option button, 139

Values Area, PivotTable Builder window, 358

Values box, PivotTable Builder window, 358, 361

Values in reverse order check box, Format Axis dialog box, 242

Values option button, 139

Var functnon, 405

Varp function, 405

VBA (Visual Basic for Applications), 80, 409

VDB financial function, 208

Verify text box, 338, 394–395

Vertical option button, 49

vertical split box, 46

Vertical Title item, 240

View buttons, 17

View icon, 56

View menu, 9

view options, for windows, 57–59

View pop-up menu, 41, 72, 421

View preferences pane, 56–58

Visual Basic Editor tool, editing macros in, 423–428

Editor interface, 424–425

hiding Personal Macro Workbook, 428

opening, 423–424

returning to Microsoft Excel from Editor, 427

sample code, 425–426

saving changes, 427

testing, 427

Visual Basic for Applications (VBA), 80, 409

Visual Basic group, Ribbon, 411

VLOOKUP lookup and reference function, 212

W

walls, of charts, 246–249

Warning style, 150

WEEKDAY date and time function, 198

WEEKDAY( ) function, 190

WEEKNUM date and time function, 198

what-if analysis, 327–349

data tables, 328–331

with one variable, 328–331

with two variables, 331

Goal Seek feature, 342–344

Solver add-in, 344–349

downloading and installing, 344–345

solving multiple-variable problems with, 345–349

worksheets, 332–341

When check box

Highlight Changes dialog box, 395

Select Changes to Accept or Reject dialog box, 401

When file is saved option button, Share Workbook dialog box, 399

When inactive for check box, 14

Where check box

Highlight Changes dialog box, 396

Select Changes to Accept or Reject dialog box, 401

Where pop-up menu, Save dialog box, 386

Which changes area, Select Changes to Accept or Reject dialog box, 401

Who check box

Highlight Changes dialog box, 396

Select Changes to Accept or Reject dialog box, 401

Who has this workbook open now list box, Share Workbook dialog box, 398

Whole number item, 147

Width box, Ribbon, 299

width of columns, setting, 121–122

Win/Loss button, Ribbon, 271–272

win/loss sparkline, 270, 273

Window group, 46

Window options area, View preferences pane, 58

windows

preferences for, 55–59

displaying comments, 57

displaying objects, 57–58

view options for, 57–59

for workbooks

arranging of, 48–49

freezing rows and columns, 50–51

showing parts of workbook in, 48

splitting of, 46–47

Windows of active workbook check box, 49

With box, 33

Within pop-up menu, 43

Word documents, Microsoft. See Microsoft Word

WordArt feature, 298–299

Workbook format, Excel, 63–64, 95–96

Workbook Gallery dialog box, Excel, 3, 56, 81, 83–85, 113, 307

Workbook icon, Excel, 3

Workbook options box

Calculation preferences pane, 223

Excel Preferences dialog box, 128

workbooks, 45–51. See also worksheets

collapsible worksheets in, 107–113

changing settings for outlining, 110

creating outline automatically, 108–109

creating outline manually, 111–112

expanding and collapsing outline, 112

removing outline, 113

updating outline after adding or deleting rows or columns, 112–113

creating charts in embedded, 255–257

creating new, 83–95

based on existing workbook, 85–86

blank, 84

from template, 85

documenting, 389–392

comments, 390–392

data validation formatting, 392

explanatory text, 390

entering data on multiple worksheets at once, 101–102

inserting pictures in, 284–286

from Apple iPhoto, 284–285

from Apple Mac file system, 285–286

layout of, tips for, 100–101

merging, 402

named ranges in, 102–107

assigning names to, 102–104

creating automatically, 104–105

deleting, 106

modifying, 106–107

using in formulas, 105–106

password protecting, 87–90

preferences for, 62–65

default folder for opening, 62

default format for saving, 63–64

displaying Properties dialog box when saving, 65

number of worksheets when creating, 62

opening automatically, 80

saving layout of as workspace, 81

setting AutoRecover for, 64–65

printing, 384–385

properties for, 90–93

on Custom tab, 91–93

finding workbooks by, 93–95

on Summary tab, 91

saving

overview, 86

for use with older versions of Excel, 95–97

sharing, 393–402

allowing editing, 397–399

protecting workbooks and worksheets, 393–395

resolving conflicts, 400–401

tracking changes, 395–397

templates for, 113–115

creating based on existing workbook, 114

saving, 114–115

windows for

arranging of, 48–49

freezing rows and columns, 50–51

showing parts of workbook in, 48

splitting of, 46–47

zooming of, 49

Workbooks.Open Filename:= statement, 426

Workbooks.Open statement, 426

WORKDAY date and time function, 198

Worksheet Menu Bar toolbar, 74–76

worksheet tab bar, 18

Worksheet tabs, 17

worksheets, 97–100, 332–341. See also workbooks

changing tab color of, 98

connecting to external data sources, 29

consolidating, 403–407

by category, 406–407

by position, 403–405

preparing, 403

deleting, 99

illustrating, 275–301

adding and formatting shapes, 286–288

adding decorative text with WordArt feature, 298–299

adjusting pictures, 291–296

clip art, 276–283

graphical objects, 290, 299–301

inserting pictures in workbooks, 284–286

SmartArt diagrams, 296–298

inserting new, 98

naming, 98

navigating among, 17–18

preferences for, number of when creating workbook, 62

printing, 381–385

checking page layout and page breaks, 382–384

setting print area, 382

sharing worksheets as PDFs, 386–387

worksheets or workbooks, 384–385

protecting, 394–395

rearranging, 99–100

running macros from objects in, 422

scenarios

creating, 335–337

creating reports from, 341

creating worksheets for, 332–334

editing and deleting, 338

merging into single worksheet, 339–340

protecting, 338

Scenario Manager dialog box, 334

switching among, 339

workspaces, saving layout of workbooks as, 81

X

XIRR financial function, 208

.xltm (Excel Macro-Enabled Template) option, 115

.xltx (Excel Template) option, 114

XNPV financial function, 208

Y

Year check box, PivotTable Builder window, 359

Year column, 352

YEAR date and time function, 197

Year field, 359, 365

Year pop-up menu, PivotTable Builder window, 362

YEARFRAC date and time function, 197

Yes button, 92, 149

YIELD financial function, 208

YIELDDISC financial function, 208

YIELDMAT financial function, 208

Z

Zero option button, Excel Preferences dialog box, 253

Zoom box, 50

Zoom dialog box, 50

Zoom pop-up menu, 50