- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Chapter 32: Making Your Worksheets Error-Free
Using Excel Auditing Tools
Excel includes a number of tools that can help you track down formula errors. This section describes the auditing tools built in to Excel.
Identifying cells of a particular type
The Go to Special dialog box (as shown in Figure 32.6) is a handy tool that enables you to locate cells of a particular type. To display this dialog box, choose Home Editing Find & Select Go to Special.
Note
If you select a multicell range before displaying the Go to Special dialog box, the command operates only within the selected cells. If a single cell is selected, the command operates on the entire worksheet. n
FIGURE 32.6
The Go to Special dialog box.
You can use the Go to Special dialog box to select cells of a certain type, which can often help you identify errors. For example, if you choose the Formulas option, Excel selects all the cells that contain a formula. If you zoom the worksheet out to a small size, you can get a good idea of the worksheet’s organization (see Figure 32.7). To zoom a worksheet, use the zoom controls on the right side of the status bar. Or, press Ctrl while you move the scroll wheel on your mouse.
661
Part IV: Using Advanced Excel Features
FIGURE 32.7
Zooming out and selecting all formula cells can give you a good overview of how the worksheet is designed.
Tip
Selecting the formula cells may also help you spot a common error: namely, a formula that has been replaced accidentally with a value. If you find a cell that’s not selected amid a group of selected formula cells, chances are good that the cell previously contained a formula that has been replaced by a value. n
Viewing formulas
You can become familiar with an unfamiliar workbook by displaying the formulas rather than the results of the formulas. To toggle the display of formulas, choose Formulas Formula Auditing Show Formulas. You may want to create a second window for the workbook before issuing this command. This way, you can see the formulas in one window and the results of the formula in the other window. Choose View Window New Window to open a new window.
Tip
You can also press Ctrl+` (the accent grave key, typically located above the Tab key) to toggle between Formula view and Normal view. n
662
Chapter 32: Making Your Worksheets Error-Free
Figure 32.8 shows an example of a worksheet displayed in two windows. The window on the top shows Normal view (formula results), and the window on the bottom displays the formulas. Choosing View Window View Side by Side, which allows synchronized scrolling, is also useful for viewing two windows. (See Chapter 4 for more information about this command.)
FIGURE 32.8
Displaying formulas (bottom window) and their results (top window).
Tracing cell relationships
To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts:
•Cell precedents: Applicable only to cells that contain a formula, a formula cell’s precedents are all the cells that contribute to the formula’s result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn’t used directly in the formula but is used by a cell that you refer to in the formula.
•Cell dependents: These formula cells depend upon a particular cell. A cell’s dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent.
For example, consider this simple formula entered into cell A4:
=SUM(A1:A3)
663
Part IV: Using Advanced Excel Features
Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 each have a dependent cell (cell A4), and they’re all direct dependents.
Identifying cell precedents for a formula cell often sheds light on why the formula isn’t working correctly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For example, if you’re about to delete a formula, you may want to check whether it has any dependents.
Identifying precedents
You can identify cells used by a formula in the active cell in a number of ways:
•Press F2. The cells that are used directly by the formula are outlined in color, and the color corresponds to the cell reference in the formula. This technique is limited to identifying cells on the same sheet as the formula.
•Display the Go to Special dialog box. (Choose Home Editing Find & Select Go to Special.) Select the Precedents option and then select either Direct Only (for direct precedents only) or All Levels (for direct and indirect precedents). Click OK, and Excel selects the precedent cells for the formula. This technique is limited to identifying cells on the same sheet as the formula.
•Press Ctrl+[. This selects all direct precedent cells on the active sheet.
•Press Ctrl+Shift+{. This selects all precedent cells (direct and indirect) on the active sheet.
•Choose Formulas Formula Auditing Trace Precedents. Excel will draw arrows to indicate the cell’s precedents. Click this button multiple times to see additional levels of precedents. Choose Formulas Formula Auditing Remove Arrows to hide the arrows. Figure 32.9 shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C13.
FIGURE 32.9
This worksheet displays arrows that indicate cell precedents for the formula in cell C13.
664
Chapter 32: Making Your Worksheets Error-Free
Identifying dependents
You can identify formula cells that use a particular cell in a number of ways:
•Display the Go to Special dialog box. Select the Dependents option and then select either Direct Only (for direct dependents only) or All Levels (for direct and indirect dependents). Click OK. Excel selects the cells that depend upon the active cell. This technique is limited to identifying cells on the active sheet only.
•Press Ctrl+]. This selects all direct dependent cells on the active sheet.
•Press Ctrl+Shift+}. This selects all dependent cells (direct and indirect) on the active sheet.
•Choose Formulas Formula Auditing Trace Dependents. Excel will draw arrows to indicate the cell’s dependents. Click this button multiple times to see additional levels of dependents. Choose Formulas Formula Auditing Remove Arrows to hide the arrows.
Tracing error values
If a formula displays an error value, Excel can help you identify the cell that is causing that error value. An error in one cell is often the result of an error in a precedent cell. Activate a cell that contains an error value and then choose Formulas Formula Auditing Error Checking Trace Error. Excel draws arrows to indicate the error source.
Fixing circular reference errors
If you accidentally create a circular reference formula, Excel displays a warning message — Circular Reference — with the cell address, in the status bar, and also draws arrows on the worksheet to help you identify the problem. If you can’t figure out the source of the problem, choose Formulas Formula Auditing Error Checking Circular References. This command displays a list of all cells that are involved in the circular references. Start by selecting the first cell listed and then work your way down the list until you figure out the problem.
Using background error-checking feature
Some people may find it helpful to take advantage of the Excel automatic error-checking feature. This feature is enabled or disabled via the Enable Background Error Checking check box, found on the Formulas tab of the Excel Options dialog box, shown in Figure 32.10. In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check.
When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a Smart Tag appears. Clicking this Smart Tag provides you with options.
Figure 32.11 shows the options that appear when you click the Smart Tag in a cell that contains a #DIV/0! error. The options vary, depending on the type of error.
665
Part IV: Using Advanced Excel Features
FIGURE 32.10
Excel can check your formulas for potential errors.
FIGURE 32.11
After you click an error, Smart Tag gives you a list of options.
In many cases, you will choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored errors can be reset so that they appear again. (Use the Reset Ignored Errors button in the Formulas tab of the Excel Options dialog box.)
666
Chapter 32: Making Your Worksheets Error-Free
You can choose Formulas Formula Auditing Error Checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. Figure 32.12 shows the Error Checking dialog box. This dialog box is modeless: that is, you can still access your worksheet when the Error Checking dialog box is displayed.
Caution
The error-checking feature isn’t perfect. In fact, it’s not even close to perfect. In other words, you can’t assume that you have an error-free worksheet simply because Excel doesn’t identify any potential errors! Also, be aware that this error-checking feature won’t catch a very common type of error: namely, overwriting a formula cell with a value. n
FIGURE 32.12
Use the Error Checking dialog box to cycle through potential errors identified by Excel.
Using the Excel Formula Evaluator
Formula Evaluator lets you see the various parts of a nested formula evaluated in the order that the formula is calculated. To use Formula Evaluator, select the cell that contains the formula and then choose Formula Formula Auditing Evaluate Formula to display the Evaluate Formula dialog box (see Figure 32.13).
FIGURE 32.13
The Evaluate Formula dialog box shows a formula being calculated one step at a time.
667