Microsoft Office Tutorials and References

In Depth Information

**Making an Exact Copy of a Formula**

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.

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.