| |
| |
| Preface | |
| |
| |
| Acknowledgments | |
| |
| |
| |
| Basic Information | |
| |
| |
| |
| Excel in a Nutshell | |
| |
| |
| The History of Excel | |
| |
| |
| The Object Model Concept | |
| |
| |
| The Workings of Workbooks | |
| |
| |
| Excel's User Interface | |
| |
| |
| Cell Formatting | |
| |
| |
| Worksheet Formulas and Functions | |
| |
| |
| Objects on the Draw Layer | |
| |
| |
| Customization in Excel | |
| |
| |
| Analysis Tools | |
| |
| |
| Protection Options | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Using Names | |
| |
| |
| What's in a Name? | |
| |
| |
| Methods for Creating Cell and Range Names | |
| |
| |
| Creating Multisheet Names | |
| |
| |
| A Name's Scope | |
| |
| |
| 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 | |
| |
| |
| Using the Indirect function to create a named range with a fixed address | |
| |
| |
| Using arrays in named formulas | |
| |
| |
| Creating a dynamic named formula | |
| |
| |
| |
| Using Functions in Your Formulas | |
| |
| |
| |
| Introducing Worksheet Functions | |
| |
| |
| What Is a Function? | |
| |
| |
| Function Argument Types | |
| |
| |
| Ways to Enter a Function into a Formula | |
| |
| |
| Function Categories | |
| |
| |
| |
| Manipulating Text | |
| |
| |
| A Few Words About Text | |
| |
| |
| Text Functions | |
| |
| |
| Advanced Text Formulas | |
| |
| |
| Custom VBA Text Functions | |
| |
| |
| |
| Working with Dates and Times | |
| |
| |
| How Excel Handles Dates and Times | |
| |
| |
| Date-Related Functions | |
| |
| |
| Time-Related Functions | |
| |
| |
| |
| Counting 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 | |
| |
| |
| Using VBA Functions to Count and Sum | |
| |
| |
| |
| Lookups | |
| |
| |
| What Is a Lookup Formula? | |
| |
| |
| Functions Relevant to Lookups | |
| |
| |
| Basic Lookup Formulas | |
| |
| |
| Specialized Lookup Formulas | |
| |
| |
| |
| Databases and Lists | |
| |
| |
| Worksheet Lists or Databases | |
| |
| |
| Using AutoFilter | |
| |
| |
| Using Advanced Filtering | |
| |
| |
| Specifying Advanced Filter Criteria | |
| |
| |
| Using Database Functions with Lists | |
| |
| |
| Creating Subtotals | |
| |
| |
| |
| Financial Calculations | |
| |
| |
| The Time Value of Money | |
| |
| |
| Loan Calculations | |
| |
| |
| Investment Calculations | |
| |
| |
| Depreciation Calculations | |
| |
| |
| |
| Miscellaneous Calculations | |
| |
| |
| Unit Conversions | |
| |
| |
| Solving Right Triangles | |
| |
| |
| Area, Surface, Circumference, and Volume Calculations | |
| |
| |
| Solving Simultaneous Equations | |
| |
| |
| Rounding Numbers | |
| |
| |
| |
| Array Formulas | |
| |
| |
| |
| Introducing Arrays | |
| |
| |
| Array Formulas | |
| |
| |
| Dimensions of an Array | |
| |
| |
| Naming Array Constants | |
| |
| |
| Working with Array Formulas | |
| |
| |
| Multicell Array Formulas | |
| |
| |
| Single-Cell Array Formulas | |
| |
| |
| |
| Performing Magic with Array Formulas | |
| |
| |
| More Single-Cell Array Formulas | |
| |
| |
| More Multicell Array Formulas | |
| |
| |
| Returning an Array from a Custom VBA Function | |
| |
| |
| |
| Miscellaneous Formula Techniques | |
| |
| |
| |
| Intentional Circular References | |
| |
| |
| Understanding Circular References | |
| |
| |
| Intentional Circular References | |
| |
| |
| How Excel Determines Calculation and Iteration Settings | |
| |
| |
| Circular Reference Examples | |
| |
| |
| Potential Problems with Intentional Circular References | |
| |
| |
| |
| Charting Techniques | |
| |
| |
| Representing Data in Charts | |
| |
| |
| Plotting Data Interactively | |
| |
| |
| Creating Awesome Designs | |
| |
| |
| Working with Trendlines | |
| |
| |
| Useful Chart Tricks | |
| |
| |
| |
| Pivot Tables | |
| |
| |
| About Pivot Tables | |
| |
| |
| Creating a Pivot Table | |
| |
| |
| Grouping Pivot Table Items | |
| |
| |
| Creating a Calculated Field or Calculated Item | |
| |
| |
| |
| Conditional Formatting and Data Validation | |
| |
| |
| Conditional Formatting | |
| |
| |
| Data Validation | |
| |
| |
| |
| Creating Megaformulas | |
| |
| |
| What Is a Megaformula? | |
| |
| |
| Creating a Megaformula: A Simple Example | |
| |
| |
| Megaformula Examples | |
| |
| |
| The Pros and Cons of Megaformulas | |
| |
| |
| |
| Tools and Methods for Debugging Formulas | |
| |
| |
| Formula Debugging? | |
| |
| |
| Formula Problems and Solutions | |
| |
| |
| Excel's Auditing Tools | |
| |
| |
| Third-Party Auditing Tools | |
| |
| |
| |
| Developing Custom Worksheet Functions | |
| |
| |
| |
| Introducing VBA | |
| |
| |
| About VBA | |
| |
| |
| Introducing the Visual Basic Editor | |
| |
| |
| |
| Function Procedure Basics | |
| |
| |
| Why Create Custom Functions? | |
| |
| |
| An Introductory VBA Function Example | |
| |
| |
| About Function Procedures | |
| |
| |
| Using the Paste Function Dialog Box | |
| |
| |
| Testing and Debugging Functions | |
| |
| |
| Creating Add-Ins | |
| |
| |
| |
| VBA Programming Concepts | |
| |
| |
| An Introductory Example Function Procedure | |
| |
| |
| Using Comments | |
| |
| |
| Using Variables, Data Types, and Constants | |
| |
| |
| Using Assignment Expressions | |
| |
| |
| Using Arrays | |
| |
| |
| Using VBA's Built-in Functions | |
| |
| |
| Controlling Execution | |
| |
| |
| Using Ranges | |
| |
| |
| |
| VBA Function Examples | |
| |
| |
| Simple Functions | |
| |
| |
| Determining a Cell's Data Type | |
| |
| |
| A Multifunctional Function | |
| |
| |
| Generating Random Numbers | |
| |
| |
| Calculating Sales Commissions | |
| |
| |
| Text Manipulation Functions | |
| |
| |
| Counting and Summing Functions | |
| |
| |
| Date Functions | |
| |
| |
| Returning the Last Nonempty Cell in a Column or Row | |
| |
| |
| Multisheet Functions | |
| |
| |
| Advanced Function Techniques | |
| |
| |
| Using Windows API Functions | |
| |
| |
| Appendixes | |
| |
| |
| |
| Working with Imported 1-2-3 Files | |
| |
| |
| |
| Excel's Function Reference | |
| |
| |
| |
| Custom Number Formats | |
| |
| |
| |
| Additional Excel Resources | |
| |
| |
| |
| What's on the CD-ROM? | |
| |
| |
| Index | |
| |
| |
| End-User License Agreement | |
| |
| |
| CD-ROM Installation Instructions | |