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
● 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 I direct its focus on 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 worksheetin 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. You can enter a formula into a cell in one of two ways: Enter it manually, or enter it by pointing to cells that are used in the formula. I discuss each of these methods 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.
NOTE
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 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:
- Move the cell pointer to cell A3.
- Type an equal sign (=) to begin the formula.
Notice that Excel displays Enter in the left side of the status bar.
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 in the status bar. If you prefer, you can use your mouse and click cell A1.
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. A2 adds to the formula.
If you prefer, you can use your mouse and click cell A2.
- 6. Press Enter to end the formula.
Like 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.
This method might sound a bit tedious, but it’s actually very efficient once you get the hang of it. Pointing to cell addresses rather than entering them manually is almost always faster and more accurate.
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
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-1 shows a formula that contains spaces and line breaks.
TIP:
To make the Formula bar display more than one line, drag the border below the
Formula bar downward.
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 above 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 checkbox 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 checkbox 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.
Tip
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 the Formula bar as a calculator:
If you simply need to perform a calculation, you can use the Formula bar as a calculator. For example, enter the following formula into any cell:
=(145*1.05)/12
Because this formula always returns the same result, you may prefer to store the formula’s result rather than the formula. To do so, press F2 to edit the cell. Then press F9, followed by Enter. Excel stores the formula’s result (12.6875), rather than the formula. This technique also works if the formula uses cell references.
This technique is most useful when you use worksheet functions. For example, to enter the square root of 221 into a cell, type =SQRT(221), press F9, and then press Enter. Excel enters the result: 14.8660687473185. You also can use this technique to evaluate just part of a formula.
Consider this formula:
=(145*1.05)/A1
If you want to convert just the expression within the parentheses to a value, get into cell edit mode and select the part that you want to evaluate. In this example, select 145*1.05. Then press F9 followed by Enter. Excel converts the formula to the following:
=(152.25)/A1
Using Operators in Formulas
As previously discussed, an operator is the basic element 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 |
|---|
| + IS EQUALS TO Addition |
| – IS EQUALS TO Subtraction |
| / IS EQUALS TO Division |
| * IS EQUALS TO Multiplication |
| % IS EQUALS TO Percent* |
| & IS EQUALS TO Text concatenation |
| ^ IS EQUALS TO Exponentiation |
| = IS EQUALS TO Logical comparison (equal to) |
| > IS EQUALS TO Logical comparison (greater than) |
| < IS EQUALS TO Logical comparison (less than) |
| >= IS EQUALS TO Logical comparison (GREATER than,equal to) |
| <= IS EQUALS TO Logical comparison (less than or equal to) |
| <> IS EQUALS 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, described in the following list, work with cell references.
Table 2-2: Reference Operators
| Symbol = Operator |
|---|
| : (colon) IS Range. Produces one reference to all the cells between two references. |
| , (comma) IS Union. Combines multiple cell or range references into one reference. |
| (single space) IS Intersection. Produces one reference to cells common to two references. |
Sample 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. Many 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
- Unlike some other spreadsheets (such as Lotus 1-2-3), 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. But, if you enter the preceding formula, 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. 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.
Subtraction or negation?
One operator that can cause confusion is the minus sign (–), which you use for subtraction. However, a minus sign can also be a negation operator, which indicates a negative number.
Consider this formula:
=–3^2
Excel returns the value 9 (not –9). The minus sign serves as a negation operator, and has a higher precedence than all other operators. The formula is evaluated as “negative 3, squared.”
Using parentheses clarifies it:
=(–3)^2
The formula is not evaluated like this:
=–(3^2)
This is another example of why using parentheses, even if they are not necessary, is a good idea.
Use parentheses to override Excel’s built-in order of precedence. Returning to the previousprevious 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.
| Symbol = Operator |
|---|
| Colon (:), comma (,), space( ) IS Reference |
| – IS Negation |
| % IS Percent |
| ^ IS Exponentiation |
| * IS and / Multiplication and division |
| + IS and – Addition and subtraction |
| & IS Text concatenation |
| =, <, >, <=, >=, and <> IS Comparison |
What is Nested Parentheses in Microsoft Excel 2010 ?
How to use Nesting term in MICROSOFT EXCEL 2010 2007 2003 ?
What is Nested Parentheses in MICROSOFT EXCEL 2010 2007 2003 ?
Tips and Tricks in Microsoft Excel 2010 in Nesting Parentheses.
MICROSOFT EXCEL 2010 NESTED PARENTHESES AND MUCH MORE!!!!
NEED HELP IN NESTING PARENTHESES IN MICROSOFT EXCEL 2010 FORMULAS, PREDIFINED MICROSOFT EXCEL 2010 FORMULAS and A COMPLETE LEARNING GUIDE FOR BEGGINNERS AND KNOWS IF YOU NEED HELP RELATED TO NESTED PARENTHESES IN MICROSOFT EXCEL 2010 ITS THE RIGHT PLACE AND YOU WILL LEARN VERY FAST WITH THE HELP OF PICTURES AND VIDEOS TO EXPLAIN AND SOLVE YOUR ISSUES REGARDING MICROSOFT EXCEL 2010 FORMULAS, PREDEFINED FORMULAS FOR YOUR HELP, LETS STARTS WITH THE TERM NESTED PARENTHESES AND HOW TO DO, HOW TO USE NESTED PARENTHESES IN MICROSOFT EXCEL 2010 COMPLETE FORMULAS GUIDE LETS GO.
Nested parentheses
You can also nest parentheses in formulas — that is, put parentheses inside of 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
This 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.
It’s a good idea to 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
TIP
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 parentheses 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-2 shows an example of Excel’s AutoCorrect feature in action
Don’t hard-code values
When you create a formula, think twice before using a literal value in the formula. For example, if your formula calculates a 7.5 percent sales tax, you may be tempted to enter a formula such as
=A1*.075
A better approach is to insert the sales tax rate into a cell and use the cell reference in place of the literal value. This makes it easier to modify and maintain your worksheet. For example, if the sales tax range changes to 7.75 percent, you need to modify every formula that uses the old value. If the tax rate is stored in a cell, you simply change one cell and all the formulas recalculate using the new value.
CAUTION:
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-2, Excel proposed this correction:
=SUM(January/SUM(Total))
In fact, the correct formula is
=SUM(January)/SUM(Total)
TTIP:
Excel’s Formula AutoCorrect feature often suggests a correction to an erroneous formula.
What is Calculating Formulas in Microsoft Excel 2010 ?
How to use Calculating Formulas in MICROSOFT EXCEL 2010 2007 2003 ?
What is Calculating Formulas in MICROSOFT EXCEL 2010 2007 2003 ?
Tips and Tricks in Microsoft Excel 2010 in Calculating Formulas.
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 calculate 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 D11, cell D11 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 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'tt seem to return correct calculations.
- Ctrl+Shift+Alt+F9: Rechecks all the dependent formulas and then forces a recalculation of all open workbooks.
CATION
Contrary to what you might expect, Excel’s Calculation mode isn’t specific to a particular worksheet. 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 dollar sign 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
What is absolute or a mixed reference in Microsoft Excel 2010 ?
How to use absolute or a mixed reference in MICROSOFT EXCEL 2010 2007 2003?
What is absolute or a mixed reference in MICROSOFT EXCEL 2010 2007 2003 ?
Tips and Tricks in Microsoft Excel 2010 in absolute or a mixed reference.
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 enter 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-3 demonstrates an absolute reference in a formula. Cell D2 contains a formula that multiplies the quantity (cell B2) by the price (cell C2) and then by the sales tax (cell B7):
=(B2*C2)*$B$7
Figure 2-3: This worksheet demonstrates the use of an absolute reference.
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-4 demonstrates the use of mixed references. Note the formula in cell C3:
=$B3*C$2
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 usesabsolute 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-4: An example of using mixed references in a formula.
A1 versus R1C1 notation
Normally, Excel uses A1 notation. Each cell address consists of a column letter and a row number.
However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1,
cell A2 as R2C1, and so on.
To change to R1C1 notation, choose File➜Options to open the Excel Options dialog box, click the
Formulas tab, and place a check mark next to the R1C1 Reference Style option. Now, notice that
the column letters all change to numbers. And all the cell and range references in your formulas
also adjust.
Look at the following examples of formulas using standard notation and R1C1 notation. The formula
is assumed to be in cell B1 (also known as R1C2).
NOTE: THESE CHARACTERS ARE USED TO GIVE SPACE THEY HAVE NO USE OR THEY ARE NOT USED FOR PURPOSE OR THEY ARE NOT USED IN MICROSOFT EXCEL 2010 THESE CHARACTERS WERE USED TO GIVE SPACE BETWEEN BOTH TERMS " <<<----->>> "
| Standard<<<<<<----->>>>>> <<<<<<----->>>>>>R1C1 |
|---|
| =A1+1 <<<----->>> =RC[–1]+1 |
| =$A$1+1 <<<----->>> =R1C1+1 |
| =$A1+1 <<<----->>> =RC1+1 |
| =A$1+1 <<<----->>> =R1C[–1]+1 |
| =SUM(A1:A10) <<<----->>> =SUM(RC[–1]:R[9]C[–1]) |
| =SUM($A$1:$A$10) <<<----->>> =SUM(R1C1:R10C1) |
If you find R1C1 notation confusing, you're not alone. R1C1 notation isn’t too bad when you're dealing with absolute references. When relative references are involved, though, the brackets can drive you nuts.
The numbers in brackets refer to the relative position of the references. For example, R[–5]C[–3] specifies the cell that appears five rows above and three columns to the left. Conversely, R[5] C[3] references the cell that appears five rows below and three columns to the right. If you omit the brackets (or the numbers), it specifies the same row or column. For example, R[5]C refers to the cell five rows below in the same column.
Although you probably won't use R1C1 notation as your standard system, it does have at least one good use. R1C1 notation makes it very easy to spot an erroneous formula. When you copy a formula, every copied formula is exactly the same in R1C1 notation. This remains true regardless of the types of cell references you use (relative, absolute, or mixed). Therefore, you can switch to R1C1 notation and check your copied formulas. If one looks different from its surrounding formulas, it’s probably incorrect.
However, you can take advantage of the background formula auditing feature, which can flag potentially incorrect formulas. I discuss this feature in Chapter 21.
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:\MSOffice\Excel\[Budget Analysis.xlsx]Sheet1’!A1+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.
CATION
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.
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 a result 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.
4. Press Enter or Esc to cancel paste mode.
You can also take advantage of a Smart Tag. In Step 3 in the preceding list, press Ctrl+V to paste.
A Smart Tag appears at the lower-right corner of the range. Click the Smart Tag and select one of the Paste Values icons (see Figure 2-5).
Figure 2-5: A Smart Tag appears after pasting data.
This technique 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.
Posts
