Understanding relative versus absolute in Microsoft Excel 2019

When you refer to cell references when constructing formula, you havetwo options. You can refer to cells using relative references or you canuse absolute references. The type of reference used is only relevantwhen you copy the formulas to other cells.

As the name suggests, relative references will change—so,they refer to cells relative to the cell containing the formula. Inother words, when you copy a formula down a column, the cell referencesused in the formula move down the column too. 

Absolute references will always refer to the samecells. Sometimes, cell references need to remain the same whencopied or when you use the AutoFillcommand. I always refer to absolute ascementing the cell reference so that it cannot be moved whencalculating. You can press the F4 function key to make a cellabsolute (fixed /constant), or add dollar signs ($) tocell references to make a column and/or a row constant(fixed). 

Naming a cell and using it in a formula is another way of usingabsolute cell referencing. If you use this method, you do nothave to press F4 to make the cell absolute. We willlearn about defining a name range in the next topic.

Let’s look at an example usingthe SSGRegions.xlsx workbook, as follows:

  1. In cell K8, we have entered the formula to calculate VAT on Total sales. We have entered the formulaas =J8*K6, as can be seen in the following screenshot:

ms office 30

  1. K6 is the cell that contains the percentage VAT (entered as a decimal). This is the cellin which all the Product Totals will be multiplied.
  2. After entering the formula, we copied the formula using the AutoFill handle down to fill the formula tothe rest of the cells in the VAT column. When we had finishedcopying the formula, the errors appeared in column K.
  3. On investigation, we noticed that the formula in cell K8 is correct,but the other cells are incorrect due to the formula referencingcell K6 as relative. This means that when copied down thecolumn, K6 moves too! The formula in cell K9 has a #VALUE! error. This means that text has beenincluded in the formula in error. If we look at the formula in K9, wewill see that the formula is referencing cell K7, which contains thetext VAT, which is what is causing theerror in the cell.
  4. To fix all these errors, we simply need to change the formula incell K8. Double-click on cell K8.

ms office 227

  1. Select K6 by highlighting it with the mouse,then press the F4 function key on the keyboard to make the cellreference absolute. Note in the following screenshot that dollarsigns are added before and after the K:
ms office 481
  1. Press Enter, and then copy the formula down the columnusing the AutoFill handle. Yourformulas are now all correct.

Leave a Comment