About the Author -- Contents at a Glance -- Table of Contents -- INTRODUCTION -- What You Need to Know -- What You Need to Have -- Conventions in This Book -- How This Book Is Organized -- How to Use This Book -- About the Companion CD-ROM -- About the Power Utility Pak Offer -- Reach Out -- Part I: Basic Information -- Chapter 1: Excel in a Nutshell -- The History of Excel -- The Object Model Concept -- The Workings of Workbooks -- The Excel User Interface -- The Excel Help System -- Cell Formatting -- Tables -- Worksheet Formulas and Functions -- Objects on the Drawing Layer -- Customizing Excel -- Internet Features -- Analysis Tools -- Protection Options -- Chapter 2: Basic Facts about Formulas -- Entering and Editing Formulas -- Using Operators in Formulas -- Calculating Formulas -- Cell and Range References -- Making an Exact Copy of a Formula -- Converting Formulas to Values -- Hiding Formulas -- Errors in Formulas -- Dealing with Circular References -- Goal Seeking -- Chapter 3: Working with Names -- What's in a Name? -- A Name's Scope -- The Name Manager -- Shortcuts for Creating Cell and Range Names -- Creating Multisheet Names -- Working with Range and Cell Names -- How Excel Maintains Cell and Range Names -- Potential Problems with Names -- The Secret to Understanding Names -- Advanced Techniques That Use Names -- Part II: Using Functions in Your Formulas -- Chapter 4: Introducing Worksheet Functions -- What Is a Function? -- Function Argument Types -- Ways to Enter a Function into a Formula -- Function Categories -- Chapter 5: Manipulating Text -- A Few Words about Text -- Text Functions -- Advanced Text Formulas -- Chapter 6: Working with Dates and Times -- How Excel Handles Dates and Times -- Date-Related Functions -- Time-Related Functions -- Chapter 7: Counting and Summing Techniques -- Counting and Summing Worksheet Cells.

Counting or Summing Records in Databases and Pivot Tables -- Basic Counting Formulas -- Advanced Counting Formulas -- Summing Formulas -- Conditional Sums Using a Single Criterion -- Conditional Sums Using Multiple Criteria -- Chapter 8: Using Lookup Functions -- What Is a Lookup Formula? -- Functions Relevant to Lookups -- Basic Lookup Formulas -- Specialized Lookup Formulas -- Chapter 9: Tables and Worksheet Databases -- Tables and Terminology -- Working with Tables -- Using Advanced Filtering -- Specifying Advanced Filter Criteria -- Using Database Functions -- Inserting Subtotals -- Chapter 10: Miscellaneous Calculations -- Unit Conversions -- Solving Right Triangles -- Area, Surface, Circumference, and Volume Calculations -- Solving Simultaneous Equations -- Rounding Numbers -- Part III: Financial Formulas -- Chapter 11: Borrowing and Investing Formulas -- Financial Concepts -- The Basic Excel Financial Functions -- Calculating the Interest and Principal Components -- Converting Interest Rates -- Limitations of Excel's Financial Functions -- Bond Calculations -- Chapter 12: Discounting and Depreciation Formulas -- Using the NPV Function -- Using the IRR Function -- Multiple Rates of IRR and the MIRR Function -- Irregular Cash Flows -- Using the FVSCHEDULE Function -- Depreciation Calculations -- Chapter 13: Financial Schedules -- Creating Financial Schedules -- Creating Amortization Schedules -- Summarizing Loan Options Using a Data Table -- Financial Statements and Ratios -- Creating Indices -- Part IV: Array Formulas -- Chapter 14: Introducing Arrays -- Introducing Array Formulas -- Understanding the Dimensions of an Array -- Naming Array Constants -- Working with Array Formulas -- Using Multicell Array Formulas -- Using Single-Cell Array Formulas -- Chapter 15: Performing Magic with Array Formulas -- Working with Single-Cell Array Formulas.

Working with Multicell Array Formulas -- Part V: Miscellaneous Formula Techniques -- Chapter 16: Intentional Circular References -- What Are Circular References? -- Intentional Circular References -- How Excel Determines Calculation and Iteration Settings -- Circular Reference Examples -- Potential Problems with Intentional Circular References -- Chapter 17: Charting Techniques -- Understanding the SERIES Formula -- Creating Links to Cells -- Chart Examples -- Working with Trendlines -- Chapter 18: Pivot Tables -- About Pivot Tables -- A Pivot Table Example -- Data Appropriate for a Pivot Table -- Creating a Pivot Table -- More Pivot Table Examples -- Grouping Pivot Table Items -- Creating a Frequency Distribution -- Creating a Calculated Field or Calculated Item -- Filtering Pivot Tables with Slicers -- Referencing Cells within a Pivot Table -- Another Pivot Table Example -- Producing a Report with a Pivot Table -- Chapter 19: Conditional Formatting and Data Validation -- Conditional Formatting -- Data Validation -- Chapter 20: Creating Megaformulas -- What Is a Megaformula? -- Creating a Megaformula: A Simple Example -- Megaformula Examples -- The Pros and Cons of Megaformulas -- Chapter 21: Tools and Methods for Debugging Formulas -- Formula Debugging? -- Formula Problems and Solutions -- Excel's Auditing Tools -- Part VI: Developing Custom Worksheet Functions -- Chapter 22: Introducing VBA -- About VBA -- Displaying the Developer Tab -- About Macro Security -- Saving Workbooks That Contain Macros -- Introducing the Visual Basic Editor -- Chapter 23: Function Procedure Basics -- Why Create Custom Functions? -- An Introductory VBA Function Example -- About Function Procedures -- Using the Insert Function Dialog Box -- Testing and Debugging Your Functions -- Creating Add-Ins -- Chapter 24: VBA Programming Concepts.

An Introductory Example Function Procedure -- Using Comments in Your Code -- Using Variables, Data Types, and Constants -- Using Assignment Expressions -- Using Arrays -- Using Built-In VBA Functions -- Controlling Execution -- Using Ranges -- Chapter 25: VBA Custom Function Examples -- Simple Functions -- Determining a Cell's Data Type -- A Multifunctional Function -- Generating Random Numbers -- Calculating Sales Commissions -- Text Manipulation Functions -- Counting Functions -- Date Functions -- Returning the Last Nonempty Cell in a Column or Row -- Multisheet Functions -- Advanced Function Techniques -- Part VII: Appendixes -- Appendix A: Excel Function Reference -- Appendix B: Using Custom Number Formats -- About Number Formatting -- Creating a Custom Number Format -- Custom Number Format Examples -- Appendix C: Additional Excel Resources -- The Excel Help System -- Microsoft Technical Support -- Internet Newsgroups -- Internet Web sites -- Appendix D: What's on the CD-ROM? -- System Requirements -- Using the CD -- Files and Software on the CD -- Troubleshooting -- Index.