How to Use Absolute References in Excel:
Most people are familiar with the use of relative references. in excel. This is because cell references in Excel spreadsheets default to the relative reference method.
However, there are times when relative references become annoying. This will change the cell references each time you copy a cell or fill in columns and rows. If you don’t want to change references, you have to go with absolute references or mixed references (including relative and absolute references).
In an absolute context, both the column and row references are “locked” so neither of them change when you copy or fill in that cell.
This article will look at how you can use absolute references to get Excel to treat the data the way you want.
How Relative References Work in Excel
When you enter values in an Excel spreadsheet, each cell has a specific letter and number. It shows the column and row of that cell.
For example, in the spreadsheet below, the value of “1” is in column A and row 2. So the “reference” to this cell is A2.
If you want to calculate based on this cell in the next cell, adding 1 to it, you would write the following formula:
This formula will insert the value from A2 into the formula, calculate it, and then output the result to the cell where this formula is.
when you press recorded, you will see the result.
With relative reference, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula as far down as you want.
In the next cell, A2 will be referenced to A3. A3 will become A4 in the cell below it. In other words, Excel knows you want to add 1 to the previous cell, so as you drag down, Excel updates the number (the row reference) accordingly.
It works the same way when you drag the formula across the column. Instead of updating the number, Excel will always update the column after the reference to refer to the cell above it.
The column on the right has B2, to its right is C2, and so on.
This is a simple example of how relative addressing works for both column and cell references.
How Absolute References Work in Excel
Absolute Reference in Excel lets you refer to a single cell, rather than excel automatically update Row or column references for you. “Mixed” reference is when you lock only the row or column, and “absolute reference” is when you lock both.
Let’s look at some examples.
Let’s say your spreadsheet has “10” in the top row, and you want each row below it to multiply that number by the number of cells on the left.
To do this, you’ll enter a formula that looks like this:
This locks the reference “2”, so if you drag the cell with this formula to the cells below it will not change the row reference. Since A3 remains “unlocked”, both row and column references will still automatically change and will always refer to the cell to the left.
You’ll notice that this only works because you’re dragging down in a cell in the same column. So you don’t need to lock it by putting a dollar sign ($) in front of column (B).
The problem with this is that if you were to use the same formula to the right of the original formula, the reference to “B” would change, and the formula would no longer refer to B2.
Let’s take a look at how to use absolute references instead of mixed references to make filling work properly in both directions.
Using Absolute Reference Correctly in Excel
To use the correct context in this thread, you must consider exactly what you are trying to do.
In this case, we want the following behavior when filling in the right side.
- Always cell B2. refer to the value in
- Always column A. refer to the value in
- Move the row reference for column A to the current row of the formula
Given these behaviors, you now know what you need to “lock” on and what don’t. “B” and “2” both need to be locked (unchanged). Also, column A needs to be locked.
So your formula in B3 should look like this: =$b$2*$a3
Now when you drag this cell either down or up, the formula works on its own.
Absolute reference can be difficult to use correctly, so it’s important to take the time to carefully consider how you want Excel to update the formula you fill in. columns or rows in any direction.
Cycling Through Reference Types in Excel
You can speed up the process when you are typing formula with absolute references by pressing F4 key, which will make the cell reference absolute.
When you press F4 the cursor can be on either side of (or even in the middle of) the cell reference, and it will still convert that single reference to an absolute.
If you don’t want Full (for example, Blended instead), keep tapping F4 until the context looks the way you want.
If you want to add any kind of references to other cells in the formula, just place your cursor there and start cycling through F4 again.
Once you’ve configured your formula, just hit Enter and start filling out your spreadsheet in any direction you like. If you set your contexts right, everything should work as expected.