Absolute Reference In Excel

What Is Absolute Reference In Excel?

An absolute reference in excel is a type of cell reference that remains unchanged when copying or moving the reference to another cell or spreadsheet. It, thus, fixes the location of a specific cell.

The user must put the “$” symbol before a row and column coordinate that denotes an absolute cell reference. It also ensures the specific cell reference is constant when the user uses it in a function or formula in the same worksheet or an entirely different workbook.

For example, $C$2 is an absolute cell reference to cell C2. And when you use a formula referencing cell C2, ‘$’ maintains the same cell value for cell C2 when applied to a different cell. The table below calculates the total price per stationery of a school in cells D2 to D4 using the absolute reference of cell C2 in the multiplication formula.

When you copy the formula from cell D2 to cells D3 and D4, the cell reference to C2 remains constant in the formula, i.e., 20, as shown below.

What Is Absolute Reference In Excel?

An absolute reference in Excel formula ensures a cell reference remains static when moving or copying it into another cell, worksheet, or even a workbook.
The shortcut to applying absolute cell reference is selecting the particular cell coordinates and pressing the F4 key.
The ‘$‘ symbol before the row or column coordinate, makes the particular cell reference absolute, but it is called the mixed reference.
You can use an absolute cell reference in various formulas, from IF function to SUMIFS.

Shortcut To Absolute Reference In Excel

You can use absolute reference in Excel via a shortcut key, F4. When you press the F4 key, it makes a cell reference constant.

Here is how you can use the shortcut to apply absolute reference in Excel formula in the table from the previous illustration.

Step 1: Place the cursor in the required cell, D3. Enter ‘=’ and select cell B3. Enter ‘*‘ and choose cell C2. So, the formula becomes:

=B3*C2

Step 2: To use the absolute reference to cell C2, select C2 in the formula and press the F4 key. It will insert the ‘$’ symbol for the row and column coordinates. Thus, the formula becomes:

=B3*$C$2

Step 3: Press the Enter key to view the result.

Please Note: When you press the F4 key for the first time, it will add the ‘$’ symbol for both the row and column coordinates. The second time you click it, you will see the ‘$’ added to the row coordinate. And when pressed for the third time, the ‘$’ gets added to the column name. Finally, when you click it one more time, the ‘$’ symbol gets removed, making the cell reference relative.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Syntax

Assume you need to use the value in a cell, say B2, as a constant in another cell. Then, when referencing cell B2 in the required cell, the syntax will be as shown in the image below:

The ‘$’ symbol appended in front of the row and column denotes absolute reference in Excel, ensuring the specific cell remains unchanged whenever referenced.

How To Use Absolute Reference In Excel?

You may use absolute reference in Excel in the following ways:

1. Manually Entering ‘$’ Symbol Before the Row and Column Coordinates

Select the cell where you want to depict the output.
When typing the formula in the selected cell, add the ‘$’ symbol before the row and column labels for which you want an absolute reference so that they remain constant.
Press the Enter key to view the output.
Add the ‘$’ symbol for all required absolute cell references in the formulas you use throughout the worksheet.

2. Using the Absolute Reference In Excel shortcut

The steps to use absolute reference excel shortcut is as follows –

Choose the cell for displaying the result.
Enter the required formula.
Select the cell reference you wish to keep absolute and press the F4. It will add the ‘$’ symbol before the row and column coordinates.
Press Enter to view the result.
Drag and drop the formula to the desired cells.

In both scenarios, the absolute reference in Excel formula will ensure the value in the absolute referenced cell is the same in every cell where you use the formula.

Here is an example of absolute reference in Excel to help you understand the steps mentioned above. The image below shows a table of the prices (in $) of four stocks listed on the New York Stock Exchange (NYSE). Though the total number of units varies, the requirement is to find the cost per unit for each stock when the total number of units is 100.

The absolute reference in Excel simplifies the calculations. Create an absolute cell reference for the number of shares or units mentioned in cell C8, as it must be constant throughout the process.

Below are the steps to proceed with the calculations:

Step 1: Place the cursor in cell C2. Enter ‘=’ and select cell B2. Then type ‘/’, ‘$’, ‘C’, ‘$’, and ‘8’. The formula will be as depicted below:

=B2/$C$8

Step 2: Press Enter. You will get the result of 20 in cell C2.

Step 3: Next, drag the formula down from cell C2 to C5. The cell reference for the total price of stocks will change from B2 to B3, B4, and B5 in the numerator, but the cell reference for the price per share or unit in the denominator will remain constant, $C$8, as shown below.

Step 4: Press the Enter key. You will get the results in C3, C4, and C5 as 40, 60, and 80.

Please Note: The absolute reference in Excel ensures the cell value you intend to keep constant remains unchanged across the cells where you apply the required formula.

Moreover, your worksheet can have multiple cells with absolute references, depending on your requirements. You need to ensure the cell references have the ‘$’ symbol before the row and column labels.

Examples

Here are a few illustrations to help you understand the absolute reference in Excel more effectively.

Example #1

Sometimes, you may apply formulas with the ‘$’ symbol before a row or column at a time, depending on which one you want to maintain as a constant. Such scenarios come under the mixed references category, and here is one example of it.

Below is the image of three tables:

The first table shows a list of fruits and vegetables and their respective rates per Kg. The second table shows units consumed per month for the vegetables in Kg. Finally, the last table shows the total cost of onions for each of the units consumed per month in $, which you can easily calculate using mixed references without making any mistakes.

The steps to find the total cost of onions per month is as follows:

Step 1: First, place the cursor in cell B10. Enter ‘=’, ‘$’, type B2, enter ‘*’, and select cell F2. The formula will be:

=$B2*F2

Step 2: Next, drag the formula through C10:D10.

Please note that as we have ‘$‘ before column reference B in cells B10, C10, and D10, the cell reference to column B becomes absolute and remains unchanged in the specific cells.

Step 3: Press Enter to view the result.

Example #2

This example of absolute reference in Excel shows how you may use it in various functions, such as IF and SUMIFS.

The following image shows two tables, one with the attendance details of 5 students and the other for marks obtained based on the attendance percentage.

Column A shows the Student Names
Column B shows the Attendance in %
Column C is for showing the Total Score
Column F shows the Attendance Range
Column G shows additional marks for various Attendance Ranges.

The steps to find the Total Score for each student are as follows:

Step 1: Place the cursor in cell C2. Enter ‘=’ and select the IF excel function from the Logical functions in the Formulas tab. The open bracket ‘(‘ appears. Enter the condition by selecting cell B2 and entering ‘=‘ and 100 followed by a ‘,’ symbol. Next, provide the value for the TRUE condition, i.e., cell G2. The cell reference must be absolute. So, enter ‘$’, G, ‘$’, 2, followed by a ‘,’.

Then enter the FALSE condition value. Here it is an IF condition. Next, you may use the AND function to check the attendance range within the IF condition. For that, type IF and open bracket, followed by AND. For example, enter ‘(‘, select cell B2 and enter ‘>’, ‘=’, 50, select cell B2, enter ‘<’, 100, and enter ‘)’. Next, enter ‘,’ and the TRUE condition value, ‘$’, G, ‘$’, 3, ‘,’. Then enter the FALSE condition, 0. Finally, as there are three open brackets, type ‘)’ thrice to complete the command.

The formula in cell C2 is as shown below:

=IF(B2=100,$G$2,(IF(AND(B2>=50,B2<100),$G$3,0)))

Please Note: We write the IF condition like this since there are three attendance ranges (100, 50-100, and <50). And cell references to G2 and G3 are absolute as you do not want their values to change while using the IF condition to update the subsequent cells in the Total Score column.

Step 2: Press Enter. The result will be:

Step 3: Next, drag down the IF formula (in cell C2) through cells C3:C6.

You will observe the cell references to column B cells is relative; they vary through cells C3:C6. So, the respective values also change accordingly. However, the reference to cells G2 and G3 remains unchanged, even when copying the same formula in different cells. And, as you drag the formula downwards, the cells do not change to G4, G5, or G6.

Step 4: Press the Enter key to view the result.

Please Note: The absolute reference in Excel ensures you avoid mistakes while performing calculations involving fixed values.

Example #3

This illustration will show you how you may apply absolute reference in Excel when you have multiple worksheets.

Let us consider the previous example and split the tables into two worksheets. Below images show the two worksheets, Total Items and Total Cost Per Item:

The first worksheet has the data, such as the cost per Kg and total units consumed per item in January, February, and March. On the other hand, the second worksheet is where all the calculations happen to determine the total cost per item in $.

The steps to perform the calculations are:

Step 1: In the worksheet, Total Cost Per Item, place the cursor in cell B2 to display the result for the first item. Use absolute cell reference to cell B2 and relative cell reference to cell F2 from the first worksheet, Total Items.

Enter ‘=‘ and go to worksheet, Total Items. After the ‘=’ sign, the term-‘Total Items’! will appear in the command. Enter ‘$’, B, ‘$’, 2, ‘*’, and select cell F2 in the same sheet. So, the formula in cell B2 of the worksheet Total Cost Per Item will be:

=’Total Items’!$B$2*’Total Items’!F2

Step 2: Press the Enter key to get the output.

Step 3: Likewise, enter the required formula in cells B3 and B4, as shown in the below image:

Please Note: The cost per Kg for each item mentioned in column B of the worksheet Total Items is constant for each vegetable. So, for Onions, the cell reference to cell B2 is absolute, and thus you can see the ‘$‘ symbol before the row and column coordinate. It remains constant when used to determine the total cost of onions each month. The same logic holds for the other items, and hence the absolute cell references $B$3 and $B$4 from the worksheet Total Items in cells B3 and B4 of the worksheet Total Cost Per Item.

Step 4: Drag the formula to fill the values for all items across all months.

Important Things To Note

The absolute reference in Excel ensures the reference to a particular cell in formulas remains unchanged in a worksheet or a different workbook.
It helps you avoid using a varying value when using the value as a constant in the formulas.
You may use the absolute cell reference in functions like IF and SUMIFS.
You may even hold either a row or column label constant by appending the ‘$’ in front of the specified coordinate. It is called a mixed reference.

Frequently Asked Questions (FAQs)

What is absolute reference in Excel?

An absolute reference is a function that keeps the cell reference static. Users can apply it by inserting the $’ symbol before and after the cell coordinates. It ensures you use a cell value as a constant in whichever cell you use the specific cell reference.

How to do absolute reference in Excel?

When you want to use the absolute reference of a cell in any formula in a worksheet, append the ‘$’ symbol before the required row and column coordinates.

Also, if you wish to have an absolute reference of either a row or column label, add the ‘$’ symbol before the row or column, respectively.

When to use absolute reference in Excel?

One can use this function when they want a cell reference to remain constant in formulas in different cells of the same worksheet or even in other workbooks.

How to find absolute reference in Excel?

If a cell reference in a formula has a ‘$‘ symbol in front of its row or column or both, it indicates the cell reference is absolute.

Download Template

This article must be helpful to understand the Absolute Reference in Excel, with its formula and examples. You can download the template here to use it instantly.

Absolute-Reference-in-Excel Download

Absolute Reference In Excel