Excel: Absolute reference - how it works

An absolute reference in an Excel formula is - as the name suggests - the unchangeable part of the formula. You can, for example, fix formulas in a cell. In this practical tip, we explain how an absolute reference is created in an Excel table.

Create an Excel formula with absolute references

Absolute references are always useful if you have one or more values ​​in the Excel formula that should apply to all formulas in a certain range. For example, you add the numbers of several rows and columns and want the respective sum to always be multiplied by the value of a specific cell. So that the whole thing becomes a little more vivid, we create a small table.

• First we select the Excel cell that should contain our absolute reference. In our case, cell A1, in which we enter the number 2. Enter any number in the cells from A3-A5 to C3-C5.
• Then click on cell D3, then go to the formula bar and enter an equal sign.
• Then click on the table cell A3, set the plus sign in the formula after A3, click on the Excel cell D3, set another plus sign and click on cell C3.
• After C3, enter the multiplication sign and then click on the cell with our absolute reference, the Excel cell A1.

This makes an MS Excel cell an absolute reference

Since we just want to casually pull down the formula we just created for the other rows, we now have to do something so that table cell A1 remains constant in the formula.

• First we move the cursor in front of the A1 in the processing line.
• Then press the [F4] key. A dollar sign is now placed in front of A and 1. This means that the value of this Excel cell is always adopted unchanged.
• From the dollar sign, you can easily see at a glance that you are dealing with an absolute reference.
• Finally, if you drag the formula into table cells D4 and D5, you will notice that the respective cell references change accordingly - except for cell A1, which remains unchanged.