Chapter 2: Basic Facts about Formulas

In This Chapter

• How to enter, edit, and paste names into formulas

• The various operators used in formulas

• How Excel calculates formulas

• Cell and range references used in formulas

• Copying and moving cells and ranges

• How to make an exact copy of a formula

• How to convert formulas to values

• How to prevent formulas from being viewed

• The types of formula errors

• Circular reference messages and correction techniques

• Excel's goal seeking feature

This chapter serves as a basic introduction to using formulas in Excel. Although it's intended primarily for newcomers to Excel, even veteran Excel users may find some new information here.

Entering and Editing Formulas

This section describes the basic elements of a formula. It also explains various ways of entering and editing your formulas.

Formula elements

A formula entered into a cell can consist of five elements:

• Operators: These include symbols such as + (for addition) and * (for multiplication).

• Cell references: These include named cells and ranges that can refer to cells in the current worksheet, cells in another worksheet in the same workbook, or even cells in a worksheet in another workbook.

• Values or text strings: Examples include 7.5 (a value) and “Year-End Results” (a string, enclosed in quotes).

• Worksheet functions and their arguments: These include functions such as SUM or AVERAGE and their arguments. Function arguments appear in parentheses and provide input for the function's calculations.

• Parentheses: These control the order in which expressions within a formula are evaluated.

Entering a formula

When you type an equal sign into an empty cell, Excel assumes that you are entering a formula because a formula always begins with an equal sign. Excel's accommodating nature also permits you to begin your formula with a minus sign or a plus sign. However, Excel always inserts the leading equal sign after you enter the formula.

As a concession to former Lotus 1-2-3 users, Excel also allows you to use an “at” symbol (@) to begin a formula that starts with a function. For example, Excel accepts either of the following formulas:

=SUM(A1:A200)

@SUM(A1:A200)

However, after you enter the second formula, Excel replaces the @ symbol with an equal sign.

If your formula uses a cell reference, you can enter the cell reference in one of two ways: Enter it manually or enter it by pointing to cells that are used in the formula. I discuss each method in the following sections.

Entering a formula manually

Entering a formula manually involves, well, entering a formula manually. You simply activate a cell and type an equal sign (=) followed by the formula. As you type, the characters appear in the cell as well as in the Formula bar. You can, of course, use all the normal editing keys when typing a formula. After you insert the formula, press Enter.

When you type an array formula, you must press Ctrl+Shift+Enter rather than just Enter. An array formula is a special type of formula, which I discuss in Part IV.

After you press Enter, the cell displays the result of the formula. The formula itself appears in the Formula bar when the cell is activated.

Entering a formula by pointing

The other method of entering a formula that contains cells references still involves some manual typing, but you can simply point to the cell references instead of typing them manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

1. Move the cell pointer to cell A3.

2. Type an equal sign (=) to begin the formula.

Notice that Excel displays Enter in the left side of the status bar.

3. Press twice.

As you press this key, notice that Excel displays a moving border around the cell, and that the cell reference (A1) appears in cell A3 and in the Formula bar. Also notice that Excel displays Point on the status bar.

If you prefer, you can use your mouse and click cell A1.

4. Type a plus sign (+).

The moving border becomes a solid blue border around A1, and Enter reappears in the status bar. The cell cursor also returns to the original cell (A3).

5. Press one more time. If you prefer, you can use your mouse and click cell A2.

A2 is appended to the formula.

6. Press Enter to end the formula.

As with typing the formula manually, the cell displays the result of the formula, and the formula appears in the Formula bar when the cell is activated.

If you prefer, you can use your mouse and click the check mark icon next to the Formula bar instead of pressing Enter. And if, at any time, you change your mind about entering the formula, just press Esc or click the X icon next to the Formula bar.

This method might sound a bit tedious, but it's actually very efficient after you get the hang of it. Pointing to cell addresses rather than entering them manually is almost always faster and more accurate.

Excel 2013 color-codes the range addresses and ranges when you are entering or editing a formula. This helps you quickly spot the cells that are used in a formula.

When you're working with a table of data (created by using Insert⇒Tables⇒Table), you can use a different type of formula — a self-propagating formula that takes advantage of column names. I cover this topic in Chapter 9.

Pasting names

As I discuss in Chapter 3, you can assign a name to a cell or range. If your formula uses named cells or ranges, you can type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically.

To insert a name into a formula, position your cursor in the formula where you want the name entered and use one of these two methods:

• Press F3 to display the Paste Name dialog box. Select the name and click OK.

• Take advantage of the Formula AutoComplete feature. When you type a letter while constructing a formula, Excel displays a list of matching options. These options include functions and names. Use the down-arrow key () to select the name and then press Tab to insert the name in your formula.

Figure 2-1 shows Formula AutoComplete in use. In this case, GrandTotal is a defined range name. This name appears in the drop-down list, along with worksheet function names.

Figure 2-1: Using Formula AutoComplete to enter a range name into a formula.

Spaces and line breaks

Normally, you enter a formula without using any spaces. However, you can use spaces (and even line breaks) within your formulas. Doing so has no effect on the formula's result, but can make the formula easier to read. To enter a line break in a formula, press Alt+Enter. Figure 2-2 shows a formula that contains spaces and line breaks.

To make the Formula bar display more than one line, drag the border below the Formula bar downward. Or click the downward-pointing icon at the extreme right of the Formula bar.

Figure 2-2: This formula contains spaces and line breaks.

Formula limits

A formula can consist of up to about 8,000 characters. In the unlikely event that you need to create a formula that exceeds this limit, you must break the formula up into multiple formulas. You also can opt to create a custom function by using Visual Basic for Applications (VBA).

Part VI focuses on creating custom functions.

Sample formulas

If you follow the preceding instructions for entering formulas, you can create a variety of formulas. This section provides a look at some sample formulas.

• The following formula multiplies 150 × .01, returning 1.5. This formula uses only literal values, so it doesn't seem very useful. However, it may be useful to show your work when you review your spreadsheet later.

=150*.01

• This formula adds the values in cells A1 and A2:

=A1+A2

• The next formula subtracts the value in the cell named Expenses from the value in the cell named Income:

=Income–Expenses

• The following formula uses the SUM function to add the values in the range A1:A12.

=SUM(A1:A12)

• The next formula compares cell A1 with cell C12 by using the = operator. If the values in the two cells are identical, the formula returns TRUE; otherwise, it returns FALSE.

=A1=C12

• This final formula subtracts the value in cell B3 from the value in cell B2 and then multiplies the result by the value in cell B4:

=(B2–B3)*B4

Editing formulas

If you make changes to your worksheet, you may need to edit formulas. Or if a formula returns one of the error values described later in this chapter, you might need to edit the formula to correct the error. You can edit your formulas just as you edit any other cell.

Here are several ways to get into cell edit mode:

• Double-click the cell. This enables you to edit the cell contents directly in the cell. This technique works only if the Double-click Allows Editing Directly in Cell check box is selected on the Advanced tab in the Excel Options dialog box.

• Press F2. This enables you to edit the cell contents directly in the cell. If the Double-click Allows Editing Directly in Cell check box is not selected, the editing will occur in the Formula bar.

• Select the formula cell that you want to edit and then click in the Formula bar. This enables you to edit the cell contents in the Formula bar.

When you edit a formula, you can select multiple characters by dragging the mouse over them or by holding down Shift while you use the arrow keys. You can also press Home or End to select from the cursor position to the beginning or end of the current line of the formula.

Suppose you have a lengthy formula that contains an error, and Excel won't let you enter it because of the error. In this case, you can convert the formula to text and tackle it again later. To convert a formula to text, just remove the initial equal sign (=). When you're ready to return to editing the formula, insert the initial equal sign to convert the cell contents back to a formula.

Using Operators in Formulas

As previously discussed, an operator is one of the basic elements of a formula. An operator is a symbol that represents an operation. Table 2-1 shows the Excel-supported operators.

Table 2-1: Excel-Supported Operators

Symbol	Operator
+	Addition
–	Subtraction
/	Division
*	Multiplication
%	Percent*
&	Text concatenation
^	Exponentiation
=	Logical comparison (equal to)
>	Logical comparison (greater than)
<	Logical comparison (less than)
>=	Logical comparison (greater than or equal to)
<=	Logical comparison (less than or equal to)
<>	Logical comparison (not equal to)

*Percent isn't really an operator, but it functions similarly to one in Excel. Entering a percent sign after a number divides the number by 100. If the value is not part of a formula, Excel also formats the cell as percent.

Reference operators

Excel supports another class of operators known as reference operators; see Table 2-2. Reference operators work with cell references.

Table 2-2: Reference Operators

Symbol	Operator
: (colon)	Range. Produces one reference to all the cells between two references.
, (comma)	Union. Combines multiple cell or range references into one reference.
(single space)	Intersection. Produces one reference to cells common to two references.

ample formulas that use operators

These examples of formulas use various operators:

• The following formula joins (concatenates) the two literal text strings (each enclosed in quotes) to produce a new text string: Part-23A:

=”Part-”&”23A”

• The next formula concatenates the contents of cell A1 with cell A2:

=A1&A2

Usually, concatenation is used with text, but concatenation works with values as well. For example, if cell A1 contains 123, and cell A2 contains 456, the preceding formula would return the value 123456. Note that, technically, the result is a text string. However, if you use this string in a mathematical formula, Excel treats it as a number. Some Excel functions will ignore this “number” because they are designed to ignore text.

• The following formula uses the exponentiation (^) operator to raise 6 to the third power, to produce a result of 216:

=6^3

• A more useful form of the preceding formula uses a cell reference instead of the literal value. Note this example that raises the value in cell A1 to the third power:

=A1^3

• This formula returns the cube root of 216 (which is 6):

=216^(1/3)

• The next formula returns TRUE if the value in cell A1 is less than the value in cell A2. Otherwise, it returns FALSE:

=A1<A2

Logical comparison operators also work with text. If A1 contains Alpha, and A2 contains Gamma, the formula returns TRUE because Alpha comes before Gamma in alphabetical order.

• The following formula returns TRUE if the value in cell A1 is less than or equal to the value in cell A2. Otherwise, it returns FALSE:

=A1<=A2

• The next formula returns TRUE if the value in cell A1 does not equal the value in cell A2. Otherwise, it returns FALSE:

=A1<>A2

• Excel doesn't have logical AND and OR operators. Rather, you use functions to specify these types of logical operators. For example, this formula returns TRUE if cell A1 contains either 100 or 1000:

=OR(A1=100,A1=1000)

This last formula returns TRUE only if both cell A1 and cell A2 contain values less than 100:

=AND(A1<100,A2<100)

Operator precedence

You can (and should) use parentheses in your formulas to control the order in which the calculations occur. As an example, consider the following formula that uses references to named cells:

=Income–Expenses*TaxRate

The goal is to subtract expenses from income and then multiply the result by the tax rate. If you enter the preceding formula, though, you discover that Excel computes the wrong answer. The formula multiplies expenses by the tax rate and then subtracts the result from the income. In other words, Excel does not necessarily perform calculations from left to right (as you might expect).

The correct way to write this formula is

=(Income–Expenses)*TaxRate

To understand how this works, you need to be familiar with operator precedence — the set of rules that Excel uses to perform its calculations. Upcoming Table 2-3 lists Excel's operator precedence. Operations are performed in the order listed in the table. For example, multiplication is performed before subtraction.

Use parentheses to override Excel's built-in order of precedence. Returning to the previous example, the formula without parentheses is evaluated using Excel's standard operator precedence. Because multiplication has a higher precedence, the Expenses cell multiplies by the TaxRate cell. Then, this result is subtracted from Income — producing an incorrect calculation.

The correct formula uses parentheses to control the order of operations. Expressions within parentheses always get evaluated first. In this case, Expenses is subtracted from Income, and the result multiplies by TaxRate.

Table 2-3: Operator Precedence in Excel Formulas

Symbol	Operator
Colon (:), comma (,), space( )	Reference
–	Negation
%	Percent
^	Exponentiation
* and /	Multiplication and division
+ and –	Addition and subtraction
&	Text concatenation
=, <, >, <=, >=, and <>	Comparison

Nested parentheses

