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.
Copying a formula changes the formula references.
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
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.
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
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