Microsoft Excel uses both absolute references and relative references. it is important to understand the difference between the two reference styles before writing formula.
– By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2
– Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.
– An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.
Making use of absolute references
• Perform calculation as normal
• Select the element of the formula that you which to make static and press F4
• This ensures that the cell in question remains the same irrespective of where the formula is dragged.
The example below shows how the use of Absolute References affects the calculation based on monthly sales of Widgets A and B.
As can be seen in Column F the first non relative formula changes the formula for each row it is copied to. This creates an error in all of the cells lower than row 7 as the formula is no longer looking at the price of the widgets and is instead looking at cell 4 rows higher than the calculated cell.
In column H the use of the $ signs have fixed the formula to always mulitply the sales numbers by the Widget costs. So Cells C3 and Cells C4 are fixed and only the number of sales changes dependant upon the row we are in.
Absolute references for row only
• Perform the calculation as normal
• Select the element that you wish to remain static
• Press F4 twice. This applies the absolute reference to the row only
• As the formula is dragged the row remains the same
Absolute references for column only
• As above but press F4 twice