Excel.2007.Bible.pdf

About Global Documents

Global Documents provides you with documents from around the globe on a variety of topics for your enjoyment.
Global Documents utilizes edocr for all its document needs due to edocr's wonderful content features. Thousands of professionals and businesses around the globe publish marketing, sales, operations, customer service and financial documents making it easier for prospects and customers to find content.
Formula Create Excel Cell format chart
John Walkenbach
Simplify number 
crunching and analysis
Create boardroom quality 
charts and diagrams
Analyze mounds of 
data with pivot tables
Walkenbach
The book you need to succeed!
The perennial bestseller—now fully 
updated for Excel 2007!
Whether you’re already a power user or just starting, find 
out how to get the most out of this major new release 
of Excel from expert instructor and Microsoft MVP, 
“Mr. Spreadsheet,” John Walkenbach. Each of the book’s 
seven parts thoroughly focuses on key elements, so no 
matter what your level of expertise, you’ll find what 
you need in the hundreds of examples, techniques, and 
tips in this comprehensive resource. 
• Master Excel’s new “menu-less” Ribbon user interface 
• Understand formulas, functions, tables, worksheets, and workbooks
• Explore the new unlimited conditional formatting
• Develop custom functions, program with VBA, and create UserForms
• Analyze data using external database files and pivot tables
• Enhance your worksheets with new SmartArt tools
• Customize the Quick Access Toolbar and save time
CD-ROM Included!
• All examples and workbooks from the book
• Searchable PDF of the book
Value-Packed CD-ROM
“ John Walkenbach provides excellent examples in this book…that Excel users will find very useful.”
— David McRitchie, Microsoft MVP 
Meet John 
Walkenbach
John Walkenbach, a.k.a “Mr. 
Spreadsheet,” is a principal of 
J-Walk and Associates, Inc. and 
a Microsoft Excel MVP. He is a 
leading authority on spreadsheet 
software and creator of the award-
winning Power Utility Pak. He has 
written more than 50 books, 
including Excel 2003 Formulas and 
Excel 2003 Power Programming 
with VBA, as well as articles and 
reviews for publications including 
PC World, InfoWorld, and 
Windows. He also maintains the 
popular Spreadsheet Page at 
www.j-walk.com/ss.
Shelving Category:
COMPUTERS/Spreadsheets 
Reader Level:
Beginning to Advanced
$39.99 USA 
$47.99 Canada 
£25.99 UK
ISBN 0-470-04403-9
Easily create summary 
reports of your data with the 
enhanced PivotTable tool
The new Page Layout View 
displays your worksheet 
exactly as it will be printed
System Requirements: 
Please see the CD-ROM 
appendix for details 
and complete system 
requirements.
®
Excel 2007
Microsoft® Offi  ce®
Microsoft® Offi  ce
John Walkenbach

on may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 
es Copyright Act, without either the prior written permission of the Publisher, or authorization
 appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA
, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal
lishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317)
http://www.wiley.com/go/permissions.
Y/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
S WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
NTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED
ALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED
E SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING
R IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL
SSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL
E SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR
HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN
TATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN
OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE
ECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT
S LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN
RITTEN AND WHEN IT IS READ.
 on our other products and services or to obtain technical support, please contact our Customer
n the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.
 Wiley logo, and related trade dress are trademarks or registered trademarks of John Wiley & Sons,
in the United States and other countries, and may not be used without written permission.
registered trademarks of Microsoft Corporation in the United States and/or other countries.  All
e property of their respective owners. Wiley Publishing, Inc., is not associated with any product or
is book.
books in a variety of electronic formats. Some content that appears in print may not be available in

in
Manager
n 
dent & Executive Group Publisher
wadley
dent and Publisher
mmings
Director
order
Jennifer Mayberry
Barbara Moore
Lynsey Osborn
Heather Pope
Ronald Terry
Quality Control Technicians
Laura Albert
Jessica Kramer
Christine Pingleton
Brian H. Walls
Media Development Project Supervisor
Laura Moss
Media Development Specialist
Steve Kudirka
Proofreading and Indexing
Techbooks

 7: Worksheet Formatting........................................................................................................109
 8: Understanding Excel’s Files ................................................................................................131
 9: Using and Creating Templates ............................................................................................145
 10: Printing Your Work ..........................................................................................................155
I: Working with Formulas and Functions . . . . . . . . . . . . . 173
 11: Introducing Formulas and Functions ..............................................................................175
 12: Creating Formulas That Manipulate Text..........................................................................205
 13: Working with Dates and Times ........................................................................................223
 14: Creating Formulas That Count and Sum..........................................................................249
 15: Creating Formulas That Look Up Values ..........................................................................273
 16: Creating Formulas for Financial Applications ..................................................................291
 17: Introducing Array Formulas ............................................................................................313
 18: Performing Magic with Array Formulas............................................................................333
II: Creating Charts and Graphics . . . . . . . . . . . . . . . . . . . 349
 19: Getting Started Making Charts ........................................................................................351
 20: Learning Advanced Charting............................................................................................381
 21: Visualizing Data Using Conditional Formatting................................................................421
 22: Enhancing Your Work with Pictures and Drawings ..........................................................441
V: Using Advanced Excel Features . . . . . . . . . . . . . . . . . . 459
 23: Customizing the Quick Access Toolbar ............................................................................461
 24: Using Custom Number Formats ......................................................................................465
 25: Using Data Validation ......................................................................................................481
 26: Creating and Using Worksheet Outlines ..........................................................................491
 27: Linking and Consolidating Worksheets ............................................................................499
 28: Excel and the Internet ......................................................................................................513
 29: Sharing Data with Other Applications ..............................................................................521
vii

3: Working with Excel Events ..............................................................................................745
4: VBA Examples..................................................................................................................757
5: Creating Custom Excel Add-Ins ......................................................................................771
I: Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783
A: Worksheet Function Reference........................................................................................785
B:  What’s on the CD-ROM..................................................................................................799
C: Additional Excel Resources ............................................................................................809
D: Excel Shortcut Keys ........................................................................................................815
............................................................................................................................................823
ishing, Inc. End-User License Agreement............................................................................869

Moving Around a Worksheet ........................................................................................................6
Navigating with your keyboard ..........................................................................................6
Navigating with your mouse ..............................................................................................7
ntroducing the Ribbon ................................................................................................................7
Ribbon tabs ........................................................................................................................8
Contextual tabs ..................................................................................................................9
Types of commands on the Ribbon....................................................................................10
Accessing the Ribbon using your keyboard ......................................................................11
sing the shortcut menus............................................................................................................13
ustomizing your Quick Access Toolbar ....................................................................................14
Working with Dialog Boxes ........................................................................................................15
Navigating dialog boxes ....................................................................................................16
Using tabbed dialog boxes ................................................................................................16
sing the Taskbar........................................................................................................................17
reating 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
er 2: What’s New in Excel 2007? . . . . . . . . . . . . . . . . . . . . . 25
 New User Interface ..................................................................................................................25
arger Worksheets ......................................................................................................................26
ew File Formats ........................................................................................................................27
Worksheet Tables ........................................................................................................................27
yles and Themes ......................................................................................................................28
ix

ering Dates and Times into Your Worksheets ........................................................................38
Entering date values..........................................................................................................39
Entering time values ........................................................................................................39
difying 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
lying 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

Splitting the worksheet window into panes ......................................................................63
Keeping the titles in view by freezing panes ......................................................................64
Monitoring cells with a Watch Window ............................................................................65
king 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
5: Working with Cells and Ranges. . . . . . . . . . . . . . . . . . . 71
erstanding 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
ying 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
xi

 6: Introducing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 95
at Is a Table? ..........................................................................................................................95
ating a Table ..........................................................................................................................97
nging the Look of a Table ......................................................................................................98
king 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
 7: Worksheet Formatting . . . . . . . . . . . . . . . . . . . . . . . 109
ing 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
ng 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

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
l File Compatibility ............................................................................................................142
Recognizing the Excel 2007 file formats..........................................................................142
Saving a file for use with an older version of Excel ..........................................................143
9: Using and Creating Templates . . . . . . . . . . . . . . . . . . . 145
oring Excel Templates ........................................................................................................145
Viewing templates ..........................................................................................................145
Creating a workbook from a template ............................................................................146
Modifying a template ......................................................................................................148
erstanding 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
xiii

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
ing 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
usting 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
enting Certain Cells from Being Printed..............................................................................170
enting Objects from being Printed ......................................................................................170
ating Custom Views of Your Worksheet ..............................................................................171
 Working with Formulas and Functions
173
 11: Introducing Formulas and Functions . . . . . . . . . . . . . . 175
erstanding 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

Handling circular references............................................................................................196
Specifying when formulas are calculated ........................................................................198
g Advanced Naming Techniques ........................................................................................199
Using names for constants ..............................................................................................199
Using names for formulas ..............................................................................................199
Using range intersections ................................................................................................200
Applying names to existing references ............................................................................202
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
12: Creating Formulas That Manipulate Text . . . . . . . . . . . . 205
w Words About Text ..........................................................................................................205
How many characters in a cell? ......................................................................................205
Numbers as text ..............................................................................................................206
Functions ..........................................................................................................................206
Determining whether a cell contains text ........................................................................207
Working with character codes ........................................................................................207
The CODE function..............................................................................................208
The CHAR function ..............................................................................................208
Determining whether two strings are identical ................................................................209
Joining two or more cells ................................................................................................210
Displaying formatted values as text ................................................................................211
Displaying formatted currency values as text ..................................................................212
Repeating a character or string ........................................................................................212
Creating a text histogram ................................................................................................212
Padding a number ..........................................................................................................213
Removing excess spaces and nonprinting characters ......................................................214
xv

 13: Working with Dates and Times . . . . . . . . . . . . . . . . . 223
w Excel Handles Dates and Times ........................................................................................223
Understanding date serial numbers ................................................................................223
Entering dates ................................................................................................................224
Understanding time serial numbers ................................................................................226
Entering times ................................................................................................................227
Formatting dates and times ............................................................................................228
Problems with dates ........................................................................................................229
Excel’s leap year bug ............................................................................................229
Pre-1900 dates......................................................................................................229
Inconsistent date entries ......................................................................................230
e-Related Functions ............................................................................................................230
Displaying the current date ............................................................................................231
Displaying any date ........................................................................................................231
Generating a series of dates ............................................................................................232
Converting a nondate string to a date..............................................................................233
Calculating the number of days between two dates ........................................................233
Calculating the number of work days between two dates ................................................234
Offsetting a date using only work days............................................................................234
Calculating the number of years between two dates ........................................................235
Calculating a person’s age................................................................................................236
Determining the day of the year ......................................................................................237
Determining the day of the week ....................................................................................237
Determining the date of the most recent Sunday ............................................................237
Determining the first day of the week after a date ..........................................................237
Determining the nth occurrence of a day of the week in a month....................................238
Calculating dates of holidays ..........................................................................................238
New Year’s Day ....................................................................................................239
Martin Luther King, Jr. Day ..................................................................................239
Presidents’ Day ....................................................................................................239

Rounding time values ....................................................................................................247
Working with non-time-of-day values ............................................................................248
14: Creating Formulas That Count and Sum. . . . . . . . . . . . . 249
nting and Summing Worksheet Cells ..................................................................................249
 Counting Formulas ..........................................................................................................251
Counting the total number of cells ..................................................................................252
Counting blank cells ......................................................................................................252
Counting nonblank cells ................................................................................................253
Counting numeric cells ..................................................................................................253
Counting text cells ..........................................................................................................253
Counting nontext cells ....................................................................................................253
Counting logical values ..................................................................................................253
Counting error values in a range ....................................................................................253
nced Counting Formulas ..................................................................................................254
Counting cells by using the COUNTIF function..............................................................254
Counting cells by using multiple criteria ........................................................................255
Using And criteria ................................................................................................256
Using Or criteria ..................................................................................................257
Combining And and Or criteria ............................................................................257
Counting the most frequently occurring entry ................................................................257
Counting the occurrences of specific text ........................................................................258
Entire cell contents ..............................................................................................259
Partial cell contents ..............................................................................................259
Total occurrences in a range..................................................................................260
Counting the number of unique values ..........................................................................260
Creating a frequency distribution....................................................................................260
The FREQUENCY function ..................................................................................261
Using formulas to create a frequency distribution ................................................262
Using the Analysis ToolPak to create a frequency distribution ..............................263
Using a pivot table to create a frequency distribution............................................264
xvii

The VLOOKUP function ................................................................................................276
The HLOOKUP function ................................................................................................277
The LOOKUP function ..................................................................................................277
Combining the MATCH and INDEX functions................................................................278
cialized Lookup Formulas....................................................................................................280
Looking up an exact value ..............................................................................................280
Looking up a value to the left..........................................................................................281
Performing a case-sensitive lookup ................................................................................282
Choosing among multiple lookup tables ........................................................................283
Determining letter grades for test scores..........................................................................283
Calculating a grade-point average ..................................................................................284
Performing a two-way lookup ........................................................................................285
Performing a two-column lookup ..................................................................................286
Determining the cell address of a value within a range ....................................................287
Looking up a value by using the closest match................................................................288
 16: Creating Formulas for Financial Applications. . . . . . . . . . 291
 Time Value of Money..........................................................................................................291
