Cell References in Excel

A handy function of the Excel program is Cell Reference Formulas. There are two types: Absolute and Relative.

Download the Cell References Example Sheet to follow along

Absolute:

We’ll use Sales Tax calculation as an example. To get the sales tax for each item in the spreadsheet, you need to multiply the price times the quantity. Then, take answer and multiply by the sales tax in cell D1.

Put this formula in the cell d3 to perform the calculation: = (b3*c3)*$d$1

Use the fill handle to drag the formula to other cells. The formula for B3 and C3 will change with each row’s formula by using the fill handle, but the Absolute reference to D1 (the sales tax) will always stay the same.

Relative:

Now, you can add the sales tax to the total for each item using Relative references.

In the example spreadsheet’s “totals” columns, add a formula to the first cell:

Enter “=(b3*c3)+d3” (Without quotations)

Labeling cells in this format – “B3“ – is called Relative Referencing.

This process will calculate the total for each of the items in each row. To finish, use the fill handle and pull down the formula to cell E7.

The segment you just created is called a range. To get the grand total in cell E8, enter “=sum(e3:e7)” in the cell and you’ll be finished with Cell References!