An excellent Excel program function is the addition of Cell Reference Formulas which help you calculate a number of different formulas spanning many cells. Absolute and Relative are the two types of references.
Download the Cell References Example Sheet to follow along
Absolute:
Sales Tax calculation is what we’ll use in the example. We must multiply the price by the quantity to get the sales tax for each item. We will multiple the answer by the sales tax in cell D1.
Enter the following formula cell D3: = (b3*c3)*$d$1
You can use the fill handle and copy this formula to the other cells. B3 and C3 each have a formula that will change by using the fill handle. The absolute reference to D1 (the sales tax) will not change.
Relative:
The next step is to use relative cell references to add the sales tax and the total for each item.
In the “totals” columns, type this formula in the first cell:
“=(b3*c3)+d3” (without the quotations)
When you are labeling cells like this – “B3“, you are using Relative Referencing.
You have now calculated the total for each for each item. Go ahead and use the fill handle to pull the formula down to cell E7.
The segment you just made is referred to as a range.
Finally, let’s calculate the grand total.
Select cell E8 and enter “=sum(e3:e7)”.
Related Templates:
- Creating Arrays in Excel
- Remove Spaces Between Words in Excel
- Count Cells in Excel Formula
- Get the Smallest Number in Excel Spreadsheet
- Using the Auto Fill Function in Excel
View this offer while you wait!