In this tutorial, we will know what is Excel absolute cell reference and how to use it. Along with how it differs in relative cell reference in excel.
What is an Absolute Cell Reference in Excel
An absolute cell reference of Excel is a reference whenever copied or moved to another cell there are no changes happen. Commonly, absolute reference is used when you want a fixed cell location.
Further, you can distinguish this through a dollar symbol ($). Basically, dollar sign is used before the coordinates in order to correct them.
For instance, cell G2 becomes an absolute reference if you did this $G$2. Moreover, you can use F4 key shortcut to create an absolute reference.
Additionally, absolute reference means it remains the same and consistent regardless of the size or content of the worksheet whenever you copied or moved it to another cell.
Particularly this absolute cell reference is essential whenever the worksheet contains constant values.
Difference between Absolute and Relative References in Excel
An absolute reference in Excel is a reference which is fixed and constant so that when copied or moved rows and columns will not change. Unlike relative reference that is automatically altered when the reference is moved or copied in another cell.
In simple words, absolute reference is an actual fixed location in a worksheet.
Apparently, this absolute reference is helpful in certain situations like you don’t want to change a reference when filling cells.
How to use an Absolute Reference in Excel
In our example, we’ll use the 10% grade incentives to calculate the final average of the students.
We’ll need to use the absolute cell reference $F$1 in our formula. Because each formula is using the same incentive, we want that reference to remain constant when the formula is copied and filled to other cells in column F.
Time needed: 2 minutes
This time we will utilize absolute cell reference in our example. Follow the step-by-step below.
- Select a cell where you enter your formula.
In our example, we’ll select cell G3.
- Enter the formula for calculation.
The formula we enter here in cell G3 is
=AVERAGE(C3:F3)+$F$1to get the final average.
- Hit Enter after you create your formula.
On your keyboard Press Enter after you are done with your formula. The formula will calculate, and the result will display in the cell.
- Drag the fill to copy the formula.
Locate the fill handle in the lower-right corner of the selected cell. In our example, we’ll locate the fill handle for cell G3. Click, hold, and drag the fill handle over the cells you want to fill, in our example, it’s in cells G3:G10.
- Release the mouse from hold.
After dragging the handle fill release the mouse. Hence, the formula should be copied along with the absolute cell reference and the values will be calculated in each cell.
- Check the cells for accuracy
Double-click the cell to see if the formula is accurate and absolute cell reference copied does not change while the other cell reference is relative.
- Things to Remember
Always remember to include a dollar sign($) when you want to fill it across multiple cells. Once, you missed to put the dollar sign it will be considered a relative reference.
Shortcut for Absolute Reference
Absolute Cell reference in Excel has a shortcut instead of manually typing the dollar sign in your formula. So after you type your formula in your cell reference –press the F4 key. This will automatically change your cell reference to absolute.
If you continue to press F4 this will cycle through all the absolute reference possibilities.
Conclusion
We hope this article has clarified how to use an Absolute reference in Excel. This topic may be difficult to grasp at first, but it is critical if you want to improve your Excel skills.
Thank you for reading 🙂