Microsoft Office Tutorials and References
In Depth Information
Gaining the Upper Hand on Formulas
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
5. Release the mouse button.
The range address appears where the formula and function are being
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 .