Microsoft Office Tutorials and References

In Depth Information

**Referring to Other Worksheets**

Copying Formulas

Referring to Other Worksheets

To reference a cell in another worksheet, you simply need to preface the cell with

the worksheet name, followed by an exclamation mark. For example, say you’ve

created a formula to double the value of the number in cell A1 on a worksheet named

Sheet1. You’d use this formula:

=A1*2

If you want to use the same formula in another worksheet (in the same workbook),

you’d insert this formula in the new worksheet:

=Sheet1!A1*2

Note:
If you use the point-and-click method to build formulas, you’ll find that you don’t need to worry

about the syntax for referring to cells on other worksheets. If you switch to another worksheet, and then

click a cell in it, while you’re building a formula, Excel automatically inserts the correct reference with the

worksheet name.

FreqUently Asked qUestion

How Changing the Location of Cells Affects Formulas

OK, I know how Excel adjusts a formula when I copy it to

another location. But what happens if I move cells around

after I’ve created a formula?

If at any time Excel can’t find your cells, the formula

changes to show the error code #REF!. You can then take a closer

look at the formula to find out what really went wrong. For

example, if you delete column B from your spreadsheet (by

selecting the column and using the Home➝Cells➝Delete

command), the formula changes to this:

No worries. It turns out that Excel is surprisingly intelligent.

Consider the following simple formula:

=B1+A2

If you cut and paste the contents of A2 to A3, Excel

automatically updates your formula to point to the new cell,

without complaining once. It also performs the same

automatic cleanup if you drag the contents of a cell to another

location (although if you simply make a duplicate copy of

the cell, Excel won’t change your formula). Excel is also on

the ball when you insert and delete rows and columns.

=#REF!+A2

Even though there’s still a B1 cell in your worksheet (it’s

the cell that was formerly named C1), Excel modifies the

formula to make it clear that you’ve lost your original data.