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.