Cover image for Excel 2003 formulas
Excel 2003 formulas
Walkenbach, John.
Personal Author:
Publication Information:
Indianapolis, IN : Wiley Pub., [2004]

Physical Description:
xxxiv, 826 pages: illustrations; 24 cm + 1 CD-ROM (4 3/4 in.)
Format :


Call Number
Material Type
Home Location
Item Holds
HF5548.4.M523 W34472 2004 Book and Software Set Central Closed Stacks

On Order



Everything you need to know about
* Mastering operators, error values, naming techniques, and absolute versus relative references
* Debugging formulas and using the auditing tools
* Importing and exporting XML files and mapping the data to specific cells
* Using Excel 2003's rights management feature
* Working magic with array formulas
* Developing custom formulas to produce the results you need

Here's the formula for Excel excellence

Formulas are the lifeblood of spreadsheets, and no one can bring a spreadsheet to life like John Walkenbach. In this detailed reference guide, he delves deeply into understanding, creating, and applying formulas in everything from basic workbooks to charts, pivot tables, and more advanced Excel applications. He examines financial formulas, explores the many options made possible with array formulas, teaches you to develop custom worksheet functions with VBA, and much more. Once again, "Mr. Spreadsheet" will astound you with the breadth and depth of Excel's capacity.

CD-ROM Includes
* Trial version of the author's award-winning Power Utility Pak 5
* More than 90 sample workbooks illustrating key formula concepts

Author Notes

John Walkenbach, principal of JWalk and Associates Inc., is a leading authority on spreadsheet software and creator of the award-winning Power Utility Pak

Table of Contents

