contents
Part I: Getting Started with Excel 1
Chapter 1: Introducing Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What Is Excel Good For? ..............................................................................................................3
Understanding Workbooks and Worksheets..................................................................................4
Moving Around a Worksheet ........................................................................................................6
Navigating with your keyboard ..........................................................................................6
Navigating with your mouse ..............................................................................................7
Introducing the Ribbon ................................................................................................................7
Ribbon tabs ........................................................................................................................8
Contextual tabs ..................................................................................................................9
Types of commands on the Ribbon....................................................................................10
Accessing the Ribbon using your keyboard ......................................................................11
Using the shortcut menus............................................................................................................13
Customizing your Quick Access Toolbar ....................................................................................14
Working with Dialog Boxes ........................................................................................................15
Navigating dialog boxes ....................................................................................................16
Using tabbed dialog boxes ................................................................................................16
Using the Taskbar........................................................................................................................17
Creating Your First Excel Worksheet ..........................................................................................18
Getting started on your worksheet ....................................................................................18
Filling in the month names ..............................................................................................19
Entering the sales data ......................................................................................................19
Formatting the numbers ..................................................................................................20
Making your worksheet look a bit fancier ........................................................................20
Summing the values..........................................................................................................21
Creating a chart ................................................................................................................21
Printing your worksheet....................................................................................................22
Saving your workbook ......................................................................................................23
Chapter 2: What’s New in Excel 2007? . . . . . . . . . . . . . . . . . . . . . 25
A New User Interface ..................................................................................................................25
Larger Worksheets ......................................................................................................................26
New File Formats ........................................................................................................................27
Worksheet Tables ........................................................................................................................27
Styles and Themes ......................................................................................................................28
Better Looking Charts..................................................................................................................28
Page Layout View ........................................................................................................................29
Enhanced Conditional Formatting ..............................................................................................30
Consolidated Options..................................................................................................................31
SmartArt.....................................................................................................................................31
Formula AutoComplete ..............................................................................................................32
Collaboration Features ................................................................................................................33
Compatibility Checker ................................................................................................................33
Improved Pivot Tables ................................................................................................................33
New Worksheet Functions ..........................................................................................................34
Other New Features ....................................................................................................................34
Chapter 3: Entering and Editing Worksheet Data . . . . . . . . . . . . . . . 35
Exploring the Types of Data You Can Use ....................................................................................35
About numerical values ....................................................................................................35
About text entries..............................................................................................................36
About formulas ................................................................................................................36
Entering Text and Values into Your Worksheets ..........................................................................37
Entering Dates and Times into Your Worksheets ........................................................................38
Entering date values..........................................................................................................39
Entering time values ........................................................................................................39
Modifying Cell Contents..............................................................................................................39
Erasing the contents of a cell ............................................................................................39
Replacing the contents of a cell ........................................................................................40
Editing the contents of a cell ............................................................................................40
Learning some handy data-entry techniques ....................................................................41
Automatically moving the cell pointer after entering data ......................................41
Using arrow keys instead of pressing Enter ............................................................42
Selecting a range of input cells before entering data ................................................42
Using Ctrl+Enter to place information into multiple cells simultaneously ..............42
Entering decimal points automatically ....................................................................42
Using AutoFill to enter a series of values ................................................................43
Using AutoComplete to automate data entry ..........................................................43
Forcing text to appear on a new line within a cell ..................................................44
Using AutoCorrect for shorthand data entry ..........................................................44
Entering numbers with fractions ............................................................................45
Simplifying data entry by using a form ..................................................................45
Entering the current date or time into a cell............................................................46
Applying Number Formatting ....................................................................................................47
Improving readability by formatting numbers ..................................................................47
Using automatic number formatting ......................................................................47
Formatting numbers by using the Ribbon ..............................................................48
Using shortcut keys to format numbers ..................................................................48
Formatting numbers using the Format Cells dialog box..........................................49
Adding your own custom number formats........................................................................51
Chapter 4: Essential Worksheet Operations . . . . . . . . . . . . . . . . . . 53
Learning the Fundamentals of Excel Worksheets ........................................................................53
Working with Excel’s windows..........................................................................................53
Moving and resizing windows ................................................................................55
Switching among windows ....................................................................................55
Closing windows ....................................................................................................56
Activating a worksheet ......................................................................................................56
Adding a new worksheet to your workbook......................................................................57
Deleting a worksheet you no longer need..........................................................................57
Changing the name of a worksheet ..................................................................................58
Changing a sheet tab’s color ..............................................................................................58
Rearranging your worksheets ............................................................................................58
Hiding and unhiding a worksheet ....................................................................................59
Controlling the Worksheet View..................................................................................................61
Zooming in or out for a better view ..................................................................................61
Viewing a worksheet in multiple windows ........................................................................62
Comparing sheets side by side ..........................................................................................63
Splitting the worksheet window into panes ......................................................................63
Keeping the titles in view by freezing panes ......................................................................64
Monitoring cells with a Watch Window ............................................................................65
Working with Rows and Columns ..............................................................................................66
Inserting rows and columns ..............................................................................................66
Deleting rows and columns ..............................................................................................67
Hiding rows and columns ................................................................................................67
Changing column widths and row heights ........................................................................68
Changing column widths........................................................................................68
Changing row heights ............................................................................................69
Chapter 5: Working with Cells and Ranges. . . . . . . . . . . . . . . . . . . 71
Understanding Cells and Ranges ................................................................................................71
Selecting ranges ................................................................................................................72
Selecting complete rows and columns ..............................................................................73
Selecting noncontiguous ranges ........................................................................................73
Selecting multisheet ranges ..............................................................................................74
Selecting special types of cells ..........................................................................................76
Selecting cells by searching ..............................................................................................77
Copying or Moving Ranges ........................................................................................................78
Copying by using Ribbon commands................................................................................79
Copying by using shortcut menu commands ....................................................................79
Copying by using shortcut keys ........................................................................................80
Copying or moving by using drag-and-drop ....................................................................80
Copying to adjacent cells ..................................................................................................81
Copying a range to other sheets ........................................................................................82
Using the Office Clipboard to paste ..................................................................................82
Pasting in special ways ......................................................................................................84
Using the Paste Special Dialog box....................................................................................85
Performing mathematical operations without formulas ..........................................86
Skipping blanks when pasting ................................................................................86
Transposing a range ................................................................................................86
Using Names to Work with Ranges..............................................................................................87
Creating range names in your workbooks ........................................................................87
Using the New Name dialog box ............................................................................87
Using the Name box ..............................................................................................88
Using the Create Names From Selection dialog box ................................................88
Managing Names ..............................................................................................................89
Adding Comments to Cells..........................................................................................................90
Formatting comments ......................................................................................................91
Changing a comment’s shape ............................................................................................92
Reading comments............................................................................................................93
Hiding and showing comments ........................................................................................93
Editing comments ............................................................................................................93
Deleting comments ..........................................................................................................93
Chapter 6: Introducing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 95
What Is a Table? ..........................................................................................................................95
Creating a Table ..........................................................................................................................97
Changing the Look of a Table ......................................................................................................98
Working with Tables ................................................................................................................100
Navigating in a table ......................................................................................................100
Selecting parts of a table..................................................................................................100
Adding new rows or columns ........................................................................................101
Deleting rows or columns ..............................................................................................101
Moving a table ................................................................................................................101
Setting table options ......................................................................................................102
Working with the Total Row............................................................................................102
Removing duplicate rows from a table ............................................................................103
Sorting and filtering a table ............................................................................................104
Sorting a table ......................................................................................................105
Filtering a table ....................................................................................................106
Converting a table back to a range ..................................................................................107
Chapter 7: Worksheet Formatting . . . . . . . . . . . . . . . . . . . . . . . 109
Getting to Know the Formatting Tools ......................................................................................109
Using the Formatting Tools in the Home Tab ..................................................................110
Using the Mini toolbar ....................................................................................................110
Using the Format Cells dialog box ..................................................................................112
Using Formatting in Your Worksheets ......................................................................................112
Using different fonts........................................................................................................112
Changing text alignment ................................................................................................115
Choosing horizontal alignment options ................................................................116
Choosing vertical alignment options ....................................................................116
Wrapping or shrinking text to fit the cell ..............................................................116
Merging worksheet cells to create additional text space ........................................117
Displaying text at an angle ....................................................................................117
Controlling the text direction................................................................................118
Using colors and shading ................................................................................................118
Adding borders and lines ................................................................................................119
Adding a background image to a worksheet....................................................................121
Using Named Styles for Easier Formatting ................................................................................122
Applying styles................................................................................................................123
Modifying an existing style..............................................................................................124
Creating new styles ........................................................................................................124
Merging styles from other workbooks ............................................................................125
Controlling styles with templates ....................................................................................125
Understanding Document Themes ............................................................................................126
Applying a theme............................................................................................................127
Customizing a theme ......................................................................................................128
Chapter 8: Understanding Excel’s Files . . . . . . . . . . . . . . . . . . . . 131
Excel File Operations ................................................................................................................131
Creating a new workbook ..............................................................................................131
Opening an existing workbook ......................................................................................132
Selecting a different location ................................................................................134
Using the My Places bar........................................................................................134
Filtering filenames ................................................................................................134
Choosing your file display preferences..................................................................135
Saving a Workbook ........................................................................................................136
Using AutoRecover ........................................................................................................136
Specifying a password ....................................................................................................137
Other workbook options ................................................................................................138
Setting workbook properties ................................................................................139
Inspecting a document ........................................................................................140
Encrypting a document ........................................................................................140
Restricting permissions ........................................................................................140
Adding a digital signature ....................................................................................140
Marking a document final ....................................................................................140
Checking compatibility ........................................................................................140
Closing workbooks ........................................................................................................141
Safeguarding your work ..................................................................................................141
Excel File Compatibility ............................................................................................................142
Recognizing the Excel 2007 file formats..........................................................................142
Saving a file for use with an older version of Excel ..........................................................143
Chapter 9: Using and Creating Templates. . . . . . . . . . . . . . . . . . . 145
Exploring Excel Templates ........................................................................................................145
Viewing templates ..........................................................................................................145
Creating a workbook from a template ............................................................................146
Modifying a template ......................................................................................................148
Understanding Custom Excel Templates ..................................................................................149
Working with the default templates ................................................................................150
Using the workbook template to change workbook defaults ................................150
Using the worksheet template to change worksheet defaults ................................151
Editing your templates..........................................................................................151
Resetting the default workbook and worksheet settings ........................................151
Creating custom templates ..............................................................................................151
Saving your custom templates ..............................................................................152
Ideas for creating templates ..................................................................................153
Chapter 10: Printing Your Work . . . . . . . . . . . . . . . . . . . . . . . . 155
Printing with One Click ............................................................................................................155
Changing Your Page View..........................................................................................................157
Normal View ..................................................................................................................157
Page Layout View............................................................................................................158
Page Break Preview ........................................................................................................159
Adjusting Common Page Setup Settings ....................................................................................160
Adjusting the page margins ............................................................................................160
Changing the page orientation ........................................................................................162
Specifying the paper size ................................................................................................162
Specifying the print area ................................................................................................162
Understanding page breaks ............................................................................................162
Inserting a page break ..........................................................................................162
Removing page breaks you’ve added ....................................................................163
Using a background image..............................................................................................163
Printing row and column titles........................................................................................164
Scaling printed output ....................................................................................................165
Printing cell gridlines ......................................................................................................165
Printing row and column headers ..................................................................................166
Adding a Header or Footer to Your Reports ..............................................................................166
Selecting a predefined header or footer ..........................................................................166
Understanding header and footer element codes ............................................................167
Other header and footer options ....................................................................................168
Adjusting the Settings in the Print Dialog Box ..........................................................................168
Choosing your printer ....................................................................................................169
Specifying what you want to print ..................................................................................169
Printing multiple copies of a report ................................................................................169
Preventing Certain Cells from Being Printed..............................................................................170
Preventing Objects from being Printed ......................................................................................170
Creating Custom Views of Your Worksheet ..............................................................................171
Part II: Working with Formulas and Functions 173
Chapter 11: Introducing Formulas and Functions . . . . . . . . . . . . . . 175
Understanding Formula Basics ..................................................................................................175
Using operators in formulas ............................................................................................176
Understanding operator precedence in formulas ............................................................177
Using functions in your formulas ....................................................................................179
Examples of formulas that use functions ..............................................................179
Function arguments..............................................................................................180
More about functions............................................................................................181
Entering Formulas into Your Worksheets ..................................................................................181
Entering formulas manually ............................................................................................183
Entering formulas by pointing ........................................................................................183
Pasting range names into formulas ..................................................................................183
Inserting functions into formulas ....................................................................................184
Function entry tips ........................................................................................................186
Editing Formulas ......................................................................................................................187
Using Cell References in Formulas ............................................................................................187
Using relative, absolute, and mixed references ................................................................187
Changing the types of your references ............................................................................190
Referencing cells outside the worksheet ..........................................................................190
Referencing cells in other worksheets ..................................................................190
Referencing cells in other workbooks ..................................................................190
Using Formulas In Tables ..........................................................................................................191
Summarizing data in a table ............................................................................................191
Using formulas within a table..........................................................................................193
Referencing data in a table ..............................................................................................194
Correcting Common Formula Errors ........................................................................................195
Handling circular references............................................................................................196
Specifying when formulas are calculated ........................................................................198
Using Advanced Naming Techniques ........................................................................................199
Using names for constants ..............................................................................................199
Using names for formulas ..............................................................................................199
Using range intersections ................................................................................................200
Applying names to existing references ............................................................................202
Tips for Working with Formulas................................................................................................203
Don’t hard-code values....................................................................................................203
Using the Formula bar as a calculator ............................................................................203
Making an exact copy of a formula..................................................................................204
Converting formulas to values ........................................................................................204
Chapter 12: Creating Formulas That Manipulate Text . . . . . . . . . . . . 205
A Few Words About Text ..........................................................................................................205
How many characters in a cell? ......................................................................................205
Numbers as text ..............................................................................................................206
Text Functions ..........................................................................................................................206
Determining whether a cell contains text ........................................................................207
Working with character codes ........................................................................................207
The CODE function..............................................................................................208
.................385
Modifying the Chart Area ..........................................................................................................386
Modifying the Plot Area ............................................................................................................387
Working with Chart Titles ........................................................................................................388
Working with the Legend ..........................................................................................................389
Working with Gridlines ............................................................................................................391
Modifying the Axes ..................................................................................................................391
Value axis options ................................................................................................391
Category axis options............................................................................................396
Working with Data Series ..........................................................................................................398
Deleting a data series ......................................................................................................398
Adding a new data series to a chart ................................................................................398
Changing data used by a series........................................................................................399
Changing the data range by dragging the range outline ........................................399
Using the Edit Series dialog box............................................................................400
Editing the Series formula ....................................................................................400
Displaying data labels in a chart ......................................................................................401
Handling missing data ....................................................................................................402
Adding error bars............................................................................................................403
Adding a trendline ..........................................................................................................404
Modifying 3-D charts ......................................................................................................406
Creating combination charts ..........................................................................................407
Displaying a data table ....................................................................................................409
Creating Chart Templates ..........................................................................................................410
Learning Some Chart-Making Tricks ........................................................................................411
Creating picture charts....................................................................................................411
Creating a thermometer chart ........................................................................................412
Creating a gauge chart ....................................................................................................413
Displaying conditional colors in a column chart..............................................................414
Creating a comparative histogram ..................................................................................415
Creating a Gantt chart ....................................................................................................417
Plotting mathematical functions with one variable ..........................................................418
Plotting mathematical functions with two variables ........................................................419
Chapter 21: Visualizing Data Using Conditional Formatting . . . . . . . . 421
About Conditional Formatting ..................................................................................................421
Specifying Conditional Formatting ............................................................................................422
Formatting types you can apply ......................................................................................423
Making your own rules ..................................................................................................424
Conditional Formats That Use Graphics....................................................................................425
Using data bars ..............................................................................................................425
A data bar example ..............................................................................................425
Using data bars in lieu of a chart ..........................................................................427
Using color scales............................................................................................................428
A color scale example ..........................................................................................428
An extreme color scale example ............................................................................429
Using Icon Sets ..............................................................................................................430
An icon set example..............................................................................................430
Another icon set example ....................................................................................432
Displaying only one icon ......................................................................................433
Creating Formula-Based Rules ..................................................................................................434
Understanding relative and absolute references ..............................................................435
Conditional formatting formula examples ......................................................................436
Identifying weekend days ....................................................................................436
Displaying alternate-row shading..........................................................................436
Creating checkerboard shading ............................................................................437
Shading groups of rows ........................................................................................437
Displaying a total only when all values are entered ..............................................437
Working with Conditional Formats ..........................................................................................439
Managing rules ..............................................................................................................439
Copying cells that contain conditional formatting ..........................................................439
Deleting conditional formatting ......................................................................................439
Find and Replace limitations ..........................................................................................440
Locating cells that contain conditional formatting ..........................................................440
Chapter 22: Enhancing Your Work with Pictures and Drawings . . . . . . . 441
Using Shapes ............................................................................................................................441
Inserting a Shape ............................................................................................................441
Adding text to a Shape ....................................................................................................444
Formatting Shapes ..........................................................................................................445
Grouping objects ............................................................................................................446
Aligning and spacing objects ..........................................................................................446
Reshaping Shapes ..........................................................................................................446
Printing objects ..............................................................................................................447
Using SmartArt.........................................................................................................................448
Inserting SmartArt ..........................................................................................................448
Customizing SmartArt ....................................................................................................449
Changing the layout........................................................................................................450
Changing the Style ..........................................................................................................452
Learning more about SmartArt........................................................................................452
Part IV: Using Advanced Excel Features 459
Chapter 23: Customizing the Quick Access Toolbar . . . . . . . . . . . . . 461
About the QAT..........................................................................................................................461
Adding New Commands to the QAT ........................................................................................462
Other QAT Actions....................................................................................................................464
Behind the Scenes ....................................................................................................................464
Chapter 24: Using Custom Number Formats . . . . . . . . . . . . . . . . . 465
About Number Formatting........................................................................................................465
Automatic number formatting ........................................................................................466
Formatting numbers by using the Ribbon ......................................................................466
Using shortcut keys to format numbers ..........................................................................467
Using the Format Cells dialog box to format numbers ....................................................467
Creating a Custom Number Format ..........................................................................................468
Parts of a number format string ......................................................................................469
Custom number format codes ........................................................................................470
Custom Number Format Examples ..........................................................................................472
Scaling values..................................................................................................................472
Displaying values in thousands ............................................................................472
Displaying values in hundreds ..............................................................................473
Displaying values in millions ................................................................................473
Adding zeros to a value ........................................................................................475
Displaying leading zeros..................................................................................................475
Displaying fractions ........................................................................................................476
Displaying a negative sign on the right............................................................................476
Formatting dates and times ............................................................................................477
Displaying text with numbers ........................................................................................478
Suppressing certain types of entries ................................................................................478
Filling a cell with a repeating character ..........................................................................479
Chapter 25: Using Data Validation . . . . . . . . . . . . . . . . . . . . . . 481
About Data Validation ..............................................................................................................481
Specifying Validation Criteria ....................................................................................................482
Types of Validation Criteria You Can Apply ..............................................................................483
Creating a Drop-Down List........................................................................................................484
Using Formulas for Data Validation Rules..................................................................................485
Understanding Cell References ..................................................................................................485
Data Validation Examples ..........................................................................................................487
Accepting text only ........................................................................................................487
Accepting a larger value than the previous cell ................................................................487
Accepting nonduplicate entries only ..............................................................................487
Accepting text that begins with A....................................................................................488
Accepting only a date that’s a Monday ............................................................................488
Accepting only values that don’t exceed a total................................................................488
Chapter 26: Creating and Using Worksheet Outlines . . . . . . . . . . . . 491
Introducing Worksheet Outlines ..............................................................................................491
Creating an Outline ..................................................................................................................494
Preparing the data ..........................................................................................................494
Creating an outline automatically....................................................................................495
Creating an outline manually ..........................................................................................495
Working with Outlines..............................................................................................................496
Displaying levels ............................................................................................................496
Adding data to an outline................................................................................................496
Removing an outline ......................................................................................................496
Hiding the outline symbols ............................................................................................497
Chapter 27: Linking and Consolidating Worksheets . . . . . . . . . . . . . 499
Linking Workbooks ..................................................................................................................499
Creating External Reference Formulas ......................................................................................500
Understanding the link formula syntax ..........................................................................500
Creating a link formula by pointing ................................................................................500
Pasting links....................................................................................................................501
Working with External Reference Formulas ..............................................................................501
Creating links to unsaved workbooks ............................................................................501
Opening a workbook with external reference formulas ..................................................502
Changing the startup prompt..........................................................................................503
Updating links ................................................................................................................503
Changing the link source ................................................................................................503
Severing links..................................................................................................................503
Avoiding Potential Problems with External Reference Formulas ................................................504
Renaming or moving a source workbook ........................................................................504
Using the Save As command ..........................................................................................504
Modifying a source workbook ........................................................................................504
Intermediary links ..........................................................................................................505
Consolidating Worksheets ........................................................................................................505
Consolidating worksheets by using formulas ..................................................................505
Consolidating worksheets by using Paste Special ............................................................506
Consolidating worksheets by using the Consolidate command ......................................507
An example ..........................................................................................................508
Refreshing a consolidation ....................................................................................509
More about consolidation ....................................................................................511
Chapter 28: Excel and the Internet . . . . . . . . . . . . . . . . . . . . . . 513
Understanding How Excel Uses HTML ....................................................................................513
Understanding the Different Web Formats ................................................................................514
Creating an HTML file ....................................................................................................514
Creating a single file Web page........................................................................................515
Chapter 29: Sharing Data with Other Applications . . . . . . . . . . . . . 521
Understanding Data Sharing......................................................................................................521
Copying and Pasting ................................................................................................................521
Copying from Excel to Word ....................................................................................................522
Pasting static information................................................................................................523
Pasting a link ..................................................................................................................524
Embedding Objects in a Worksheet ..........................................................................................526
Embedding Word documents..........................................................................................526
Embedding other types of documents ............................................................................527
Embedding an Excel Workbook in a Word Document ..............................................................527
Embedding a workbook in Word by copying ..................................................................528
Embedding a saved workbook in Word ..........................................................................529
Creating a new Excel object in Word ..............................................................................529
Chapter 30: Using Excel in a Workgroup . . . . . . . . . . . . . . . . . . . 531
Using Excel on a Network ........................................................................................................531
Understanding File Reservations ..............................................................................................532
Sharing Workbooks ..................................................................................................................533
Understanding shared workbooks ..................................................................................533
Designating a workbook as a shared workbook ..............................................................534
Controlling the advanced sharing settings ......................................................................535
Tracking changes ..................................................................................................535
Updating changes ................................................................................................535
Resolving conflicting changes between users ........................................................536
Controlling the Include in Personal View settings ................................................536
Tracking Workbook Changes ....................................................................................................536
Turning Track Changes on and off ..................................................................................536
Reviewing the changes ....................................................................................................538
Chapter 31: Protecting Your Work. . . . . . . . . . . . . . . . . . . . . . . 539
Types of Protection ....................................................................................................................539
Worksheet Protection ................................................................................................................540
Unlocking cells ..............................................................................................................540
Sheet protection options ................................................................................................542
Assigning User Permissions ............................................................................................542
Workbook Protection ................................................................................................................543
Requiring a password to open a workbook
Chapter 32: Making Your Worksheets Error-Free . . . . . . . . . . . . . . . 551
Finding and Correcting Formula Errors ....................................................................................551
Mismatched parentheses ................................................................................................552
Cells are filled with hash marks ......................................................................................552
Blank cells are not blank ................................................................................................553
Extra space characters ....................................................................................................553
Formulas returning an error............................................................................................554
#DIV/0! errors ......................................................................................................554
#N/A errors ..........................................................................................................555
#NAME? errors ....................................................................................................555
#NULL! errors ......................................................................................................555
#NUM! errors ......................................................................................................556
#REF! errors ........................................................................................................556
#VALUE! errors ....................................................................................................556
Absolute/relative reference problems ..............................................................................556
Operator precedence problems ......................................................................................557
Formulas are not calculated ............................................................................................558
Actual versus displayed values ........................................................................................558
Floating point number errors ..........................................................................................559
“Phantom link” errors ....................................................................................................560
Using Excel’s Auditing Tools......................................................................................................560
Identifying cells of a particular type ................................................................................560
Viewing formulas ............................................................................................................562
Tracing cell relationships ................................................................................................562
Identifying precedents ..........................................................................................563
Identifying dependents ........................................................................................564
Tracing error values ........................................................................................................564
Fixing circular reference errors........................................................................................564
Using background error-checking feature........................................................................564
Using Excel Formula Evaluator ......................................................................................566
Searching and Replacing............................................................................................................567
Searching for information ..............................................................................................567
Replacing information ....................................................................................................568
Searching for formatting..................................................................................................568
Spell Checking Your Worksheets ..............................................................................................569
Using AutoCorrect ....................................................................................................................570
Part V: Analyzing Data with Excel 573
Chapter 33: Using Microsoft Query with External Database Files . . . . . 575
Understanding External Database Files......................................................................................575
Importing Access Tables ............................................................................................................576
Chapter 34: Introducing Pivot Tables . . . . . . . . . . . . . . . . . . . . . 591
About Pivot Tables ....................................................................................................................591
A pivot table example ....................................................................................................592
Data appropriate for a pivot table....................................................................................594
Creating a Pivot Table................................................................................................................595
Specifying the data..........................................................................................................595
Specifying the location for the pivot table ......................................................................596
Laying out the pivot table ..............................................................................................597
Formatting the pivot table ..............................................................................................599
Modifying the pivot table ................................................................................................601
More Pivot Table Examples ......................................................................................................602
Question 1 ......................................................................................................................603
Question 2 ......................................................................................................................604
Question 3 ......................................................................................................................604
Question 4 ......................................................................................................................605
Question 5 ......................................................................................................................606
Question 6 ......................................................................................................................606
Learning More ..........................................................................................................................607
Chapter 35: Analyzing Data with Pivot Tables . . . . . . . . . . . . . . . . 609
Working with Non-Numeric Data ............................................................................................609
Grouping Pivot Table Items ......................................................................................................611
A manual grouping example ..........................................................................................611
Viewing grouped data ....................................................................................................
Chapter 36: Performing Spreadsheet What-If Analysis . . . . . . . . . . . . 635
A What-If Example....................................................................................................................635
Types of What-If Analyses ........................................................................................................637
Manual What-If Analysis ..........................................................................................................637
Creating Data Tables..................................................................................................................637
Creating a one-input data table ......................................................................................637
Creating a two-input data table ......................................................................................640
Using Scenario Manager ............................................................................................................643
Defining scenarios ..........................................................................................................643
Displaying scenarios........................................................................................................645
Modifying scenarios ........................................................................................................645
Merging scenarios ..........................................................................................................646
Generating a scenario report ..........................................................................................647
Chapter 37: Analyzing Data Using Goal Seek and Solver . . . . . . . . . . 649
What-If Analysis, in Reverse......................................................................................................649
Single-Cell Goal Seeking ..........................................................................................................650
A goal-seeking example ..................................................................................................650
More about Goal Seeking ................................................................................................651
Introducing Solver ....................................................................................................................652
Appropriate problems for Solver ....................................................................................652
A simple Solver example ................................................................................................653
More about Solver ..........................................................................................................656
Solver Examples ........................................................................................................................658
Solving simultaneous linear equations ............................................................................658
Minimizing shipping costs ..............................................................................................660
Allocating resources ........................................................................................................662
Optimizing an investment portfolio ................................................................................664
Chapter 38: Analyzing Data with the Analysis ToolPak. . . . . . . . . . . . 667
The Analysis ToolPak: An Overview ..........................................................................................667
Installing the Analysis ToolPak Add-in ......................................................................................668
Using the Analysis Tools............................................................................................................668
Introducing the Analysis ToolPak Tools ....................................................................................669
The Analysis of variance tool ..........................................................................................669
The Correlation tool........................................................................................................670
Part VI: Programming Excel with VBA 679
Chapter 39: Introducing Visual Basic for Applications . . . . . . . . . . . . 681
Introducing VBA Macros ..........................................................................................................681
Displaying the Developer tab ....................................................................................................681
About Macro Security................................................................................................................682
Saving Workbooks That Contain Macros ..................................................................................684
Two Types of VBA Macros..........................................................................................................684
VBA Sub procedures ......................................................................................................684
VBA functions ................................................................................................................685
Creating VBA Macros ................................................................................................................687
Recording VBA macros....................................................................................................687
Recording your actions to create VBA code: The basics ........................................687
Recording a macro: A simple example ..................................................................688
Examining the macro............................................................................................688
Testing the macro ................................................................................................689
Editing the macro ................................................................................................689
Another example ..................................................................................................
Chapter 40: Creating Custom Worksheet Functions . . . . . . . . . . . . . 703
Overview of VBA Functions ......................................................................................................703
An Introductory Example..........................................................................................................704
A custom function ..........................................................................................................704
Using the function in a worksheet ..................................................................................704
Analyzing the custom function........................................................................................705
About Function Procedures ......................................................................................................705
Executing Function Procedures ................................................................................................706
Calling custom functions from a procedure ....................................................................706
Using custom functions in a worksheet formula..............................................................707
Function Procedure Arguments ................................................................................................707
A function with no argument ..........................................................................................708
A function with one argument ........................................................................................708
Another function with one argument ..............................................................................708
A function with two arguments ......................................................................................710
A function with a range argument ..................................................................................710
Debugging Custom Functions ..................................................................................................711
Inserting Custom Functions ......................................................................................................712
Learning More ..........................................................................................................................713
Chapter 41: Creating UserForms . . . . . . . . . . . . . . . . . . . . . . . 715
Why Create UserForms?............................................................................................................715
Use.............................................764
Saving all workbooks ......................................................................................................764
Saving and closing all workbooks ..................................................................................764
Working with Charts ................................................................................................................765
Modifying the chart type ................................................................................................765
Modifying chart properties..............................................................................................766
Applying chart formatting ..............................................................................................766
VBA Speed Tips ........................................................................................................................766
Turning off screen updating ............................................................................................767
Preventing alert messages................................................................................................767
Simplifying object references ..........................................................................................767
Declaring variable types ..................................................................................................768
Chapter 45: Creating Custom Excel Add-Ins . . . . . . . . . . . . . . . . . 771
What Is an Add-In? ..................................................................................................................771
Working with Add-Ins ..............................................................................................................772
Why Create Add-Ins? ................................................................................................................773
Creating Add-Ins ......................................................................................................................774
An Add-In Example ..................................................................................................................775
Setting up the workbook ................................................................................................775
Procedures in Module1 ..................................................................................................776
About the UserForm ......................................................................................................776
Testing the workbook ....................................................................................................777
Adding descriptive information ......................................................................................778
Protecting the project ......................................................................................................778
Creating the add-in ........................................................................................................779
Creating the user interface for your add-in macro ..........................................................779
Installing the add-in........................................................................................................
Part VII: Appendixes 783
Appendix A: Worksheet Function Reference . . . . . . . . . . . . . . . . . 785
Appendix B: What’s on the CD-ROM . . . . . . . . . . . . . . . . . . . . . 799
Appendix C: Additional Excel Resources . . . . . . . . . . . . . . . . . . . 809
Appendix D: Excel Shortcut Keys . . . . . . . . . . . . . . . . . . . . . . . 815
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823
Wiley Publishing, Inc. End-User License Agreement . . . . . . . . . . . . . 869
Writing Excel 2007 Bible was one of my most challenging projects. Never before has an Excel
upgrade incorporated so many new features and changes. Thanks to the brilliant people at
Microsoft for the hard work it took to get this product out the door at Redmond, and onto
my hard drive. I can’t say that I agree with all of their user interface decisions, but I have no doubt that
this is the best version of Excel ever. It’s always a pleasure to deal with Greg Croy, acquisitions editor.
I’ve worked with Greg for many years, and I appreciate all he does to get my books to market in a timely
manner. And a special thank-you to Kelly Ewing, project editor for this book. She and the other talented
people behind the scenes did an incredible job of converting my sloppy and often incomprehensible
Word files into real book pages that actually make sense.
A few other people also deserve thanks. First of all, I’d like to acknowledge Mark Tedeschi, who was
the first to request. I must also thank ~Q~ for opening my eyes to new possibilities for the oft-neglected
tilde. And I thank Michael D. Bono for his life-long guidance and sound hypocritical stance on key
issues.
It is with deepest humility that I thank Anagram, a longtime contributor to banjo, biplane, biker, and
binary appreciation societies around the world. When I grow up, I want to be just like her. And, to keep
it in the family, a special thanks to Bisbonian, who introduced me to the art of flailing the banjo and
took me on an awesome biplane ride over Bisbee, Arizona.
A few random pages of this book (towards the back) are dedicated to young MacDonald — the one
without the farm. Thanks to Satyrsong and MK, for sushi and steaming up my glasses. Just as Joe Blow
taught me the true meaning of Swiss cheese, Keveena taught me the meaning of bhroondaglog (and I’m
truly grateful for that). And my gratitude even extends to Michael R. Bernstein, who taught me everything
I know about Hannukwanzaamas.
Very special thanks go out to my long-time friend Wendy Lauver, a dedicated fan, an occasional pivot
table princess, and a self-described charting hag. She’s one of the few people in this modern world who
deserves an exclamation point after her name. Here’s to you, Wendy!
A special acknowledgment goes out to Margie Corbett, for encouraging her husband to pursue his
Excel obsession, and for allowing him to buy this book with his own money. I’d also like to thank
Gerard Gibbons, who simply wants to confuse his wife by having his name appear in an Excel book. I’d
be remiss if I didn’t thank Curtis Curtington for being meshugeh ahf toit. Oh, and for the underpants.
A special thanks to Stephen, from Kennesaw State University, for his commitment to raising the quality
of education in our great country. I’d also like to thank the always deft Biff, for answering thousands of
Excel questions in the Microsoft newsgroups so I could spend my time writing this book. And thanks to
Brent Nichols, who took the time to explain to me the difference between Excel and axles. Now, looking
under my car isn’t so perplexing. Thanks, Mr. Ed, for siring the love of my life.
I’m down on my knees when I thank Spirit Mountain for providing the inspiration to complete this
book. And I’m grateful to RickHap, for donating Faith Mountain to my charity fundraiser for Whole
Wheat Radio. And, of course, special thanks to Cindy in Wasilla for helping me with the decorating