n Calculations......................................................................................................................292
Worksheet functions for calculating loan information ....................................................292
The PMT function ................................................................................................293
The PPMT function ..............................................................................................293
The IPMT Function ..............................................................................................294
The RATE function ..............................................................................................294
The NPER function ..............................................................................................294
The PV Function ..................................................................................................294
A loan calculation example ............................................................................................295
Credit-card payments......................................................................................................296
Creating a loan amortization schedule ............................................................................297

erstanding the Dimensions of an Array ..............................................................................317
One-dimensional horizontal arrays ................................................................................317
One-dimensional vertical arrays ......................................................................................318
Two-dimensional arrays ..................................................................................................318
ing Array Constants ..........................................................................................................319
king with Array Formulas ..................................................................................................320
Entering an array formula ..............................................................................................320
Selecting an array formula range ....................................................................................321
Editing an array formula ................................................................................................321
Expanding or contracting a multicell array formula ........................................................322
g Multicell Array Formulas ................................................................................................323
Creating an array from values in a range ........................................................................323
Creating an array constant from values in a range ..........................................................323
Performing operations on an array ..................................................................................324
Using functions with an array ........................................................................................325
Transposing an array ......................................................................................................325
Generating an array of consecutive integers ....................................................................326
g Single-Cell Array Formulas..............................................................................................327
Counting characters in a range........................................................................................327
Summing the three smallest values in a range ................................................................328
Counting text cells in a range..........................................................................................329
Eliminating intermediate formulas ..................................................................................330
Using an array in lieu of a range reference ......................................................................331
18: Performing Magic with Array Formulas . . . . . . . . . . . . . 333
king with Single-Cell Array Formulas ................................................................................333
Summing a range that contains errors ............................................................................333
Counting the number of error values in a range ..............................................................334
Summing the n largest values in a range..........................................................................335
xix

Returning a list of unique items in a range ......................................................................347
Displaying a calendar in a range......................................................................................347
: Creating Charts and Graphics
349
 19: Getting Started Making Charts . . . . . . . . . . . . . . . . . 351
at Is a Chart?........................................................................................................................351
erstanding How Excel Handles Charts ................................................................................352
Embedded charts ............................................................................................................353
Chart sheets ....................................................................................................................353
ating a Chart ........................................................................................................................355
ds On: Creating and Customizing a Chart ..........................................................................355
Selecting the data ............................................................................................................356
Choosing a chart type ....................................................................................................356
Experimenting with different layouts ..............................................................................357
Trying another view of the data ......................................................................................358
Trying other chart types ..................................................................................................358
Trying other chart styles..................................................................................................360
king with Charts ................................................................................................................360
Moving and resizing a chart ............................................................................................361
Copying a chart ..............................................................................................................361
Deleting a chart ..............................................................................................................361
Adding chart elements ....................................................................................................361
Moving and deleting chart elements................................................................................361
Formatting chart elements ..............................................................................................362
Printing Charts................................................................................................................363
erstanding Chart Types ......................................................................................................363
Choosing a chart type ....................................................................................................364
Chart type examples ......................................................................................................365
Column charts ......................................................................................................365
Bar charts ............................................................................................................367

Using the Format dialog box ..........................................................................................384
Using the Ribbon and Mini Toolbar ................................................................................385
fying the Chart Area ..........................................................................................................386
fying the Plot Area ............................................................................................................387
king with Chart Titles ........................................................................................................388
king with the Legend ..........................................................................................................389
king with Gridlines ............................................................................................................391
fying the Axes ..................................................................................................................391
Value axis options ................................................................................................391
Category axis options............................................................................................396
king 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
ting Chart Templates ..........................................................................................................410
ning 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
xxi

Another icon set example ....................................................................................432
Displaying only one icon ......................................................................................433
ating 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
king 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
 22: Enhancing Your Work with Pictures and Drawings . . . . . . . 441
ng 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
ng SmartArt..........................................................................................................................448
Inserting SmartArt ..........................................................................................................448
Customizing SmartArt ....................................................................................................449
Changing the layout........................................................................................................450
Changing the Style ..........................................................................................................452
Learning more about SmartArt........................................................................................452

ut 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
ting a Custom Number Format ..........................................................................................468
Parts of a number format string ......................................................................................469
Custom number format codes ........................................................................................470
om 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
25: Using Data Validation . . . . . . . . . . . . . . . . . . . . . . 481
ut Data Validation ..............................................................................................................481
ifying Validation Criteria ....................................................................................................482
s of Validation Criteria You Can Apply ..............................................................................483
ting a Drop-Down List........................................................................................................484
g Formulas for Data Validation Rules..................................................................................485
erstanding Cell References ..................................................................................................485
Validation Examples ..........................................................................................................487
Accepting text only ........................................................................................................487
Accepting a larger value than the previous cell ................................................................487
xxiii

king Workbooks ..................................................................................................................499
ating External Reference Formulas ......................................................................................500
Understanding the link formula syntax ..........................................................................500
Creating a link formula by pointing ................................................................................500
Pasting links....................................................................................................................501
king 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
ding 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
solidating 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
 28: Excel and the Internet . . . . . . . . . . . . . . . . . . . . . . 513
erstanding How Excel Uses HTML ....................................................................................513
erstanding the Different Web Formats ................................................................................514
Creating an HTML file ....................................................................................................514
Creating a single file Web page........................................................................................515

Creating a new Excel object in Word ..............................................................................529
30: Using Excel in a Workgroup . . . . . . . . . . . . . . . . . . . 531
g Excel on a Network ........................................................................................................531
erstanding File Reservations ..............................................................................................532
ng 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
king Workbook Changes ....................................................................................................536
Turning Track Changes on and off ..................................................................................536
Reviewing the changes ....................................................................................................538
31: Protecting Your Work. . . . . . . . . . . . . . . . . . . . . . . 539
s of Protection ....................................................................................................................539
ksheet Protection ................................................................................................................540
Unlocking cells ..............................................................................................................540
Sheet protection options ................................................................................................542
Assigning User Permissions ............................................................................................542
kbook Protection ................................................................................................................543
Requiring a password to open a workbook ....................................................................543
Protecting a workbook’s structure ..................................................................................544
Protecting a workbook’s windows ..................................................................................545
roject Protection ................................................................................................................545
ed Topics ..........................................................................................................................546
Saving a workbook as a PDF file ....................................................................................546
Marking a workbook final ..............................................................................................547
xxv

#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
ng 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
ching and Replacing............................................................................................................567
Searching for information ..............................................................................................567
Replacing information ....................................................................................................568
Searching for formatting..................................................................................................568
l Checking Your Worksheets ..............................................................................................569
ng AutoCorrect ....................................................................................................................570
Analyzing Data with Excel
573
 33: Using Microsoft Query with External Database Files . . . . . 575
erstanding External Database Files......................................................................................575
orting Access Tables ............................................................................................................576

Using multiple database tables ........................................................................................588
Adding and editing records in external database tables ..................................................588
Formatting data ..............................................................................................................588
ning More about Query ......................................................................................................589
34: Introducing Pivot Tables . . . . . . . . . . . . . . . . . . . . . 591
ut Pivot Tables ....................................................................................................................591
A pivot table example ....................................................................................................592
Data appropriate for a pivot table....................................................................................594
ting 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
 Pivot Table Examples ......................................................................................................602
Question 1 ......................................................................................................................603
Question 2 ......................................................................................................................604
Question 3 ......................................................................................................................604
Question 4 ......................................................................................................................605
Question 5 ......................................................................................................................606
Question 6 ......................................................................................................................606
ning More ..........................................................................................................................607
35: Analyzing Data with Pivot Tables . . . . . . . . . . . . . . . . 609
king with Non-Numeric Data ............................................................................................609
ping Pivot Table Items ......................................................................................................611
A manual grouping example ..........................................................................................611
Viewing grouped data ....................................................................................................611
xxvii

ating Data Tables..................................................................................................................637
Creating a one-input data table ......................................................................................637
Creating a two-input data table ......................................................................................640
ng Scenario Manager ............................................................................................................643
Defining scenarios ..........................................................................................................643
Displaying scenarios........................................................................................................645
Modifying scenarios ........................................................................................................645
Merging scenarios ..........................................................................................................646
Generating a scenario report ..........................................................................................647
 37: Analyzing Data Using Goal Seek and Solver . . . . . . . . . . 649
at-If Analysis, in Reverse......................................................................................................649
le-Cell Goal Seeking ..........................................................................................................650
A goal-seeking example ..................................................................................................650
More about Goal Seeking ................................................................................................651
oducing Solver ....................................................................................................................652
Appropriate problems for Solver ....................................................................................652
A simple Solver example ................................................................................................653
More about Solver ..........................................................................................................656
er Examples ........................................................................................................................658
Solving simultaneous linear equations ............................................................................658
Minimizing shipping costs ..............................................................................................660
Allocating resources ........................................................................................................662
Optimizing an investment portfolio ................................................................................664
 38: Analyzing Data with the Analysis ToolPak. . . . . . . . . . . . 667
 Analysis ToolPak: An Overview ..........................................................................................667
