Microsoft Office Tutorials and References

In Depth Information

**Gaining the Upper Hand on Formulas**

Understanding references

References abound in Excel formulas. You can reference cells. You can

reference ranges. You can reference cells and ranges on other worksheets. You can

reference cells and ranges in other workbooks. Formulas and functions are at

their most useful when using references, so you need to understand them.

And if that isn’t enough to stir the pot, you can use three types of cell

references: relative, absolute, and mixed. Okay, one step at a time here. Try a

formula that uses a range.

Formulas that use ranges often have a function in the formula, so use the

SUM function here:

1. Enter some numbers in many cells going down one column.

2. Click in another cell where you want the result to appear.

3. Enter
=SUM(
to start the function.

4. Click the first cell that has an entered value, hold the left mouse

button down, and drag the mouse pointer over all the cells that

have values.

5. Release the mouse button.

The range address appears where the formula and function are being

entered.

6. Enter a
).

7. Press Enter.

8. Give yourself a pat on the back.

Wherever you drag the mouse to enter the range address into a function, you

can also just type in the address of the range, if you know what it is.

Excel is
dynamic
when it comes to cell addresses. If you have a cell with a

formula that references a different cell’s address and you copy the formula from

the first cell to another cell, the address of the reference inside the formula

changes. Excel updates the reference inside the formula to match the number

of rows and/or columns that separate the original cell (where the formula is

being copied from) from the new cell (where the formula is being copied to).

This may be confusing, so try an example so you can see this for yourself:

1. In cell B2, enter
100
.

2. In cell C2, enter
=B2 * 2
.