Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Identifying and correcting common formula errors

2. Using Excel auditing tools

3. Using formula AutoCorrect

4. Tracing cell relationships

5. Checking spelling and related features

It goes without saying that you want your Excel worksheets to produce accurate results. Unfortunately, it's not always easy to be certain that the results are correct, especially if you deal with large, complex worksheets. This chapter introduces the tools and techniques available to help identify, correct, and prevent errors.

Finding and Correcting Formula Errors

Making a change in a worksheet — even a relatively minor change — may produce a ripple effect that introduces errors in other cells. For example, accidentally entering a value into a cell that previously held a formula is all too easy to do. This simple error can have a major impact on other formulas, and you may not discover the problem until long after you make the change — if you discover the problem at all.

Formula errors tend to fall into one of the following general categories:

· Syntax errors: You have a problem with the syntax of a formula. For example, a formula may have mismatched parentheses, or a function may not have the correct number of arguments.

· Logical errors: A formula doesn't return an error, but it contains a logical flaw that causes it to return an incorrect result.

· Incorrect reference errors: The logic of the formula is correct, but the formula uses an incorrect cell reference. As a simple example, the range reference in a Sum formula may not include all the data that you want to sum.

· Semantic errors: An example is a function name that is spelled incorrectly. Excel will attempt to interpret it as a name and will display the #NAME? error.

· Circular references: A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Circular references are useful in a few cases, but most of the time, a circular reference indicates a problem.

· Array formula entry error: When entering (or editing) an array formula, you must press Ctrl+Shift+Enter to enter the formula. If you fail to do so, Excel doesn't recognize the formula as an array formula, and you may get an error or incorrect results.

Refer to Chapter 17, “Introducing Array Formulas,” for an introduction to array formulas.

· Incomplete calculation errors: The formulas simply aren't calculated fully. To ensure that your formulas are fully calculated, press Ctrl+Alt+Shift+F9.

Syntax errors are usually the easiest to identify and correct. In most cases, you'll know when your formula contains a syntax error. For example, Excel won't permit you to enter a formula with mismatched parentheses. Other syntax errors also usually result in an error display in the cell.

The following sections describe common formula problems and offer advice on identifying and correcting them.

Mismatched parentheses

In a formula, every left parenthesis must have a corresponding right parenthesis. If your formula has mismatched parentheses, Excel usually won't permit you to enter it. An exception to this rule involves a simple formula that uses a function. For example, if you enter the following formula (which is missing a closing parenthesis), Excel accepts the formula and provides the missing parenthesis:

=SUM(A1:A500

A formula may have an equal number of left and right parentheses, but the parentheses may not match properly. For example, consider the following formula, which converts a text string such that the first character is uppercase and the remaining characters are lowercase. This formula has five pairs of parentheses, and they match properly:

=UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1)

The following formula also has five pairs of parentheses, but they're mismatched. The result displays a syntactically correct formula that simply returns the wrong result:

=UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1))

Often, parentheses that are in the wrong location will result in a syntax error, which is usually a message that tells you that you entered too many or too few arguments for a function.

Tip

Excel can help you with mismatched parentheses. When you're editing a formula and you move the cursor over a parenthesis, Excel displays it (and its matching parenthesis) in bold for about one-half second. In addition, Excel color-codes pairs of nested parentheses while you're editing a formula.

Using Formula AutoCorrect

When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction. The accompanying figure shows an example of a proposed correction.

Be careful when accepting corrections for your formulas from Excel because it doesn't always guess correctly. For example, I entered the following formula (which has mismatched parentheses):