alling the Analysis ToolPak Add-in ......................................................................................668
ng the Analysis Tools............................................................................................................668
oducing the Analysis ToolPak Tools ....................................................................................669
The Analysis of variance tool ..........................................................................................669
The Correlation tool........................................................................................................670

ducing VBA Macros ..........................................................................................................681
aying the Developer tab ....................................................................................................681
ut Macro Security................................................................................................................682
ng Workbooks That Contain Macros ..................................................................................684
Types of VBA Macros..........................................................................................................684
VBA Sub procedures ......................................................................................................684
VBA functions ................................................................................................................685
ting 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 ..................................................................................................690
Examining the macro............................................................................................690
Testing the macro ................................................................................................691
More about recording VBA macros..................................................................................692
Absolute versus relative recording ........................................................................692
Storing macros in your Personal Macro Workbook ..............................................692
Assigning a macro to a shortcut key......................................................................693
Assigning a macro to a button ..............................................................................693
Writing VBA code ..........................................................................................................694
The basics: Entering and editing code ..................................................................694
How VBA works ..................................................................................................695
Objects and collections ........................................................................................697
Properties ............................................................................................................697
Methods ..............................................................................................................699
Variables ..............................................................................................................699
Controlling execution ..........................................................................................699
A macro that can’t be recorded..............................................................................701
ning More ..........................................................................................................................702
xxix

rting Custom Functions ......................................................................................................712
ning More ..........................................................................................................................713
 41: Creating UserForms . . . . . . . . . . . . . . . . . . . . . . . 715
y Create UserForms?............................................................................................................715
rForm Alternatives ..............................................................................................................716
The InputBox function....................................................................................................716
The MsgBox function ......................................................................................................717
ating UserForms: An Overview ............................................................................................720
Working with UserForms................................................................................................720
Adding controls ..............................................................................................................721
Changing the properties of a control ..............................................................................722
Handling events ..............................................................................................................723
Displaying a UserForm....................................................................................................724
serForm Example ................................................................................................................724
Creating the UserForm....................................................................................................724
Testing the UserForm......................................................................................................725
Creating an event-handler procedure ..............................................................................726
ther UserForm Example ......................................................................................................726
Creating the UserForm....................................................................................................727
Testing the UserForm......................................................................................................728
Creating event-handler procedures ................................................................................729
Testing the UserForm......................................................................................................730
Making the macro available from a worksheet button ....................................................731
Making the macro available on your Quick Access Toolbar ............................................731
e on Creating UserForms ....................................................................................................732
Adding accelerator keys ..................................................................................................732
Controlling tab order ......................................................................................................732
ning More ..........................................................................................................................732

ScrollBar control ............................................................................................................742
SpinButton control..........................................................................................................743
TextBox controls ............................................................................................................743
ToggleButton control ......................................................................................................744
43: Working with Excel Events . . . . . . . . . . . . . . . . . . . . 745
erstanding Events ..............................................................................................................745
Entering event-handler VBA code ..................................................................................745
g Workbook-Level Events ..................................................................................................747
Using the Open event ....................................................................................................748
Using the SheetActivate event ........................................................................................749
Using the NewSheet event ..............................................................................................749
Using the BeforeSave event ............................................................................................750
Using the BeforeClose event............................................................................................750
king with Worksheet Events ..............................................................................................750
Using the Change event ..................................................................................................751
Monitoring a specific range for changes ..........................................................................752
Using the SelectionChange event ....................................................................................752
Using the BeforeRightClick event ....................................................................................753
g Non-Object Events ..........................................................................................................754
Using the OnTime event ................................................................................................754
Using the OnKey event ..................................................................................................754
44: VBA Examples . . . . . . . . . . . . . . . . . . . . . . . . . . 757
king with Ranges ................................................................................................................757
Copying a range..............................................................................................................758
Copying a variable-size range..........................................................................................758
Selecting to the end of a row or column..........................................................................759
Selecting a row or column ..............................................................................................760
Moving a range ..............................................................................................................760
xxxi

 45: Creating Custom Excel Add-Ins . . . . . . . . . . . . . . . . . 771
