How To Use An Absolute Reference In Excel

Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel Web App Excel 2010 Excel 2007 More...Less

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. If, for example, you lot refer to cell A2 from cell C2, you lot are actually referring to a cell that is two columns to the left (C minus A)—in the same row (ii). When y'all copy a formula that contains a relative cell reference, that reference in the formula will change.

Every bit an case, if you lot copy the formula =B4*C4 from jail cell D4 to D5, the formula in D5 adjusts to the right by ane column and becomes =B5*C5. If you desire to maintain the original cell reference in this example when y'all re-create it, yous make the prison cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). So, when you lot re-create the formula =$B$iv*$C$4 from D4 to D5, the formula stays exactly the aforementioned.

Less ofttimes, you may want to mixed absolute and relative jail cell references by preceding either the column or the row value with a dollar sign—which fixes either the cavalcade or the row (for example, $B4 or C$iv).

To change the blazon of prison cell reference:

Select the cell that contains the formula.
In the formula bar , select the reference that you want to change.
Press F4 to switch between the reference types.

The table beneath summarizes how a reference type updates if a formula containing the reference is copied two cells down and ii cells to the right.

For a formula being copied:	If the reference is:	It changes to:
	$A$1 (absolute column and absolute row)	$A$i (the reference is absolute)
	A$1 (relative column and absolute row)	C$1 (the reference is mixed)
	$A1 (accented column and relative row)	$A3 (the reference is mixed)
	A1 (relative column and relative row)	C3 (the reference is relative)