Prefacep. vii
Acknowledgmentsp. xv
Part I Basic Information
Chapter 1 Excel in a Nutshellp. 3
The History of Excelp. 4
The Object Model Conceptp. 7
The Workings of Workbooksp. 8
Excel's User Interfacep. 10
Excel's Help Systemp. 16
Cell Formattingp. 17
Worksheet Formulas and Functionsp. 18
Objects on the Drawing Layerp. 19
Customization in Excelp. 21
Analysis Toolsp. 22
Protection Optionsp. 25
Chapter 2 Basic Facts about Formulasp. 29
Entering and Editing Formulasp. 29
Using Operators in Formulasp. 35
Calculating Formulasp. 40
Cell and Range Referencesp. 41
Making an Exact Copy of a Formulap. 45
Converting Formulas to Valuesp. 46
Hiding Formulasp. 48
Errors in Formulasp. 49
Dealing with Circular Referencesp. 50
Goal Seekingp. 52
Chapter 3 Working with Namesp. 55
What's in a Name?p. 55
Methods for Creating Cell and Range Namesp. 56
Creating Multisheet Namesp. 63
A Name's Scopep. 64
Working with Range and Cell Namesp. 66
How Excel Maintains Cell and Range Namesp. 75
Potential Problems with Namesp. 75
The Secret to Understanding Namesp. 78
Advanced Techniques That Use Namesp. 86
Part II Using Functions in Your Formulas
Chapter 4 Introducing Worksheet Functionsp. 95
What Is a Function?p. 95
Function Argument Typesp. 98
Ways to Enter a Function into a Formulap. 102
Function Categoriesp. 107
Chapter 5 Manipulating Textp. 111
A Few Words about Textp. 111
Text Functionsp. 113
Advanced Text Formulasp. 127
Custom VBA Text Functionsp. 134
Chapter 6 Working with Dates and Timesp. 135
How Excel Handles Dates and Timesp. 135
Date-Related Functionsp. 145
Time-Related Functionsp. 164
Chapter 7 Counting and Summing Techniquesp. 175
Counting and Summing Worksheet Cellsp. 175
Counting or Summing Records in Databases and Pivot Tablesp. 178
Basic Counting Formulasp. 178
Advanced Counting Formulasp. 182
Summing Formulasp. 196
Conditional Sums Using a Single Criterionp. 199
Conditional Sums Using Multiple Criteriap. 202
Using VBA Functions to Count and Sump. 205
Chapter 8 Using Lookup Functionsp. 207
What Is a Lookup Formula?p. 207
Functions Relevant to Lookupsp. 208
Basic Lookup Formulasp. 209
Specialized Lookup Formulasp. 216
Chapter 9 Databases and Listsp. 233
Worksheet Lists or Databasesp. 233
Working with a Designated Listp. 235
Using AutoFilteringp. 238
Using Advanced Filteringp. 245
Specifying Advanced Filter Criteriap. 249
Using Database Functions with Listsp. 258
Summarizing a List with a Data Tablep. 261
Creating Subtotalsp. 264
Chapter 10 Miscellaneous Calculationsp. 267
Unit Conversionsp. 267
Solving Right Trianglesp. 278
Area, Surface, Circumference, and Volume Calculationsp. 280
Solving Simultaneous Equationsp. 283
Rounding Numbersp. 284
Part III Financial Formulas
Chapter 11 Introducing Financial Formulasp. 293
Excel's Basic Financial Functionsp. 293
Signing of Money Flows Conventionp. 295
Accumulation, Discounting, and Amortization Functionsp. 297
Converting Interest Ratesp. 315
Effective Cost of Loansp. 321
Calculating the Interest and Principal Componentsp. 324
Matching Different Interest and Payment Frequenciesp. 327
Limitations of Excel's Financial Functionsp. 328
Chapter 12 Discounting and Depreciation Financial Functionsp. 333
Using the NPV Functionp. 333
Using the IRR Functionp. 343
Multiple Rates of IRR and the MIRR Functionp. 347
Using the FVSCHEDULE Functionp. 350
Depreciation Calculationsp. 352
Chapter 13 Advanced Uses of Financial Functions and Formulasp. 357
Creating Dynamic Financial Schedulesp. 357
Creating Amortization Schedulesp. 358
Summarizing Loan Options Using a Data Tablep. 364
Accumulation Schedulesp. 368
Discounted Cash Flow Schedulesp. 370
Credit Card Calculationsp. 371
XIRR and XNPV Functionsp. 373
Variable Rate Analysisp. 376
Creating Indicesp. 377
Part IV Array Formulas
Chapter 14 Introducing Arraysp. 383
Introducing Array Formulasp. 383
Understanding the Dimensions of an Arrayp. 387
Naming Array Constantsp. 390
Working with Array Formulasp. 391
Using Multicell Array Formulasp. 394
Using Single-Cell Array Formulasp. 399
Chapter 15 Performing Magic with Array Formulasp. 405
Working with Single-Cell Array Formulasp. 405
Working with Multicell Array Formulasp. 423
Returning an Array from a Custom VBA Functionp. 427
Part V Miscellaneous Formula Techniques
Chapter 16 Intentional Circular Referencesp. 433
What Are Circular References?p. 433
Intentional Circular Referencesp. 435
How Excel Determines Calculation and Iteration Settingsp. 438
Circular Reference Examplesp. 439
Potential Problems with Intentional Circular Referencesp. 446
Chapter 17 Charting Techniquesp. 449
Representing Data in Chartsp. 449
Plotting Data Interactivelyp. 469
Creating Awesome Designsp. 477
Working with Trendlinesp. 478
Useful Chart Tricksp. 488
Chapter 18 Pivot Tablesp. 497
About Pivot Tablesp. 497
Creating a Pivot Tablep. 502
Grouping Pivot Table Itemsp. 511
Creating a Calculated Field or Calculated Itemp. 514
Chapter 19 Conditional Formatting and Data Validationp. 521
Conditional Formattingp. 521
Data Validationp. 542
Chapter 20 Creating Megaformulasp. 551
What Is a Megaformula?p. 551
Creating a Megaformula: A Simple Examplep. 552
Megaformula Examplesp. 554
The Pros and Cons of Megaformulasp. 567
Chapter 21 Tools and Methods for Debugging Formulasp. 569
Formula Debugging?p. 569
Formula Problems and Solutionsp. 570
Excel's Auditing Toolsp. 582
Third-Party Auditing Toolsp. 591
Part VI Developing Custom Worksheet Functions
Chapter 22 Introducing VBAp. 597
About VBAp. 597
Introducing the Visual Basic Editorp. 598
Chapter 23 Function Procedure Basicsp. 609
Why Create Custom Functions?p. 609
An Introductory VBA Function Examplep. 610
About Function Proceduresp. 612
Using the Insert Function Dialog Boxp. 615
Testing and Debugging Your Functionsp. 619
Creating Add-Insp. 626
Chapter 24 VBA Programming Conceptsp. 629
An Introductory Example Function Procedurep. 629
Using Comments in Your Codep. 632
Using Variables, Data Types, and Constantsp. 632
Using Assignment Expressionsp. 638
Using Arraysp. 640
Using VBA's Built-in Functionsp. 641
Controlling Executionp. 643
Using Rangesp. 653
Chapter 25 VBA Custom Function Examplesp. 663
Simple Functionsp. 663
Determining a Cell's Data Typep. 669
A Multifunctional Functionp. 670
Generating Random Numbersp. 672
Calculating Sales Commissionsp. 674
Text Manipulation Functionsp. 678
Counting and Summing Functionsp. 684
Date Functionsp. 687
Returning the Last Nonempty Cell in a Column or Rowp. 690
Multisheet Functionsp. 692
Advanced Function Techniquesp. 695
Appendix A Working with Imported 1-2-3 Filesp. 709
Appendix B Excel Function Referencep. 717
Appendix C Using Custom Number Formatsp. 735
Appendix D Additional Excel Resourcesp. 761
Appendix E What's on the CD-ROMp. 769
Indexp. 783
End-User License Agreementp. 829