EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Absolute Reference in Excel

Absolute Reference in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 16, 2023

Absolute Reference in Excel

Absolute Reference in Excel (Table of Contents)
  • Absolute Cell Reference in Excel
  • How to Create Absolute Cell Reference in Excel?
  • How to Use Absolute Cell Reference in Excel?

Introduction to Absolute Cell Reference in Excel

Absolute reference in Excel is used when we want to fix the position of the selected cell in any formula so that its value will not change when we are changing the cell or copying the formula to other cells or sheets. This is done by putting the dollar (“$”) sign before and after the column name of the selected cell.

For example, if you want to fix cell A1, it will look like “=$A$1”. Or else, we can type the F4 function key, which will automatically cover the column alphabet with the dollar.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Uses of Absolute Cell Reference in Excel

The absolute cell reference in Excel is a cell address that contains a dollar sign ($). It can precede the column reference, the row reference, or both. With an absolute cell reference in Excel, we can keep a row, column, or both constant. It doesn’t change when copied to other cells.

How to Create an Absolute Cell Reference in Excel?

Below are the steps to convert a cell address into an absolute cell reference:

  • Select a cell where you want to create an absolute cell reference. Suppose cell A1.
  • =A1 – It is a relative reference by default. However, we must add a dollar sign ($) before the column or row coordinates to create an absolute reference.
  • =$A1 –  If a $ dollar sign is placed before the column coordinate (A), it will only lock the column. When we copy or drag this formula cell to other cells in the same row, the column will remain fixed at A, while the row coordinate will change.
  • =A$1 – If we put a $ dollar sign before the row coordinate (1), it will lock only rows. This means that when we copy or drag this cell to other cells in the same column, the row coordinate will remain fixed at 1, while the column coordinate will change.
  • =$A$1 – It’s called an absolute cell reference, which locks both the row and the column. If we drag or copy this cell, the formula cell will remain constant, i.e., it will always refer to cell A1, no matter where it is copied or dragged.

How to Use Absolute Cell Reference in Excel?

Let’s take some examples to understand using an absolute cell reference in Excel.

You can download this Absolute Reference Excel Template here – Absolute Reference Excel Template

Absolute Reference in Excel – Example #1

The hourly wage rate is $10.00 per hour. We need to calculate the gross pay for each worker based on their hours worked. The table below shows data for a group of workers, including their names and the corresponding number of hours worked.
Absolute Reference Example 1-1

Solution:
We will input the hourly wage rate amount of $10 in cell B16. Then, as seen in the image below, to calculate the gross pay for the first employee (John), we will multiply the fixed-wage hourly rate of $10.00 (cell B16) by the number of hours worked (cell B20). Therefore, we will enter the formula “=B20*B16” in cell C20.
Absolute Reference Example 1-2

The result is $50, as shown below.
Absolute Reference Example 1-3

However, if we drag the formula and paste it into the corresponding cells in each row for the other workers, it will give incorrect results, as shown in the below image. This is because the fixed-wage hourly rate is changing for each worker.
Absolute Reference Example 1-4

To fix this, we must make the cell containing the fixed-wage hourly rate an absolute cell reference by adding a $ sign before the column name and row number.
Absolute Reference Example 1-5

We can put a $ dollar sign before the column name and the row number, like this – $B$16. The result is shown below.
Absolute Reference Example 1-6

Through this, we can lock the value of B16 for all workers. Now, we can drag this formula for the rest of the workers to recalculate their gross pay.
Absolute Reference Example 1-7

Note: You can use the F4 key as a shortcut to make a cell an absolute cell reference. It automatically inserts a dollar sign in both places.

Absolute Reference in Excel – Example #2

Alice went to a retail shop and purchased about 15 groceries and beauty products. Each item has a GST rate of 10%. To calculate the Net Amount, we need to determine the tax rate for each item.
Example 2-1

Solution:
First, we will calculate each item’s total price by multiplying the number of items by the corresponding product price.
Example 2-2

The result is seen below.
Example 2-3

Drag this formula for the rest of the products to get their total prices.
Example 2-4

To calculate the tax rate for each product, multiply the total price by the GST rate (10%).

Use an absolute cell reference for the GST rate by making cell E33 an absolute reference using the F4 key. This will add a $ sign before E and 33 like this – $E$33
Example 2-5

