Updated August 16, 2023

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.

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.

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.

The result is $50, as shown below.

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.

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.

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

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.

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.

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

The result is seen below.

Drag this formula for the rest of the products to get their total prices.

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

The result is 20.

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.

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

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

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).

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

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

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.

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”).

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

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.

Things to Remember

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.
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.
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.
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.
Use named ranges: Rather than referencing cells in your formulas, consider using named ranges to make your formulas easier to understand and sustain.
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.
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:

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.
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.
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.
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.
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.
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

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.
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.
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.
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.
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.
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

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.
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.
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.
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.
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.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage