Microsoft Office Tutorials and References

In Depth Information

**Working with Formulas**

You edit formulas in the same way you edit any

other Excel data, either by retyping the formula or

double-clicking the cell and making corrections.

When editing formulas, Excel color codes each cell

address to its corresponding cell. When you press

the Enter key, Excel recalculates the formula.

Figure 9-4

Copying a formula changes the formula references.

Copying Formulas

Now that you’ve created a formula, there’s no

reason to type it repeatedly for subsequent cells. Let

Excel copy the formula for you! When you copy a

formula, the formula changes depending on where

you put it. It is said, therefore, to be relative—relative

to the position of the original formula.

Copying with Copy and Paste

If the originating cells and the recipient cells are

not sequential, you will find it easier to use the

Copy and Paste commands. You first learned about

the Windows copy and paste functions in Chapter

2, “Getting Started with Word.”

Copying with AutoFill

If you’re going to copy a formula to surrounding

cells, you can use the AutoFill method. You first

learned about the AutoFill command in Chapter 8,

“Creating a Basic Worksheet.” Follow these steps to

copy a formula:

1.
Select the cell with the formula that you

want to duplicate.

2.
Press Ctrl+C or choose Home>Clipboard>

Copy. A marquee appears around the copied

cells.

3.
Highlight the cells in which you want to

place the duplicated formula and either press

Ctrl+V or choose Home>Clipboard>Paste.

1.
Click the cell that has the formula.

2.
Position the mouse pointer on the

lowerright corner of the beginning cell. Make sure

the mouse pointer turns into a black cross.

Cancel Marquee

3.
Press and hold the mouse button and drag to

select the next cells to be filled in.

If necessary, press the Escape key to cancel

the “marching ants” marquee.

4.
Release the mouse button. Excel copies the

formula.

Copying Values Instead of Formulas

As you’ve seen when you copy a formula, you don’t

copy the formula results, you copy the formula’s

underlying mathematical expression. What about

those times where you just want the resulting

value, but not the formula? Fortunately, Excel

includes a tool that provides the value only. Copy

the cells containing the values you want and then

in the cell where you want the answers, click the

When Excel copies a formula, the references

change as the formula is copied. If the original

formula was =E2+F2 and you copied it to the next

cell down, the formula would read =E3+F3. Then,

if you copied it down again it would be =E4+F4,

and so on. Take a look at the copied formula in

Figure 9-4.