Absolute Cell Reference in Excel
In the last blog, we learned about Relative Cell Reference . This article we explain Absolute Cell Reference in Excel with an example.
To recap, in Excel, data is stored in a worksheet containing rows and columns and the intersection of a row and column is a cell. Rows are represented by numbers and columns by alphabets. A cell reference or cell address is a combination of the row number and column letter that identifies the cell, for e.g. A1, B2 etc.
We can recall that cell references help to identify the data present in the specified cells. As shown in the figure below, A1 represents column A , row 1, and B2 represents column B, row 2.
The important Cell references in Excel are : Relative, Absolute and Mixed. This blog will help you understand Absolute cell reference.
What is Absolute Cell reference?
Relative references change when a formula is copied (or auto filled) to another cell. On the contrary, absolute references remain constant wherever they are copied. In Excel, an absolute reference is a cell reference in which the column and row coordinates remain constant when copying a formula from one cell to the other. A dollar symbol ($) is inserted before the row and column coordinates to make the reference absolute. For instance, $B$1 is an absolute reference to cell B1.
When should you use Absolute Cell Reference?
Absolute cell references are used when we need to perform a similar calculation on multiple cells, while some of the variables in the formula need to remain constant.
Let us consider the following example to understand absolute cell reference. The sheet shown below contains the weight of 5 items of jewelry purchased. We need to calculate the price of each item, which is the product of the weight of the jewelry (column B) and the gold rate (cell D1). We must keep in mind that D1 has to remain a constant throughout the calculation.
To calculate the price for the first item ‘Chain’, use the formula in cell C4 as
=B4*D1
We get the following output:
Since the same calculation needs to be repeated, we drag the fill handle on cell C4 up to C7. The following output will be displayed:
Incorrect values
Note that the price of the first item, viz, the chain was calculated correctly, but when we auto filled the rest of the cells, the output is incorrect. This is because, by default, Excel uses relative cell reference. We can check this by clicking on the Formula tab in the ribbon and then on Show Formulas. Observe in the sheet below that the variables have changed relatively to (B5, D2), (B6, D3) AND (B7, D4). Our objective is to keep D1 constant because it contains the gold rate.
Resolution
To fix this, we need to use absolute cell reference, which means, the D1 should remain constant in the formula. We will insert a dollar symbol ($) before the row and column coordinates. The new formula on cell C4 will now be
=B4*$D$1
Now, click on the fill handle and drag it up to cell C7.This will produce the correct output as shown below.
Absolute references are used when one or more variables in a calculation remain fixed or constant.
Reader exercise:
In the above example, the final price of each item needs to be calculated by adding sales tax(cell F1). Use the formula for the first item, viz. Chain, in cell E4 as
=C4+(C4*F1).
Using the above sheet, Readers should calculate the Final price for the remaning items. Check the output and use absolute reference to correct the same. Write your correct formula in the comments section below.
If you have any queries, or want to know the formula using absolute cell reference, feel free to type your question in the comments section.
Explore additional insights like these and enhance your Excel proficiency with comprehensive knowledge from the book Excel Basics to Advanced.
Enhance your Excel skills by exploring MS Excel Blogs for more valuable tips.
Chandraish Sinha is the founder and President of Ohio Computer Academy, a company dedicated to providing IT education. An enthusiastic IT trainer, Chandraish embodies his company’s motto: Inspire, Educate & Evolve.
He has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau, Power BI and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in various domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below:
