Absolute Reference in Excel – Shiksha Online

Absolute referencing is essential for creating robust and adaptable formulas in Excel. By understanding its uses and how to create it, you can ensure your calculations remain accurate and consistent throughout your spreadsheets. The article covers what is absolute referencing in Excel, how to create an absolute reference, and examples of absolute referencing.

You aim to achieve relative referencing when you copy a formula in the cells, making it the default behaviour of Excel. However, the objective might be to apply absolute references and not relative references. You will understand this by the example that we have mentioned below.

The absolute reference fixes a cell reference to an absolute, or we can say, a defined cell address. It always refers to the same cell and, therefore, does not allow it to be modified even when copying the formula.

Content

• How to Create an Absolute Reference in Excel
  • Absolute Reference Shortcut
• Example 1 – Absolute Referencing
• Example 2 – Absolute Referencing

Absolute references are important because they remain fixed, which is very useful when working with Excel functions or formulas.

How to Create an Absolute Reference in Excel

The absolute reference is created by placing the dollar sign ($) before the column letter and before the row number of the cell we want to set, for example: =$F$2

Absolute Reference Shortcut

To make absolute reference, we have several ways. One of them is by manually typing the dollar sign ($), or the other is by pressing the F4 key on our keyboard. The latter would be much faster and is recommended to avoid manual errors. For this, you just need to select cell F2 and press F4; the result will be $F$2.

Read MS Excel Tutorials

Example 1 – Absolute Referencing

To give you a more precise explanation of how to create an absolute reference in Excel, we will continue with the example. We will convert the value of the products in Dollars to Rupees. For this, we will put the exchange value or exchange rate in another cell, F2. 

We will put the change value in cell F2 and place that absolute reference so that copying does not alter the information. If we were to do this without fixing the cell, we would have the following result.

With this, we can validate that the information is not copied correctly. So that our example can have the expected result, we only have to fix the cell. This is where absolute references in Excel are crucial.

Make an Absolute Reference

Continuing with our example, we are going to locate ourselves in cell C3, and we are going to write our formula, which in our case, is a simple formula. We would have the following =B3*F2. Since we have not yet fixed cell F2, we won’t be able to make an absolute reference. We will use the formula  

=B3*$F$2.

It is also important to note that we are working with a relative reference that is B3, and at the same time, we have created an absolute reference. Now, we would only have to copy or drag so that the formula applies to other cells.

You can see that when we copied the absolute references formula in the rest of the cells, Excel made the correct calculation, and you get the desired results for all the entries.

Example 2 – Absolute Referencing

Absolute references are very popular in business data analysis, especially when dealing with fixed values ​​such as VAT. 

In this example, we aim to make a profit for a company. We have the sales data and monthly production costs. In addition, we also have fixed expenses per month.

In column B, we have the sales data; in column C, we have monthly costs. We will calculate the profit in column D. In addition; cell A10 contains the fixed cost that is repeated every month. The formula would be as follows: 

Absolute Reference formula

Sales – (Production costs – Fixed cost) 

In Excel, it would translate as –

=B2-(C2+$A$10)

The total formula combines relative Excel references and an absolute Excel reference.

When we extend the formula to the rest of the cells in the Profits column, we see how the result is applied to each cell. 

The relative references of the sale and the cost of production are adapted to each row, while the absolute reference of the fixed cost of the month remains fixed. 

Thus, in cell D2 the formula is =B2-(C2+$A$10), 

In cell D3, the formula is =B3-(C3+$A$10), until cell D7 =B7-(C7+$A$10).

As we can see, absolute references are used so that the content of a specific cell is not affected if we copy and paste the formula to another cell in Excel.

This is how we create absolute references in Excel. We hope that this article helped you to understand what is absolute reference in Excel.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel