An absolute cell reference in Excel is helpful for when you are copying a formula into multiple cells. In Excel spreadsheets sometimes you want a cell reference to stay the same when copied or filled. By default, the cell reference changes when a formula is copied to another cell. An absolute cell reference retains the formula without changing the values so you can copy the formula anywhere and keep the same cell value.
Let’s see an example of where an absolute cell reference would be helpful. In the above screenshot, the area of a circle has to be calculated. The formula to find it contains a constant value, π. When the formula =B6*A2*A2 is entered in cell B2, the answer is calculated and displayed.
Cell B6 contains a constant value of 3.14. When the formula is copied to the following cells, the answer is 0. That’s because, by default, the formula copied the relative cell reference. In cell B3, the formula became =B7*A3*A3, and the formula in cell C3 became =B8*A4*A4. Calls A3 and A4 contain the radius of the circle, which is used in the formula. Cells B7 and B8 don’t contain any value. Hence, a value of 0 is again displayed.
When the formula is copied down to B3 and B4, the formula moves as expected and tries to calculate the values in B7 and B8, respectively. B7 and B8 don’t contain the value of pi (π), so we get an error or a zero. We need to use an absolute reference to fix in place B6 as the place we are telling Excel to look. We do this by adding the dollar symbol before the B and the 6: $B$6. If you are on a Windows device, this can be achieved using the shortcut F4.
What Is an Absolute Reference?
By default, Excel supports relative cell reference. When you perform some arithmetic operations in Excel, you generally drag the cell that contains a value that is the result of the formula applied there. When you drag the cell down, the cell’s reference changes based on the row and column number. This is a relative reference.
Absolute Reference – Definition
An absolute reference in Excel fixes the cell’s reference to a value or a formula. When you use the cell containing the absolute reference in the formula, the value stays the same whether you move the cell or the formula within the same sheet or across different sheets.
The absolute cell reference in Excel works by locking a formula to a certain cell to keep the row and column constant. An Excel absolute reference adds a dollar symbol ($) before the column and row to keep the values intact.
For instance, if A1 is the default cell reference, $A$1 is the absolute cell reference.
If it precedes the column or row but not both, it’s known as a mixed reference. In a mixed reference, a cell value can be blended to have both absolute and relative cell reference. For instance, $A1 or A$1 is a mixed reference. In this case, either the column or row name remains constant.
Creating an Absolute Reference in Excel
Let’s take the following example. There’s a simple sales table that has the electronic items purchased, their price, and quantities. You can easily find the total amount to be paid by using the formula =B2*C2.
Let’s take, for instance, that there is a discount of $50 on each item purchased. Therefore, in the net amount, you need to reduce this amount of $50 from each item and then calculate the total amount.
The formula to calculate the net total amount after offering a discount of $50 is =(B2*C2) – B8. When you drag the cell, E2, so that the formula can be applied to the cells below it, you find that the formula is applied to the E2 cell alone.
The values in E3, E4, and E5 are the same as D3, D4, and D5 without the discount being applied to the cells below E2. This is an example of relative reference.
One of the Excel versatile features is that you can examine the formula applied to each cell. Click Formula → Show Formulas to display all the formulas applied in that worksheet instead of the results.
The below screenshot shows the formula applied to each cell in detail. In cell E3, the formula is applied over there as =(B3*C3)-B9. Similarly, the formulas in cells E4 and E5 are =(B4*C4)-B10 and =(B5*C5)-B11 respectively.
There are no values in cells B9, B10, or B11. Therefore, the total amount after the discount also remains the same as it was before deducting the discount.
Listed below are the steps to use the absolute cell reference feature in Excel.
- Select the cell where you want to create an absolute cell reference. In this example, it is cell B8. This cell contains the default Excel cell reference, the relative reference.
- When you put the $ symbol before column reference B, as in $B8, it locks the column, keeping the row reference from changing. It means that when you drag this cell, only the row keeps changing in that particular column, and the column remains constant.
- Put the dollar symbol before the row reference =B$8 to keep the column locked. It locks only the row, which means that when we drag the cell that contains the formula the column changes in that particular row, and the row $8 remains unchanged.
- Use the absolute cell reference =$B$8 to lock both the row and the column. Drag the cell that contains the formula and you find that the value in that cell remains constant.
How to Use an Excel Cell Reference?
In the example above, we used a discount of $50 in cell B8 to calculate the total amount of the purchase. Use the absolute cell reference in Excel = $B$8 in the formula. We will refer to the same constant value when the formula is copied and filled into other cells.
Select cell E2 that contains the formula, then enter the formula =(B2*C2)-$B$8. Now, drag the cell until it gets to E5 so the formula is copied to the rest of the cells below. The formula is copied to the remaining cells with absolute reference.
To check for accuracy, you can double-click any cells from E2 to E5. You may find that the absolute reference is the same for each cell, while the other references are relative to the cell’s row.
You can check the formula by clicking on Formulas → Show Formulas then looking at the column where absolute reference is applied. In both cases, whether you double-click on the cell or check by using the option Show Formulas, you can see the corresponding column and row reference changes but not the cell where you applied the absolute reference.
FAQs
1. What is an absolute cell reference?
An absolute cell reference is a method of fixing the cell’s location on a worksheet. When you copy the formula, the reference doesn’t change. By using an absolute cell reference, you can lock the column, the row, or both.
2. What are the types of cell references in Excel?
There are three types of Excel cell references. They are the following:
- Relative cell reference – The default cell references that adjust and change when copied or when Autofill is used.
- Absolute cell reference – Cells that remain the same when copied or when autofill is used. The dollar symbol ($) is used to ensure a column and/or row reference remains constant.
- Mixed cell reference – A cell’s column or row is locked to hold the value constant.
3. When would an absolute cell reference be most helpful?
An absolute cell reference is most useful when you don’t want cell references to change while copying a formula. Place the $ symbol before the cell reference that you wish not to change.
Final Thoughts
In this blog, we have showed how to make an absolute reference in Excel. An absolute cell reference comes in handy when someone like a sales manager is creating a spreadsheet to calculate the total amount of purchase and needs to input the sales tax into the total amount. The sales tax is constant for every customer and is applied to different cells without changing the cell reference of the constant value.
Check out our courses in Excel and Microsoft Office Applications to learn more about Excel functions and formulas. Some of the features in Excel that you can master here are lookup functions, pivot tables, and slicers. You can enroll in all of these courses on our website and will earn micro-credentials on the completion of these courses.