You can also nest parentheses in formulas — that is, put parentheses inside parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested expressions first and works its way out. The following example of a formula uses nested parentheses:

=((B2*C2)+(B3*C3)+(B4*C4))*B6

The preceding formula has four sets of parentheses. Three sets are nested inside the fourth set. Excel evaluates each nested set of parentheses and then sums the three results. This sum is then multiplied by the value in B6.

Make liberal use of parentheses in your formulas even when they aren't necessary. Using parentheses clarifies the order of operations and makes the formula easier to read. For example, if you want to add 1 to the product of two cells, the following formula does the job:

=A1*A2+1

Because of Excel's operator precedence rules, the multiplication will be performed before the addition. Therefore, parentheses are not necessary. You may find it much clearer, however, to use the following formula even though it contains superfluous parentheses:

=(A1*A2)+1

Every left parenthesis, of course, must have a matching right parenthesis. If you have many levels of nested parentheses, you may find it difficult to keep them straight. Fortunately, Excel lends a hand in helping you match parentheses. When editing a formula, matching parentheses are colored the same, although the colors can be difficult to distinguish if you have a lot of parentheses. Also, when the cursor moves over a parenthesis, Excel momentarily displays the parenthesis and its matching parenthesis in bold. This lasts for less than a second, so watch carefully.

In some cases, if your formula contains mismatched parentheses, Excel may propose a correction to your formula. Figure 2-3 shows an example of Excel's AutoCorrect feature in action.

Simply accepting the correction proposed in the dialog box is tempting, but be careful. In many cases, the proposed formula, although syntactically correct, isn't the formula that you want. In the following example, I omitted the closing parenthesis after January. In Figure 2-3, Excel proposed this correction:

=SUM(January/SUM(Total)

In fact, the correct formula is

=SUM(January)/SUM(Total)

Figure 2-3: Excel's Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Calculating Formulas

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change any cells that the formula uses, the formula displays a new result with no effort on your part. This occurs when Excel's Calculation mode is set to Automatic. In this mode (the default mode), Excel follows certain rules when calculating your worksheet:

• When you make a change (enter or edit data or formulas, for example), Excel calculates immediately those formulas that depend on new or edited data.

• If working on a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you finish.

• Formulas are evaluated in a natural sequence. For instance, if a formula in cell D12 depends on the result of a formula in cell F12, cell F12 is calculated before D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you may find that things can slow to a snail's pace while Excel does its thing. In this case, you can set Excel's Calculation mode to Manual. Do this by choosing Formulas⇒Calculation⇒Calculation Options⇒Manual.

When you work in manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas.

The Formulas⇒Calculation group contains two controls that, when clicked, perform a calculation: Calculate Now and Calculate Sheet. In addition to these controls, you can use click the Calculate word on the status bar, or use the following shortcut keys to recalculate the formulas:

• F9: Calculates the formulas in all open workbooks (same as the Calculate Now control).

• Shift+F9: Calculates only the formulas in the active worksheet. It does not calculate other worksheets in the same workbook (same as the Calculate Sheet control).

• Ctrl+Alt+F9: Forces a complete recalculation of all open workbooks. Use it if Excel (for some reason) doesn't seem to return correct calculations.

• Ctrl+Shift+Alt+F9: Rechecks all the dependent formulas and then forces a recalculation of all open workbooks.

Contrary to what you might expect, Excel's Calculation mode isn't specific to a particular workbook. When you change Excel's Calculation mode, it affects all open workbooks — not just the active workbook. Also, the initial Calculation mode is set by the Calculation mode saved with the first workbook that you open.

Cell and Range References

Most formulas reference one or more cells by using the cell or range address (or the name if it has one). Cell references come in four styles; the use of the dollar sign symbol differentiates them:

• Relative: The reference is fully relative. When you copy the formula, the cell reference adjusts to its new location.

Example: A1

• Absolute: The reference is fully absolute. When you copy the formula, the cell reference does not change.

Example: $A$1

• Row Absolute: The reference is partially absolute. When you copy the formula, the column part adjusts, but the row part does not change.

Example: A$1

• Column Absolute: The reference is partially absolute. When you copy the formula, the row part adjusts, but the column part does not change.

Example: $A1

Creating an absolute or a mixed reference

When you create a formula by pointing to cells, all cell and range references are relative. To change a reference to an absolute reference or a mixed reference, you must do so manually by adding the dollar signs. Or when you're entering a cell or range address, you can press the F4 key to cycle among all possible reference modes.

If you think about it, you may realize that the only reason you would ever need to change a reference is if you plan to copy the formula.

Figure 2-4 demonstrates an absolute reference in a formula. Cell D2 contains a formula that multiples the quantity (cell B2) by the price (cell C2) and then by the sales tax (cell B7):

=(B2*C2)*$B$7

The reference to cell B7 is an absolute reference. When you copy the formula in cell D2 to the cells below, the $B$7 reference always points to the sales tax cell. Using a relative reference (B7) results in incorrect results in the copied formulas.

Figure 2-5 demonstrates the use of mixed references. Note the formula in cell C3:

=$B3*C$2

Figure 2-4: This worksheet demonstrates the use of an absolute reference.

This formula calculates the area for various lengths (listed in column B) and widths (listed in row 2). After you enter the formula, it can then be copied down and across. Because the formula uses absolute references to row 2 and column B, each copied formula produces the correct result. If the formula uses relative references, copying the formula causes the references to adjust and produce the wrong results.

Figure 2-5: An example of using mixed references in a formula.

Referencing other sheets or workbooks

A formula can use references to cells and ranges that are in a different worksheet. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Note this example of a formula that uses a cell reference in a different worksheet (Sheet2):

=Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point (!), like this:

=[Budget.xlsx]Sheet1!A1+1

If the workbook name or sheet name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:

='[Budget Analysis.xlsx]Sheet1'!A1+A1

If the linked workbook is closed, you must add the complete path to the workbook reference. For example:

='C:MSOfficeExcel[Budget Analysis.xlsx]Sheet1'!A1+A1

A linked file can also reside on another system that's accessible on your corporate network. The following formula refers to a cell in a workbook in the files directory of a computer named DataServer.

='\DataServerfiles[Budget Analysis.xlsx]Sheet1'!A1

If the linked workbook is stored on the Internet, the formula will also include the URL. For example:

='https://d.docs.live.net/86a61fd208/files/[Annual Budget.xlsx]Sheet1'!A1

Although you can enter link formulas directly, you can also create the reference by using the normal pointing methods discussed earlier. To do so, make sure that the source file is open. Normally, you can create a formula by pointing to results in relative cell references. But when you create a reference to another workbook by pointing, Excel always creates absolute cell references. If you plan to copy the formula to other cells, you must edit the formula to make the references relative.

Working with links can be tricky and may cause some unexpected problems. For example, if you use the File⇒Save As command to make a backup copy of the source workbook, you automatically change the link formulas to refer to the new file (not usually what you want). You can also mess up your links by renaming the source workbook file.

Copying or Moving Formulas

As you create a worksheet, you may find it necessary to copy or move information from one location to another. Excel makes copying or moving ranges of cells easy. Here are some common things you might do:

• Copy a cell to another location.

• Copy a cell to a range of cells. The source cell is copied to every cell in the destination range.

• Copy a range to another range. Both ranges must be the same size.

• Move a range of cells to another location.

The primary difference between copying and moving a range is the effect of the operation on the source range. When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range.

Copying a cell normally copies the cell's contents, any formatting that is applied to the original cell (including conditional formatting and data validation), and the cell comment (if it has one). When you copy a cell that contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination.

Copying or moving consists of two steps (although shortcut methods are available):

1. Select the cell or range to copy (the source range) and copy it to the Clipboard. To move the range instead of copying it, cut the range rather than copying it.

2. Move the cell pointer to the range that will hold the copy (the destination range) and paste the Clipboard contents.

When you paste information, Excel overwrites any cells that get in the way without warning you. If you find that pasting overwrote some essential cells, choose Undo from the Quick Access toolbar (or press Ctrl+Z).

When you copy a cell or range, Excel surrounds the copied area with an animated border. As long as that border remains animated, the copied information is available for pasting. If you press Esc to cancel the animated border, Excel removes the information from the Clipboard.

When you copy a range that contains formulas, the cell references in the formulas are adjusted. When you move a range that contains formulas, the cell references in the formulas are not adjusted. This is almost always what you want.

Making an Exact Copy of a Formula

When you copy a formula, Excel adjusts the formula's cell references when you paste it to a different location. Usually, adjusting the cell references is exactly what you want. Sometimes, however, you may want to make an exact copy of the formula. You can do this by converting the cell references to absolute references, as discussed earlier — but this isn't always desirable.

A better approach is to select the formula while in edit mode and then copy it to the Clipboard as text. There are several ways to do this. Here I present a step-by-step example of how to make an exact copy of the formula in A1 and copy it to A2:

1. Select cell A1 and press F2 to activate edit mode.

2. Press Ctrl+Home to move the cursor to the start of the formula, followed by Ctrl+Shift+End to select all the formula text.

Or you can drag the mouse to select the entire formula.

Note that holding down the Ctrl key is necessary when the formula is more than one line long, but optional for formulas that are a single line.

3. Choose Home⇒Clipboard⇒Copy (or press Ctrl+C).

This copies the selected text to the Clipboard.

4. Press Esc to end edit mode.

5. Activate cell A2.

6. Press F2, for edit mode.

7. Choose Home⇒Clipboard⇒Paste (or press Ctrl+V), followed by Enter.

This operation pastes an exact copy of the formula text into cell A2.

You can also use this technique to copy just part of a formula to use in another formula. Just select the part of the formula that you want to copy by dragging the mouse or by pressing the Shift+arrow keys. Then use any of the available techniques to copy the selection to the Clipboard. You can then paste the text to another cell.

Formulas (or parts of formulas) copied in this manner won't have their cell references adjusted when you paste them to a new cell. This is because you copy the formulas as text, not as actual formulas.

Another technique for making an exact copy of a formula is to edit the formula and remove its initial equal sign. This converts the formula to text. Then, copy the “nonformula” to a new location. Finally, edit both the original formula and the copied formula by inserting the initial equal sign.

Converting Formulas to Values

If you have a range of formulas that always produce the same result (that is, dead formulas), you may want to convert them to values. You can use the Home⇒Clipboard⇒Paste⇒Values command to do this.

Suppose that range A1:A10 contains formulas that calculate results that never changes. To convert these formulas to values:

1. Select A1:A10.

2. Choose Home⇒Clipboard⇒Copy (or press Ctrl+C).

3. Choose Home⇒Clipboard⇒Paste⇒Values (V).

4. Press Enter or Esc to cancel paste mode.

You can also take advantage of a drop-down control that shows paste options. In Step 3 in the preceding list, press Ctrl+V to paste. A drop-down list appears at the lower-right corner of the range. Click and select one of the Paste Values icons (see Figure 2-6).

Figure 2-6: Choosing a paste option after pasting data.

Converting formulas to values is very useful when you use formulas as a means to convert cells. For example, assume that you have a list of names (in uppercase) in column A. You want to convert these names to proper case. In order to do so, you need to create formulas in a separate column; then convert the formulas to values and replace the original values in column A. The following steps illustrate how to do this:

1. Insert a new column after column A.

2. Insert the following formula into cell B1:

=PROPER(A1)

3. Copy the formula down column B to accommodate the number of entries in column A.

Column B then displays the values in column A, but in proper case.

4. Select all the names in column B.

5. Choose Home⇒Clipboard⇒Copy.

6. Select cell A1.

7. Choose Home⇒Clipboard⇒Paste⇒Values.

8. Press Enter or Esc to cancel paste mode.

9. Delete column B.

Excel 2013 introduces a new feature — Flash Fill — that can also be an alternative to formulas. See Chapter 16 for more information about Flash Fill.

Hiding Formulas

In some cases, you may not want others to see your formulas. For example, you may have a special formula you developed that performs a calculation proprietary to your company. You can use the Format Cells dialog box to hide the formulas contained in these cells.

To prevent one or more formulas from being viewed:

1. Select the formula or formulas.

2. Right-click and choose Format Cells to show the Format Cells dialog box (or press Ctrl+1).

3. In the Format Cells dialog box, click the Protection tab.

4. Place a check mark in the Hidden check box, as shown in Figure 2-7.

5. Use the Review⇒Changes⇒Protect command to protect the worksheet.

To prevent others from unprotecting the sheet, specify a password in the Protect Sheet dialog box.

Figure 2-7: Use the Format Cells dialog box to change the Hidden and Locked status of a cell or range.

By default, all cells are locked. Protecting a sheet prevents any locked cells from being changed. So you should unlock any cells that require user input before protecting your sheet.

Be aware that it's very easy to crack the password for a worksheet. So this technique of hiding your formulas does not ensure that no one can view your formulas.

Errors in Formulas

It's not uncommon to enter a formula only to find that the formula returns an error. Table 2-4 lists the types of error values that may appear in a cell that has a formula.

Formulas may return an error value if a cell that they refer to has an error value. This is known as the ripple effect: A single error value can make its way to lots of other cells that contain formulas that depend on that cell.

Table 2-4: Excel Error Values

Error Value	Explanation
#DIV/0!	The formula attempts to divide by zero (an operation not allowed on this planet). This also occurs when the formula attempts to divide by an empty cell.
#NAME?	The formula uses a name that Excel doesn't recognize. This can happen if you delete a name used in the formula or if you misspell a function.
#N/A	The formula refers (directly or indirectly) to a cell that uses the NA function to signal unavailable data. This error also occurs if a lookup function does not find a match.
#NULL!	The formula uses an intersection of two ranges that don't intersect. (I describe range intersection in Chapter 3.)
#NUM!	A problem occurs with a value; for example, you specify a negative number where a positive number is expected.
#REF!	The formula refers to an invalid cell. This happens if the cell has been deleted from the worksheet.
#VALUE!	The formula includes an argument or operand of the wrong type. An operand refers to a value or cell reference that a formula uses to calculate a result.

If the entire cell fills with hash marks (#########), this usually means that the column isn't wide enough to display the value. You can either widen the column or change the number format of the cell. The cell also fills with hash marks if it contains a formula that returns an invalid date or time.

Refer to Chapter 22 for more information about identifying and tracing errors.

Dealing with Circular References

When you enter formulas, you may occasionally see a message from Excel like the one shown in Figure 2-8. This indicates that the formula you just entered will result in a circular reference.

A circular reference occurs when a formula refers to its own value, either directly or indirectly. For example, if you type =A1 into cell A3, =A3 into cell B3, and =B3 into cell A1, this produces a circular reference because the formulas create a circle where each formula depends on the one before it. Every time the formula in A3 is calculated, it affects the formula in B3, which in turn affects the formula in A1. The result of the formula in A1 then causes A3 to recalculate, and the calculation circle starts all over again.

Figure 2-8: Excel's way of telling you that your formula contains a circular reference.

When you enter a formula that contains a circular reference, Excel displays a dialog box with two options: OK and Help.

Normally, you'll want to correct any circular references, so you should click OK. After you do so, Excel inserts tracing arrows and displays the Help topic for circular references. The status bar displays Circular References: A3, in this case. To resolve the circular reference, choose Formulas⇒Formula Auditing⇒Error Checking⇒Circular References to see a list of the cells involved in the circular reference. Click each cell in turn and try to locate the error. If you cannot determine whether the cell is the cause of the circular reference, navigate to the next cell on the Circular References submenu. Continue reviewing each cell on the Circular References submenu until the status bar no longer reads Circular References.

Instead of navigating to each cell using the Circular References submenu, you can click the tracer arrows to quickly jump between cells.

After you enter a formula that contains a circular reference, Excel displays a message in the status bar reminding you that a circular reference exists. In this case, the message reads Circular References: A3. If you activate a different worksheet or workbook, the message simply displays Circular References (without the cell reference).

Excel doesn't warn you about a circular reference if you have the Enable Iterative Calculation setting turned on. You can check this in the Excel Options dialog box (in the Calculation section of the Formulas tab). If this option is checked, Excel performs the circular calculation the number of times specified in the Maximum Iterations field (or until the value changes by less than .001 — or whatever other value appears in the Maximum Change field). You should, however, keep the Enable Iterative Calculation setting off so that you'll be warned of circular references. Generally, a circular reference indicates an error that you must correct.

When the formula in a cell refers to that cell, the cause of the circular reference is quite obvious and is, therefore, easy to identify and correct. For this type of circular reference, Excel does not show tracer arrows. For an indirect circular reference, as in the preceding example, the tracer arrows can help you identify the problem.

Goal Seeking

Many spreadsheets contain formulas that enable you to ask questions such as, “What would be the total profit if sales increase by 20 percent?” If you set up your worksheet properly, you can change the value in one cell to see what happens to the profit cell.

Goal seeking serves as a useful feature that works in conjunction with your formulas. If you know what a formula result should be, Excel can tell you which values of one or more input cells you need to produce that result. In other words, you can ask a question such as, “What sales increase is needed to produce a profit of $1.2 million?”

Single–cell goal seeking (also known as backsolving) represents a rather simple concept. Excel determines what value in an input cell produces a desired result in a formula cell. You can best understand how this works by walking through an example.

A goal seeking example

Figure 2-9 shows a mortgage loan worksheet that has four input cells (C4:C7) and four formula cells (C10:C13). The formulas calculate various values using the input cell. The formulas are

C10: =(1–C5)*C4

C11: =PMT(C7/12,C6,–C10)

C12: =C11*C6

C13: =C12–C10

Figure 2-9: This worksheet presents a simple demonstration of goal seeking.

Imagine that you're in the market for a new home, and you know that you can afford $1,800 per month in mortgage payments. You also know that a lender can issue a fixed-rate mortgage loan for 6.5 percent, based on an 80 percent loan-to-value (a 20 percent down payment). The question is, “What is the maximum purchase price you can handle?” In other words, what value in cell C4 causes the formula in cell C11 to result in $1,800? You can plug values into cell C4 until C11 displays $1,800. Or, you can let Excel determine the answer.

To answer this question, choose Data⇒Data Tools⇒What-If Analysis⇒Goal Seek. Excel displays the Goal Seek dialog box, as shown in Figure 2-10. Completing this dialog box resembles forming the following sentence: Set cell C11 to 1800 by changing cell C4. Enter this information in the dialog box by either typing the cell references or by pointing with the mouse. Click OK to begin the goal seeking process.

Figure 2-10: The Goal Seek dialog box.

Almost immediately, Excel announces that it has found the solution and displays the Goal Seek status box. This box tells you the target value and what Excel came up with. In this case, Excel found an exact value. The worksheet now displays the found value in cell C4 ($355,974). As a result of this value, the monthly payment amount is $1,800. Now, you have two options:

• Click OK to replace the original value with the found value.

• Click Cancel to restore your worksheet to its original form before you chose Goal Seek.

More about goal seeking

If you think about it, you may realize that Excel can't always find a value that produces the result that you're looking for — sometimes a solution doesn't exist. In such a case, the Goal Seek Status box informs you of that fact. Other times, however, Excel may report that it can't find a solution even though you believe one exists. In this case, you can adjust the current value of the changing cell to a value closer to the solution and then reissue the command. If that fails, double-check your logic and make sure that the formula cell does indeed depend on the specified changing cell.

Like all computer programs, Excel has limited precision. To demonstrate this, enter =A1^2 into cell A2. Then choose Data⇒Data Tools⇒What-If Analysis⇒Goal Seek to find the value in cell A1 that causes the formula to return 16. Excel returns a value of 4.00002269 — close to the square root of 16, but certainly not exact. You can adjust the precision in the Calculation section of the Formulas tab in the Excel Options dialog box (make the Maximum change value smaller).

In some cases, multiple values of the input cell produce the same desired result. For example, the formula =A1^2 returns 16 if cell A1 contains either –4 or +4. If you use goal seeking when two solutions exist, Excel gives you the solution that is nearest to the current value in the cell.

Perhaps the main limitation of the Goal Seek command is that it can find the value for only one input cell. For example, it can't tell you what purchase price and what down payment percent result in a particular monthly payment. If you want to change more than one variable at a time, use the Solver add-in.