at Is an Add-In? ..................................................................................................................771
king with Add-Ins ..............................................................................................................772
y Create Add-Ins? ................................................................................................................773
ating Add-Ins ......................................................................................................................774
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........................................................................................................780
I: Appendixes
783
ix A: Worksheet Function Reference . . . . . . . . . . . . . . . . . 785
ix B: What’s on the CD-ROM . . . . . . . . . . . . . . . . . . . . . 799
ix C: Additional Excel Resources . . . . . . . . . . . . . . . . . . . 809
ix D: Excel Shortcut Keys . . . . . . . . . . . . . . . . . . . . . . . 815
 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823
ublishing, Inc. End-User License Agreement . . . . . . . . . . . . . 869

her people also deserve thanks. First of all, I’d like to acknowledge Mark Tedeschi, who was
to request. I must also thank ~Q~ for opening my eyes to new possibilities for the oft-neglected
d I thank Michael D. Bono for his life-long guidance and sound hypocritical stance on key
h deepest humility that I thank Anagram, a longtime contributor to banjo, biplane, biker, and
ppreciation societies around the world. When I grow up, I want to be just like her. And, to keep
family, a special thanks to Bisbonian, who introduced me to the art of flailing the banjo and
 on an awesome biplane ride over Bisbee, Arizona.
ndom pages of this book (towards the back) are dedicated to young MacDonald — the one
the farm. Thanks to Satyrsong and MK, for sushi and steaming up my glasses. Just as Joe Blow
me the true meaning of Swiss cheese, Keveena taught me the meaning of bhroondaglog (and I’m
teful for that). And my gratitude even extends to Michael R. Bernstein, who taught me every-
now about Hannukwanzaamas.
cial thanks go out to my long-time friend Wendy Lauver, a dedicated fan, an occasional pivot
ncess, and a self-described charting hag. She’s one of the few people in this modern world who
 an exclamation point after her name. Here’s to you, Wendy!
l acknowledgment goes out to Margie Corbett, for encouraging her husband to pursue his
session, and for allowing him to buy this book with his own money. I’d also like to thank
Gibbons, who simply wants to confuse his wife by having his name appear in an Excel book. I’d
s if I didn’t thank Curtis Curtington for being meshugeh ahf toit. Oh, and for the underpants.
l thanks to Stephen, from Kennesaw State University, for his commitment to raising the quality
tion in our great country. I’d also like to thank the always deft Biff, for answering thousands of
uestions in the Microsoft newsgroups so I could spend my time writing this book. And thanks to
ichols, who took the time to explain to me the difference between Excel and axles. Now, look-
er my car isn’t so perplexing. Thanks, Mr. Ed, for siring the love of my life.
n on my knees when I thank Spirit Mountain for providing the inspiration to complete this
nd I’m grateful to RickHap, for donating Faith Mountain to my charity fundraiser for Whole
adio. And, of course, special thanks to Cindy in Wasilla for helping me with the decorating
xxxiii

ife to learning an instrument with seven fewer strings. 
 of course, never would have come to fruition without the awesome Austin music from Casey.
hank The One True Dan Tripp for agreeing to buy this book because his name is in this section.
 I should thank mare, but I can’t find the note that explains what I have to thank him/her/it for.
, thanks! And that also goes to moioci, for general intrepidity. Thanks are also due to The
Lady for making sure everyone can hang their name badge on a sparkle — something that’s
ortant in this day and age.
eciate Mr. Mike, for teaching me to play chess. His crushing victory over his 7-year old opponent
nspirational. I would also like to thank Victor Conte and his peeps at BALCO. And special
Dan and Spencer for remaining faithful to the Padres, even when they shouldn’t have been.
e a grievous error to overlook Andrew Methmann, who has several J-Walk books floating
 office, and has promised to add to the collection if he finds his name here. I hereby dedicate
 of this book to Kirk. In this world woven of illusions and insubstantial impressions, I always
w he can stand me and my books — and, of course, the blog.
nform Mary — who keeps telling me that putting things/people in boxes is wrong, wrong, 
o go soak her head. Putting things in boxes is what Excel is about. So there. Now, back to the
gments. I thank Tina, for her desire to get a raise by learning all she can about Excel, and for 
er boss and coworkers with material that she lifts directly from my books. And I must mention
Allan, one of thousands of people who can’t remember the password for Power Utility Pak and
o me via e-mail.
Ruth Maher in Ireland, who figured out the secret to using Excel. She always takes her Excel 
pint of Guinness. I must also thank Soren Bo Christensen from Denmark, for being the only
ested in Excel 2007 (or so he claims). But most of all, I thank Gareth Forster in England, for
there and not here. And least but certainly not last, I pay tribute to Lindsay, for his riveting links
flappable insistence on bifurcating them. I almost forgot. Thanks to Miss Cellania for being so
ous.

uman realm, a bark out goes to Tootsie, the best darn chocolate Cocker Spaniel living in
And thanks to Jean. I hereby offer my official apology for running over her dog and blaming it
hbors. I’m also grateful to Rex. He doesn’t understand most of the words in my books, but he
entering formulas in cell K9.
s to Dave Green, for not contributing to this book. Without his interference, I was able to com-
ime with only a few dozen major rewrites. I send a round-about circular thanks to Andrew
who often insists on reinventing the wheel. 
 to thank five of Dave Brett’s seven personalities for their valuable assistance (the other two are
ens). And I simply must acknowledge Nazire because she has an uncanny ability to interpret my
 instructions without even trying very hard. I also appreciate Alexis Cole, who insists on
s birthday working on very clunky macros. A big thanks is due Charles Chickering, for plagia-
ode to help the multitudes on the public newsgroups. 
re Stuart O’Brien, for all the long hours, hard work, and Herculean effort he put into writing
nowledgment. And thanks to Lewis Johnson, for giving me my big break — both times.
nks to Mike C, for thoughtfully stroking his beard and looking intelligent while reading my
blic places (that sort of thing really helps sales). And equally special thanks to Jan Chan, for
ng his semi-amazing Reverse Tsil Tnemgdelwonkca formula.
 time, I’d like to publicly acknowledge Dustin Spicuzza, for showing me how much fun
illboards can be. Also, thanks to Joel Schultz for using my book in all of his Excel classes, and
genius in general. I’d like to give a special thanks to Roger Martin, for his suggestion to use
pages. If only I’d learned that trick sooner in my career!
owledge Artoch, for his tireless efforts with the Reckoning. This book would have two fewer
 it weren’t for him. And I extend special thanks to Tobias H. Schmidbauer, for thinking that my
connection with The Tonight Show. How could I forget Greg P. in Fairfield? He showed me that
ch easier when you use both hands. 
xxxv

ony Williamson, Avalon, Lori, Eden, Cardi, Ricardo Dittmer, Jeremy Mathis, Dan and
Kenzie, Di Hogg, Roger Holmes, Katarina Kotulakova, and (last, but least) Phil Borkstrom. 
obliged to Claire Summers, a friend of mine who uses Excel a lot in her work, even though she
lly need to. Special thanks to Art C, who taught me everything I know about Excel — but unfor-
ot everything he knows about Excel. I’d also like to thank Big Leather Dave, for teaching me how
out hurting and hurt without hitting, skills that come in handy several times a week.
k Evan and Robyn C. enough. I admire that fact that they use their Excel powers only for good
’m also grateful to Rich (aka shades), who is old, slow, and confused — which makes me feel
, and alert. And a very special thanks to Tombraider. In my time of need, he was the only one
the Hungarian phrase for “But officer, the llama was on fire when I got here.” I’m also indebted
or accepting that extra cash I had lying around without making a big deal of it.
my “Excel can do anything” files, and remembered Guy, who deserves thanks for helping me
 wiring in my ‘51 NoCaster using a circuit design created in an Excel worksheet (no macros). I
 like to thank the Gideon Society. It may be a baseless rumor, but I’ve heard that they will be
 copy of this book along with the standard fare they supply to hotel and motel rooms every-
d, of course, I’d like to acknowledge Mike Hiscock. He’s just this guy I know.
preciative of Sheldon Reynolds. Even though he’s not the guitarist Sheldon Reynolds from Earth
Fire, he made me laugh once (or maybe I’m thinking of someone else). And a very extra super-
nks to ClownPie. His love of pie taught me that there’s only one thing in this world to live for.
ely, the love of his life generates a #NAME error in Excel. The correct spelling is PI().
ial word up to Jack Faley, for pointing out that I forgot to carry the zero on page 582. Because of
ation, this book is now certified error-free. I’d also like to thank Wilma Compton, for being the
ar-old regular reader of the J-Walk Blog. But that’s just because her dad reads Excel books. And
ke to thank Quasimike Why? Falettinme Be Mice Elf Agin.
h no choice but to express my heartfelt gratitude to Fine-line, whose incalculable ignorance of
es me gainful employment well into my twilight years. And if I spoke Spanish, I’d say gracias to
rd and Rubberband for rockin’ the house. On a related note, many thanks Left Hand Green for
y face off.