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

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

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 not what I intended. The correct formula is

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

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 cells. One way is to choose Home Editing Find & Select Go To (or press F5 or Ctrl+G), which displays the Go To dialog box. Click the Special button and then choose the Blanks option in the Go To Special dialog box. Excel will select all blank cells so that you can spot cells that appear to be empty but are not.

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, if text contains one or more trailing space characters.

A formula may return any of the following error values:

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

#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 32.1. The formula in cell D2, which was copied to the cells below it, is

=(C2-B2)/C2

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

This formula calculates the percent change between the values in columns B and C. Data isn’t available for months beyond May, 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 C:

= IF(C2=0,"",(C2-B2)/C2)

This formula displays an empty string if cell C2 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((C2-B2)/C2,"")

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 that is causing an error value to appear, activate the cell that contains the error and then use Formulas Formula Auditing Error Checking Trace Error. Excel draws arrows to indicate which cell is the source of the error. When you’ve identified the error, use Formulas Formula Auditing Error Checking Remove Errors to get rid of the arrow display.

New Feature

The IFERROR function is new to Excel 2007. For compatibility with previous versions, use this formula:

=IF(ISERROR((C2-B2)/C2),"",(C2-B2)/C2)

=SUM(B5:B14 A16:F16)

=SUM(B5:B14 A9:F9)

As described in Chapter 11, 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 32.2, 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 32.2. 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.

Excel has some straightforward rules about the order in which mathematical operations are performed (see Chapter 11). 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 exactly 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 the result is subtracted from 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)

If you use custom worksheet functions written in VBA, you may find that formulas that use these functions fail to get recalculated and may display incorrect results. 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.

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

=1/3

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

Cell B6 contains the following formula:

=SUM(B3:B5)

All the cells are formatted to display with three decimal places. As you can see, the formula in cell B6 appears to display an incorrect result. (You may expect it to display 0.999.) The formula, of course, does return the correct result. The formula uses the actual values in the range B3:B5, not the displayed values. You can instruct Excel to use the displayed values by checking the Set Precision As Displayed check box on the Advanced section of the Excel Options dialog box. (Choose Office Excel Options to display this dialog box.)

Computers, by their very nature, don’t have infinite precision. Excel stores numbers in binary format by using eight bytes, which can handle numbers with 15-digit accuracy. Some numbers can’t be expressed precisely by using eight bytes, so the number stores 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 Excel’s 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.

You may open a workbook and see a message like the one shown in Figure 32.4. 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 32.4. Excel’s way of asking you if you want to update links in a workbook.

First, try choosing Office Prepare 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. 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.

The Go To Special dialog box 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, which displays the Go To Special dialog box, as shown in Figure 32.5.

Figure 32.5. 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.6).

Figure 32.6. Zooming out and selecting all formula cells can give you a good overview of how the worksheet is designed.

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.

Figure 32.7 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. The View Window View Side By Side command, which allows synchronized scrolling, is also useful for viewing two windows (see Chapter 4 for more information about this command).

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

To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts:

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 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 to see 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+[ to select all direct precedent cells on the active sheet.

• Press Ctrl+Shift+{ to select all precedent cells (direct and indirect) on the active sheet.

• Choose Formulas Formula Auditing Trace Precedents, and 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.8 shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C13.

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

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 choose Formulas Formula Auditing Error Checking Trace Error. Excel draws arrows to indicate the error source.

If you accidentally create a circular reference formula, Excel displays a warning message, Circular Reference (with the cell address) in the status bar, and draws arrows on the worksheet to help you identify the problem. If you can’t figure out the source of the problem, use 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.

Some people may find it helpful to take advantage of Excel’s automatic error-checking feature. This feature is enabled or disabled by using the check box labeled Enable Background Error Checking, on the Formulas tab in the Excel Options dialog box shown in Figure 32.9. In addition, you can specify which types of errors to check for by using the check boxes in the Error Checking Rules section.

Figure 32.9. Excel can check your formulas for potential errors.

When error checking is turned on, Excel continually evaluates your worksheet, including its formulas. 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.10 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.

Figure 32.10. 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.)

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.11 shows the Error Checking dialog box. Note that this dialog box is modeless, so that you can still access your worksheet when the Error Checking dialog box is displayed.

Figure 32.11. Using the Error Checking dialog box to cycle through potential errors identified by Excel.

Excel’s Formula Evaluator lets you see the various parts of a nested formula evaluated in the order that the formula is calculated. To use the Formula Evaluator, select the cell that contains the formula and choose Formula Formula Auditing Evaluate Formula to display the Evaluate Formula dialog box (see Figure 32.12).

Figure 32.12. Excel’s Formula Evaluator 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:

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.

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

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 32.14). When you select an entry in this list, Excel scrolls the worksheet so that you can view it in context.

Figure 32.14. Displaying the result of a search in the Find And Replace dialog box.

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 then locates the next matching item. To override the replacement, click Find Next. To replace all items without verification, click Replace All.

The Find And Replace dialog box also enables you to 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:

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.