The result is 20.
Example 2-6

Drag this formula to get their tax rates for the rest of the products. For instance, the formula in cell F49 is =E35*$E$33.
Example 2-7

To calculate the net amount, add the total price with the GST for each product using the SUM Function in Excel.
Example 2-8

The final result is Rs. 300.60 (the sum of the net amounts for all the products).
Example 2-9

Advanced Techniques

There are several advanced techniques you can use with absolute references in Excel. Here are a few examples:

#1 Using Absolute References to Create Dynamic Range Names

You can use absolute references to create dynamic range names that automatically adjust depending on the data size.

Suppose you have a dataset as shown below. To create a dynamic range name for the Sales column, follow these steps:

Date

Sales

01/01/2022 $100
02/01/2022 $200
03/01/2022 $300
04/01/2022 $400
05/01/2022 $500

1. Select the entire column (in this case, B2:B6).
Select the Column

2. Click on the “Formulas” tab, and then click on “Define Name” in the “Defined Names” section.
Click on the Formulas tab

3. In the “New Name” dialog box, enter a name for the range (e.g., “SalesData“).
Type a Name

4. In the “Refers to” field, enter the formula =OFFSET(Sheet1!$B$2,0,0, COUNTA(Sheet1!$B:$B)-1,1), and then click OK.
Type a Formula

This will create a dynamic range name that adjusts automatically when new data gets added to the Sales column.

#2 Using Absolute References with IF Function

You can use absolute references with the IF function to perform calculations based on specific criteria.

Suppose you have the following dataset:

Date

Sales

01/01/2022 $100
02/01/2022 $200
03/01/2022 $300
04/01/2022 $400
05/01/2022 $500

To use Absolute References with the IF function, follow these steps:

1. In cell C2, enter the formula =IF(B2>$300,”Yes”,”No”).
In cell C2, enter the formula

2. Copy the formula down to the rest of the cells in column C.
Copy the formula

This will use an absolute reference to compare each value in the Sales column to a fixed value ($300 in this case) and return “Yes” if the value exceeds $300.
Formula applied to All the Cells

Things to Remember

  1. Use relative references when appropriate: Relative references can make your formulas simpler and easier to read, especially for simple calculations. Reserve absolute references for situations where you must lock a specific cell or range of cells.
  2. Understand the difference between references: Excel allows for mixed references, enabling you to lock only one part of a cell reference while leaving the other relative. Ensure you understand the difference and choose the appropriate reference type for your needs.
  3. Avoid circular references: A circular reference is when a formula returns to its cell, forming an endless loop. This can cause errors in a spreadsheet.
  4. Document your formulas: If you share your spreadsheet with others or need to refer to it later, document them. So that others can understand how they work and troubleshoot any issues.
  5. Use named ranges: Rather than referencing cells in your formulas, consider using named ranges to make your formulas easier to understand and sustain.
  6. Use comments: If your formulas are particularly complex, consider using comments to explain how they work and why you chose to use absolute references in certain places.
  7. Separate sheet for formulas: If you have many complex formulas in your spreadsheet, consider creating a separate sheet for storing and documenting your formulas. This can make managing and troubleshooting your formulas easier, especially if you need to make changes later.

Common Errors

People make a few common errors when using absolute references in Excel. Some of these errors include:

  1. Forgetting to use the dollar sign: When creating an absolute reference, it is essential to utilize the sign of dollar ($) before the column and row of the cell reference to lock it. If you forget to do this, Excel will interpret it as a relative reference, which can result in incorrect calculations.
  2. Using the wrong reference: It’s easy to reference the wrong cell when using absolute references, especially when working with large datasets. This can happen if you accidentally select the wrong cell or change the position of the referenced cell without updating the formula.
  3. Copying and pasting incorrectly: When copying and pasting formulas that contain absolute references, you need to make sure that you select the correct cell when pasting the formula. If you paste the formula into the wrong cell, the absolute reference may not point to the intended cell, resulting in incorrect calculations.
  4. Forgetting to lock the correct cell: When creating an absolute reference, it’s important to lock it using the dollar sign. If you lock the wrong cell or forget to lock it, it can lead to errors in your calculations.
  5. Using absolute references unnecessarily: While they can be helpful in certain situations, they can make your formulas more complex and harder to read. It’s important to use them judiciously and only when necessary.
  6. Failing to update absolute references: If you change the location of cells or ranges referenced in your absolute reference, you must update the reference accordingly. Forgetting to do so can guide you to errors in your calculations.

