An absolute reference in Excel refers to a reference that is “locked” so that rows and columns won’t change when copied. Unlike a relative reference , an absolute reference refers to an actual fixed location on a worksheet.
To create an absolute reference in Excel, add a dollar sign before the row and column. For example, an absolute reference to A1 looks like this:
=$A$1
An absolute reference for the range A1:A10 looks like this:
=$A$1:$A$10
Example
In the example shown, the formula in D5 will change like this when copied down column D:
=C5*$C$2
=C6*$C$2
=C7*$C$2
=C8*$C$2
=C9*$C$2
Note that the absolute reference to C2, which holds the hourly rate does not change, while the reference to hours in C5 changes with each new row.
Toggle between absolute and relative addresses
When entering formulas, you can use a keyboard shortcut to toggle through relative and absolute reference options , without typing dollar signs ($) manually.
An aggregate operation is an operation that returns a single value . For example, given a range of data, the following formulas return a single result, even when the data provided contains hundreds or thousands of values.
=MAX(data)
=MIN(data)
=COUNT(data)
=AVERAGE(data)
=SUM(data)
Common functions that perform aggregate operations include, MAX , MIN , COUNT , COUNTA , AVERAGE , AND , OR , and SUM .
In many cases, an aggregate value is the desired result. However, because Excel can handle arrays and array operations , there are situations where a single value is not desired .
