Microsoft Office Tutorials and References

In Depth Information

**Creating formulas to calculate values**

If you want a cell reference to remain constant when the formula using it is copied to

another cell, you can use an absolute reference. To write a cell reference as an absolute

reference, enter
$
before the row letter and the column number. For example, if you want the

formula in cell D16 to show the sum of values in cells C10 through C14 regardless of the cell

into which it is pasted, you can write the formula as
=SUM($C$10:$C$14)
.

TIP
Another way to ensure that your cell references don’t change when you copy the

formula to another cell is to click the cell that contains the formula, copy the formula’s text

in the formula bar, press the Esc key to exit cut-and-copy mode, click the cell in which you

want to paste the formula, and press Ctrl+V. Excel doesn’t change the cell references when

you copy your formula to another cell in this manner.

One quick way to change a cell reference from relative to absolute is to select the cell

reference in the formula box and then press F4. Pressing F4 cycles a cell reference through the

four possible types of references:

▪
Relative columns and rows (for example, C4)

▪
Absolute columns and rows (for example, $C$4)

▪
Relative columns and absolute rows (for example, C$4)

▪
Absolute columns and relative rows (for example, $C4)

In this exercise, you’ll create a formula manually, revise it to include additional cells, create a

formula that contains an Excel table reference, create a formula with relative references, and

change the formula so it contains absolute references.

SET UP
You need the ITExpenses workbook located in the Chapter03 practice file

folder to complete this exercise. Open the workbook, and then follow the steps.