=AVERAGE(SUM(A1:A12,SUM(B1:B12))

Excel then proposed the following correction to the formula:

=AVERAGE(SUM(A1:A12,SUM(B1:B12)))

You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula is syntactically correct, but it's not what I intended. The correct formula is

=AVERAGE(SUM(A1:A12),SUM(B1:B12))

Cells are filled with hash marks

A cell is filled with a series of hash marks (#) for one of two reasons:

· The column is not wide enough to accommodate the formatted numeric value. To correct it, you can make the column wider or use a different number format. (See Chapter 25, “Using Custom Number Formats.”)

· The cell contains a formula that returns an invalid date or time. For example, Excel doesn't support dates prior to 1900 or the use of negative time values. A formula that returns either of these values results in a cell filled with hash marks. Widening the column won't fix it.

Blank cells are not blank

Some Excel users have discovered that by pressing the spacebar, the contents of a cell seem to erase. Actually, pressing the spacebar inserts an invisible space character, which isn't the same as erasing the cell.

For example, the following formula returns the number of nonempty cells in range A1:A10. If you “erase” any of these cells by using the spacebar, these cells are included in the count, and the formula returns an incorrect result:

=COUNTA(A1:A10)

If your formula doesn't ignore blank cells the way that it should, check to make sure that the blank cells are really blank. Here's how to search for cells that contain only blank characters:

1. Press Ctrl+F. The Find and Replace dialog box appears.

2. Click the Options button to expand the dialog box so it displays additional options.

3. In the Find What box, enter * *. That's an asterisk, followed by a space, followed by another asterisk.

4. Make sure the Match Entire Cell Contents check box is selected.

5. Click Find All. If any cells that contain only space characters are found, Excel lists the cell address at the bottom of the Find and Replace dialog box.

Extra space characters

If you have formulas or use procedures that rely on comparing text, be careful that your text doesn't contain additional space characters. Adding an extra space character is particularly common when data has been imported from another source.

Excel automatically removes trailing spaces from values that you enter, but trailing spaces in text entries are not deleted. It's impossible to tell just by looking at a cell whether it contains one or more trailing space characters.

The TRIM function removes leading spaces, trailing spaces, and multiple spaces within a text string. Figure 31.1 shows some text in column A. The formula in B1, which was copied down the column, is

=TRIM(A1)=A1

Figure 31.1 Using a formula to identify cells that contain extra space characters.

This formula returns FALSE if the text in column A contains leading spaces, trailing spaces, or multiple spaces. In this case, the words Penguin and Chicken each contain a trailing space.

Formulas returning an error

A formula may return any of the following error values:

· #DIV/0!

· #N/A

· #NAME?

· #NULL!

· #NUM!

· #REF!

· #VALUE!

The following sections summarize possible problems that may cause these errors.

Tip

Excel allows you to choose the way error values are printed. To access this feature, display the Page Setup dialog box and select the Sheet tab. You can choose to print error values as displayed (the default) or as blank cells, dashes, or #N/A. To display the Page Setup dialog box, click the dialog box launcher of the Page Layout Page Setup group.

Tracing Error Values

Often, an error in one cell is the result of an error in a precedent cell. For help in identifying the cell causing an error value to appear, activate the cell that contains the error and then choose Formulas Formula Auditing Error Checking Trace Error. Excel draws arrows to indicate which cell is the source of the error.

After you identify the error, choose Formulas Formula Auditing Remove Arrows to get rid of the arrow display.

#DIV/0! errors

Division by zero is not a valid operation. If you create a formula that attempts to divide by zero, Excel displays its familiar #DIV/0! error value.

Because Excel considers a blank cell to be zero, you also get this error if your formula divides by a missing value. This problem is common when you create formulas for data that you haven't entered yet, as shown in Figure 31.2. The formula in cell D4, which was copied to the cells below it, is

=C4/B4

Figure 31.2 #DIV/0! errors occur when the data in column B is missing.

This formula calculates the ratio of the values in columns C and B. Data isn't available for all days, so the formula returns a #DIV/0! error.

To avoid the error display, you can use an IF function to check for a blank cell in column B:

=IF(B4=0,"",C4/B4)

This formula displays an empty string if cell B4 is blank or contains 0; otherwise, it displays the calculated value.

Another approach is to use an IFERROR function to check for any error condition. The ­following formula, for example, displays an empty string if the formula results in any type of error:

=IFERROR(C4/B4,"")

Note

The IFERROR function was introduced in Excel 2007. For compatibility with previous versions of Excel, use this formula:

=IF(ISERROR(C4/B4),"",C4/B4)

#N/A errors

The #N/A error occurs if any cell referenced by a formula displays #N/A.

Note

Some users like to use =NA() or #N/A explicitly for missing data. This method makes it perfectly clear that the data is not available and hasn't been deleted accidentally.

The #N/A error also occurs when a LOOKUP function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP) can't find a match.

If you would like to display an empty string instead of #N/A, use the IFNA function in a formula like this:

=IFNA(VLOOKUP(A1,C1:F50,4,FALSE),"")

Note

The IFNA function was introduced in Excel 2013. For compatibility with previous versions, use a formula like this:

=IF(ISNA(VLOOKUP(A1,C1:F50,4,FALSE)),"",VLOOKUP(A1,C1:F50,4,FALSE))

#NAME? errors

The #NAME? error occurs under these conditions:

· The formula contains an undefined range or cell name.

· The formula contains text that Excel interprets as an undefined name. A misspelled function name, for example, generates a #NAME? error.

· The formula contains text that isn't enclosed in quotation marks.

· The formula contains a range reference that omits the colon between the cell addresses.

· The formula uses a worksheet function that's defined in an add-in, and the add-in is not installed.

Caution

Excel has a bit of a problem with range names. If you delete a name for a cell or a range and the name is used in a formula, the formula continues to use the name, even though it's no longer defined. As a result, the formula displays #NAME?. You might expect Excel to automatically convert the names to their corresponding cell references, but this doesn't happen.

#NULL! errors

A #NULL! error occurs when a formula attempts to use an intersection of two ranges that don't actually intersect. Excel's intersection operator is a space. The following formula, for example, returns #NULL! because the two ranges don't intersect:

=SUM(B5:B14 A16:F16)

The following formula doesn't return #NULL! but displays the contents of cell B9, which represents the intersection of the two ranges:

=SUM(B5:B14 A9:F9)

You also see a #NULL! error if you accidentally omit an operator in a formula. For example, this formula is missing the second operator:

= A1+A2 A3

#NUM! errors

A formula returns a #NUM! error if any of the following occurs:

· You pass a nonnumeric argument to a function when a numeric argument is expected (for example, $1,000 instead of 1000).

· You pass an invalid argument to a function, such as attempting to calculate the square root of a negative number. This formula returns #NUM!:

=SQRT(-12)

· A function that uses iteration can't calculate a result. Examples of functions that use iteration are IRR and RATE.

· A formula returns a value that is too large or too small. Excel supports values between –1E-307 and 1E+307.

#REF! errors

A #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the following situations:

· You delete the row column of a cell that is referenced by the formula. For example, the following formula displays a #REF! error if row 1, column A, or column B is deleted:

=A1/B1

· You delete the worksheet of a cell that is referenced by the formula. For example, the following formula displays a #REF! error if Sheet2 is deleted:

=Sheet2!A1

· You copy a formula to a location that invalidates the relative cell references. For example, if you copy the following formula from cell A2 to cell A1, the formula returns #REF! because it attempts to refer to a nonexistent cell:

=A1-1

· You cut a cell (choose Home Clipboard Cut) and then paste it to a cell that's referenced by a formula. The formula will display #REF!.

#VALUE! errors

A #VALUE! error is common and can occur under the following conditions:

· An argument for a function is of an incorrect data type, or the formula attempts to perform an operation using incorrect data. For example, a formula that adds a value to a text string returns the #VALUE! error.

· A function's argument is a range when it should be a single value.

· A custom worksheet function is not calculated. You can press Ctrl+Alt+F9 to force a recalculation.

· A custom worksheet function attempts to perform an operation that is not valid. For example, custom functions can't modify the Excel environment or make changes to other cells.

· You forget to press Ctrl+Shift+Enter when entering an Array formula.

Pay Attention to the Colors

When you edit a cell that contains a formula, Excel color-codes the cell and range references in the formula. Excel also outlines the cells and ranges used in the formula by using corresponding colors. Therefore, you can see at a glance the cells that are used in the formula.

You also can manipulate the colored outline to change the cell or range reference. To change the references used in a formula, drag the outline's border or fill handle (at the lower right of the outline). This technique is often easier than editing the formula.

Absolute/relative reference problems

As I describe in Chapter 10, “Introducing Formulas and Functions,” a cell reference can be relative (for example, A1), absolute (for example, $A$1), or mixed (for example, $A1 or A$1). The type of cell reference that you use in a formula is relevant only if the formula will be copied to other cells.

A common problem is using a relative reference when you should use an absolute reference. As shown in Figure 31.3, cell C1 contains a tax rate, which is used in the formulas in column C. The formula in cell C4 is

=B4+(B4*$C$1)

Figure 31.3 Formulas in the range C4:C7 use an absolute reference to cell C1.

Notice that the reference to cell C1 is an absolute reference. When the formula is copied to other cells in column C, the formula continues to refer to cell C1. If the reference to cell C1 were a relative reference, the copied formulas would return an incorrect result.

Operator precedence problems

As I note in Chapter 10, Excel has some straightforward rules about the order in which mathematical operations are performed. When in doubt (or when you simply need to clarify your intentions), you should use parentheses to ensure that operations are performed in the correct order. For example, the following formula multiplies A1 by A2 and then adds 1 to the result. The multiplication is performed first because it has a higher order of precedence:

=1+A1*A2

The following is a clearer version of this formula. The parentheses aren't necessary, but in this case, the order of operations is perfectly obvious:

=1+(A1*A2)

Notice that the negation operator symbol is the same as the subtraction operator symbol. This, as you may expect, can cause some confusion. Consider these two formulas:

=-3^2

=0-3^2

The first formula, as expected, returns 9. The second formula, however, returns –9. Squaring a number always produces a positive result, so how is it that Excel can return the –9 result?

In the first formula, the minus sign is a negation operator and has the highest precedence. However, in the second formula, the minus sign is a subtraction operator, which has a lower precedence than the exponentiation operator. Therefore, the value 3 is squared, and then the result is subtracted from 0 (zero), which produces a negative result.

Using parentheses, as shown in the following formula, causes Excel to interpret the operator as a minus sign rather than a negation operator. This formula returns –9:

=-(3^2)

Formulas are not calculated

If you use custom worksheet functions written in VBA, you may find that formulas that use these functions fail to be recalculated and may display incorrect results. For example, assume that you wrote a VBA function that returns the number format of a referenced cell. If you change the number format, the function will continue to display the previous number format. That's because changing a number format doesn't trigger a recalculation.

To force a single formula to be recalculated, select the cell, press F2, and then press Enter. To force a recalculation of all formulas, press Ctrl+Alt+F9.

Actual versus displayed values

You may encounter a situation in which values in a range don't appear to add up properly. For example, Figure 31.4 shows a worksheet with the following formula entered into each cell in the range B2:B4:

=1/3

Figure 31.4 A simple demonstration of numbers that appear to add up incorrectly.

Cell B5 contains the following formula:

=SUM(B2:B4)

All the cells are formatted to display with two decimal places. As you can see, the formula in cell B5 appears to display an incorrect result. (You may expect it to display 0.99.) The formula, of course, does return the correct result. The formula uses the actual values in the range B2:B4, not the displayed values.

You can instruct Excel to use the displayed values by selecting the Set Precision as Displayed check box of the Advanced section of the Excel Options dialog box. (Choose File Options to display this dialog box.)

Caution

Be careful with the Set Precision as Displayed option. This option also affects normal values (nonformulas) that have been entered into cells. For example, if a cell contains the value 4.68 and is displayed with no decimal places (that is, 5), selecting the Precision as Displayed check box converts 4.68 to 5.00. This change is permanent, and you can't restore the original value if you later clear the Set Precision as Displayed check box. A better approach is to use the ROUND function to round off the values to the desired number of decimal places.

Floating point number errors

Computers, by their nature, don't have infinite precision. Excel stores numbers in binary format by using 8 bytes, which can handle numbers with 15-digit accuracy. Some numbers can't be expressed precisely by using 8 bytes, so the number is stored as an approximation.

To demonstrate how this lack of precision may cause problems, enter the following formula into cell A1:

=(5.1-5.2)+1

The result should be 0.9. However, if you format the cell to display 15 decimal places, you discover that Excel calculates the formula with a result of 0.899999999999999. This result occurs because the operation in parentheses is performed first, and this intermediate result stores in binary format by using an approximation. The formula then adds 1 to this value, and the approximation error is propagated to the final result.

In many cases, this type of error doesn't present a problem. However, if you need to test the result of that formula by using a logical operator, it may present a problem. For example, the following formula (which assumes that the previous formula is in cell A1) returns FALSE:

=A1=.9

One solution to this type of error is to use the ROUND function. The following formula, for example, returns TRUE because the comparison is made by using the value in A1 rounded to one decimal place:

=ROUND(A1,1)=0.9

Here's another example of a “precision” problem. Try entering the following formula:

=(1.333-1.233)-(1.334-1.234)

This formula should return 0, but it actually returns –2.220446E-16 (a number very close to zero).

If that formula is in cell A1, the following formula returns Not Zero:

=IF(A1=0,"Zero","Not Zero")

One way to handle these “very close to zero” rounding errors is to use a formula like this:

=IF(ABS(A1)<1E-6,"Zero","Not Zero")

This formula uses the less-than operator (<) to compare the absolute value of the number with a very small number. This formula returns Zero.

“Phantom link” errors

You may open a workbook and see a message like the one shown in Figure 31.5. This message sometimes appears even when a workbook contains no linked formulas. Often, these phantom links are created when you copy a worksheet that contains names.

Figure 31.5 Excel's way of asking whether you want to update links in a workbook.

First, try choosing File Info Edit Links to Files to display the Edit Links dialog box. Then select each link and click Break Link. If that doesn't solve the problem, this phantom link may be caused by an erroneous name. Choose Formulas Defined Names Name Manager and scroll through the list of names in the Name Manager dialog box. If you see a name that refers to #REF!, delete the name. The Name Manager dialog box has a Filter button that lets you filter the names. For example, you can filter the lists to display only the names with errors.

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 (shown in Figure 31.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.

Figure 31.6 The Go to Special dialog box.

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.

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 31.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.

Figure 31.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.

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.

Figure 31.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.

Figure 31.8 Displaying formulas (bottom window) and their results (top window).

See Chapter 4, “Working with Cells and Ranges,” for more information about this command.

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)

Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 all have at least one dependent cell (cell A4).

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.

· Choose Home Editing Find & Select Go to Special to display the Go to Special dialog box. 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 31.9 shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C13.

Figure 31.9 This worksheet displays arrows that indicate cell precedents for the formula in cell C13.

Identifying dependents

You can identify formula cells that use a particular cell in a number of ways:

· Choose Home Editing Find & Select Go to Special to 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 on 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. It 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 the 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 31.10. In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check.

Figure 31.10 Excel can check your formulas for potential errors.

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 drop-down control appears. Clicking this drop-down control provides you with options. Figure 31.11 shows the options that appear when you click the drop-down control in a cell that contains a #DIV/0! error. The options vary, depending on the type of error.

Figure 31.11 After you click an error, a drop-down control gives you a list of options.

In many cases, you'll 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 on the Formulas tab of the Excel Options dialog box.)

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 31.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.

Figure 31.12 Use the Error Checking dialog box to cycle through potential errors identified by Excel.

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 potential errors! Also, be aware that this error-checking feature won't catch a common type of error, namely, overwriting a formula cell with a value.

Using Formula Evaluator

Formula Evaluator lets you see the various parts of a nested formula evaluated in the order in which 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 31.13).

Figure 31.13 The Evaluate Formula dialog box shows a formula being calculated one step at a time.

Click the Evaluate button to show the result of calculating the expressions within the formula. Each click of the button performs another calculation. This feature may seem a bit complicated at first, but if you spend some time working with it, you'll understand how it works and see the value.

Excel provides another way to evaluate a part of a formula:

1. Select the cell that contains the formula.

2. Press F2 to get into Cell Edit mode.

3. Use your mouse to highlight the portion of the formula you want to evaluate. Or press Shift and use the navigation keys.

4. Press F9.

The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc to cancel and return your formula to its previous state.

Caution

Be careful when using this technique because if you press Enter (rather than Esc), the formula will be modified to use the calculated values.

Searching and Replacing

Excel has a powerful search-and-replace feature that makes it easy to locate information in a worksheet or across multiple worksheets in a workbook. As an option, you can also search for text and replace it with other text.

To access the Find and Replace dialog box, start by selecting the range that you want to search. If you select any single cell, Excel searches the entire sheet. Choose Home Editing Find & Select Find (or press Ctrl+F). You'll see the Find and Replace dialog box, shown in Figure 31.14. If you're simply looking for information in the worksheet, select the Find tab. If you want to replace existing text with new text, use the Replace tab. Also note that you can use the Options button to display (or hide) additional options. The dialog box shown in the figure displays these additional options.

Figure 31.14 Use the Find and Replace dialog box to locate information in a worksheet or workbook.

Searching for information

Enter the information to search for in the Find What text box and then specify any of the following options:

· Within drop-down list: Specify where to search (the current sheet or the entire workbook).

· Search drop-down list: Specify the direction (by rows or by columns).

· Look In drop-down list: Specify what cell parts to search (formulas, values, or comments).

· Match Case check box: Specify whether the search should be case sensitive.

· Match Entire Cell Contents check box: Specify whether the entire cell contents must be matched.

· Format button: Click to search for cells that have a particular formatting. (See the upcoming “Searching for formatting” section.)

Click Find Next to locate the matching cells one at a time or click Find All to locate all matches. If you use the Find All button, the Find and Replace dialog box expands to display the addresses of all matching cells in a list (see Figure 31.15). When you select an entry in this list, Excel scrolls the worksheet so that you can view it in context.

Figure 31.15 Displaying the result of a search in the Find and Replace dialog box.

Tip

After using Find All, press Ctrl+A to select all the found cells in the worksheet.

Note

Because the Find and Replace dialog box is modeless, you can access the worksheet and make changes without the need to dismiss the dialog box.

Replacing information

To replace text with other text, use the Replace tab in the Find and Replace dialog box. Enter the text to be replaced in the Find What field, and then enter the new text in the Replace With field. Specify other options as described in the previous section.

Click Find Next to locate the first matching item and then click Replace to do the replacement. When you click the Replace button, Excel locates the next matching item. To override the replacement, click Find Next. To replace all items without verification, click Replace All. If the replacement didn't occur as you planned, you can use the Undo button on the Quick Access toolbar (or press Ctrl+Z).

Tip

To delete information, enter the text to be deleted in the Find What field but leave the Replace With field empty.

Searching for formatting

From the Find and Replace dialog box, you can also locate cells that contain a particular type of formatting. As an option, you can replace that formatting with another type of formatting. For example, assume that you want to locate all cells that are formatted as bold and then change that formatting to bold and italic. Follow these steps:

1. Choose Home Editing Find & Select Replace or press Ctrl+H. The Find and Replace dialog box appears.

2. Make sure that the Replace tab is displayed. If necessary, click the Options button to expand the dialog box.

3. If the Find What and Replace With fields are not empty, delete their contents.

4. Click the top Format button. The Find Format dialog box appears. This dialog box resembles the standard Format Cells dialog box.

5. Select the Font tab.

6. Select Bold in the Font Style list, and then click OK.

7. Click the bottom Format button. The Replace Format dialog box appears.

8. Select the Font tab.

9. Select Bold Italic from the Font Style list, and then click OK. At this point, the Find and Replace dialog box resembles Figure 31.16. Notice that it displays previews of the formatting that will be found and replaced.

Figure 31.16 Use the Find and Replace dialog box to change formatting.

10.In the Find and Replace dialog box, click Replace All. Excel locates all cells that have bold formatting and changes the formatting to bold italic.

You can also find formatting based on a particular cell. In the Find Format dialog box, click the Choose Format from Cell button and then click the cell that contains the formatting you're looking for.

Caution

The Find and Replace dialog box can't find background color formatting in tables that was applied using table styles or formatting that is applied based on conditional formatting.

Spell-Checking Your Worksheets

If you use a word-processing program, you probably take advantage of its spell-checker feature. Spelling mistakes can be just as embarrassing when they appear in a spreadsheet. Fortunately, Microsoft includes a spell checker with Excel.

To access the spell checker, choose Review Proofing Spelling, or press F7. To check the spelling in just a particular range, select the range before you activate the spell checker.

If the spell checker finds any words it doesn't recognize as correct, the Spelling dialog box, shown in Figure 31.17, appears.

Figure 31.17 Use the Spelling dialog box to locate and correct spelling errors in your worksheets.

Note

The spell checker checks cell contents, text in graphics objects and charts, and page headers and footers. Even the contents of hidden rows and columns are checked.

The Spelling dialog box works similarly to other spell checkers with which you may be familiar. If Excel encounters a word that isn't in the current dictionary or that is misspelled, it offers a list of suggestions. You can respond by clicking one of these buttons:

· Ignore Once: Ignore the word and continue the spell check.

· Ignore All: Ignore the word and all subsequent occurrences of it.

· Add to Dictionary: Add the word to the dictionary.

· Change: Change the word to the selected word in the Suggestions list.

· Change All: Change the word to the selected word in the Suggestions list and change all subsequent occurrences of it without asking.

· AutoCorrect: Add the misspelled word and its correct spelling (which you select from the list) to the AutoCorrect list.

Using AutoCorrect

AutoCorrect is a handy feature that automatically corrects common typing mistakes. You can also add words to the list that Excel corrects automatically. The AutoCorrect dialog box appears in Figure 31.18. To access this feature, choose File Options. In the Excel Options dialog box, select the Proofing tab and then click the AutoCorrect Options button.

Figure 31.18 Use the AutoCorrect dialog box to control the spelling corrections Excel makes automatically.

This dialog box has several options:

· Correct TWo INitial CApitals: Automatically corrects words with two initial uppercase letters. For example, BUdget is converted to Budget. This mistake is common among fast typists. You can click the Exceptions button to specify a list of exceptions to this rule.

· Capitalize First Letter of Sentences: Capitalizes the first letter in a sentence. All other letters are unchanged.

· Capitalize Names of Days: Capitalizes the days of the week. If you enter monday, Excel converts it to Monday.

· Correct Accidental Use of cAPS LOCK key: Corrects errors caused if you accidentally pressed the CapsLock key while typing.

· Replace Text as You Type: AutoCorrect automatically changes incorrect words as you type them.

Excel includes a long list of AutoCorrect entries for commonly misspelled words. In addition, it has AutoCorrect entries for some symbols. For example, (c) is replaced with ©, and (r) is replaced with®. You can also add your own AutoCorrect entries. For example, if you find that you frequently misspell the word January as Janruary, you can create an AutoCorrect entry so that it's changed automatically. To create a new AutoCorrect entry, enter the misspelled word in the Replace box and the correctly spelled word in the With field. You can also delete entries that you no longer need.

Tip

You can use the AutoCorrect feature to create shortcuts for commonly used words or phrases. For example, if you work for a company named Consolidated Data Processing Corporation, you can create an AutoCorrect entry for an abbreviation, such ascdp. Then, whenever you type cdp, Excel automatically changes it to Consolidated Data Processing Corporation. Just make sure that you don't use a combination of characters that might normally appear in your text and be replaced erroneously.

Note

In some cases, you may want to override the AutoCorrect feature. For example, you may need to literally enter (c) rather than a copyright symbol. Just click the Undo button on the Quick Access toolbar or press Ctrl+Z.

You can use the AutoFormat as You Type tab of the AutoCorrect dialog box to control a few other automatic settings in Excel.

The Actions tab enables what were formerly known as Smart Tags for certain types of data in your worksheets. The types of actions Excel recognizes vary depending on the types of software that are installed on your system. For example, if you enable the Financial Symbol action, you can right-click a cell that contains a financial symbol (such as MSFT, for Microsoft), choose Additional Cell Actions, and you'll be presented with a list of options. For example, you can insert a refreshable stock price in your worksheet.

The Math AutoCorrect tab contains shortcuts used to enter symbols when working in the Equation Editor. (See Chapter 23, “Enhancing Your Work with Pictures and Drawings.”)