Advantages of Absolute References

  1. Saves Time: Using absolute references in Excel saves you time when copying formulas across many cells. The fixed value or formula can be in reference without manually updating each cell. This is particularly useful when working with large data sets or complex formulas.
  2. Accuracy: Absolute references can help ensure the accuracy of formulas by keeping specific values constant. This is especially useful when working with complex formulas that reference many cells. By using absolute references, you can be confident that the formula will always produce the correct result, regardless of changes to other cells.
  3. Customization: Absolute references are customizable in Excel, allowing you to choose which cells to remain fixed and which cells you want to be relative. This flexibility creates formulas that adapt to different situations, like changes in input variables or data structure.
  4. Replication: Absolute references help replicate formulas with fixed values, such as commission calculations. By creating an absolute reference to the fixed value, you can copy the formula to other cells without manually adjusting each one. This can save time and reduce the risk of errors.
  5. Improved readability: Absolute references make formulas easier to read and understand. They show which cells contain constants and variables. By fixing specific values in your formulas, you can show which cells are input and output, making the formula easier to follow.
  6. Consistent formatting: Absolute references ensure that your Excel sheets are consistent. You can keep your formulas and charts neat and tidy by fixing certain cells and ranges, even adding or removing data. This can make reading and understanding the data easier and help prevent formatting errors.

Disadvantages of Absolute References

  1. Harder to Read: While absolute references can offer benefits, they can make formulas more complex and harder to read. This is particularly true when you have many absolute references in the same formula. In this case, the formula can become difficult to understand and troubleshoot, especially if you didn’t create it yourself.
  2. Error-Prone: Absolute references can be error-prone if you forget to lock the correct cells. Suppose you forget to lock the cell that contains the fixed tax rate in a sales tax calculation formula. Here, copying the formula to other cells may result in incorrect calculations. This can lead to incorrect calculations and wasting time troubleshooting the issue.
  3. Potential for Data Inaccuracy: While absolute references can help ensure accuracy by keeping specific values constant, they can also lead to data inaccuracy if the referenced cell or range of cells contains incorrect data. For example, suppose the cell containing the fixed tax rate in a sales tax calculation formula has the wrong value. In that case, all the cells referencing that cell will also produce incorrect results.
  4. Difficulty Adapting to Changes: Using absolute references can make it more difficult to adapt to changes in data, especially if you need to change the cells that contain fixed values. In this case, you may need to update all the formulas referencing the fixed cells, which can be time-consuming and prone to errors.
  5. Limits Flexibility: While absolute references can allow you to choose which cells to fix and which to be relative to, they can also limit flexibility by fixing cells you may want to change. For example, if you create a formula that references a cell containing a date, using an absolute reference will fix the date to the specific cell, making it difficult to change the date without changing the formula.

Relative vs. Absolute References

Aspect

Relative References

Absolute References

Definition                References that adjust with cell movement References that remain constant despite movement
Denoted By Denoted by cell references without dollar signs Cell references with dollar signs denoted it
Change Change based on where they are copied or moved Remain fixed regardless of where they are copied or moved
Usage Useful when creating formulas that need to be adjusted based on location Useful when creating formulas that need to reference specific cells
Syntax  A referenced cell gets indicated by its position A referenced cell gets indicated by its cell address
Example                If you copy a formula that refers to cell A1 and pastes it into cell B1, the reference in the formula will change from A1 to B1. If you copy a formula that refers to cell A1 and pastes it into cell B1, the reference will remain A1.

Recommended Articles

This has been a guide to Absolute Reference in Excel. We discuss its uses and how to create Absolute Cell References, Excel examples, and downloadable Excel templates. You may also look at these useful tools in Excel –

  1. 3D Cell Reference in Excel
  2. Mixed Reference in Excel
  3. Excel Circular Reference
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Absolute Reference Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Absolute Reference Excel Template

EDUCBA

डाउनलोड Absolute Reference Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more