What Is an Absolute Reference in Excel?

During your IT interview, you may be asked to talk about an absolute reference in Excel. Let us tell you the main facts about absolute and relative references so that you know the difference. This will help you provide a complete answer to the interviewer and ace your interview.

Relative and Absolute References in Excel

In Excel, there are two main types of references: absolute and relative. Here are the core differences between the two:

• Relative and Absolute references have a different behavior when they are copied and filled to other cells in your sheet.
• Absolute references remain the same when they are copied.
• Relative references change when they are copied to other cells.

The Main Facts About Excel Relative References

If you want to provide a complete answer about absolute references in Excel, you will have to know the main facts about Excel relative references as well. Pointing out the differences will help you impress your interviewer.

• All Excel references are relative references by default.
• They are automatically adapted when copied to other positions (other rows and columns).
• Example: Let?s say you have the formula ?=A1+B1?. If you copy it from row 1 and paste it to row 2, it will automatically become ?=A2+B2?.
• Relative references in Excel are extremely convenient if you need to repeat certain formulas for various rows and columns.

The Main Facts About Absolute Cell References in Excel

Now that you know all about Excel relative references, let us tell you the main facts about absolute cell references. The main difference between absolute and relative references in Excel is that absolute references will not modify their structure or behavior when moved or copied to another cell. Therefore, they are uses to keep rows and/or columns constant.

• To define an Excel cell reference as absolute, all you need to do is add a dollar sign ($) in the formula.
• The dollar sign may be placed before the row reference, the column reference, or both of these.

A$3 (the dollar sign is placed before the row reference) ? This row does not change when it is copied.

$A3 (the dollar sign is placed before the column reference) ? The column does not change when it is copied.

$A$3 (the dollar sign is placed before the row and the column reference) ? Neither the column or the row change when they are copied.

As you can imagine, you will most likely have to use the $A$3 format most times because you will need both the column and the reference to remain the same.

Absolute Reference in Exel – An Example

To help you get an idea of what an absolute reference in Excel is used for, we decided to take an example. This is a classic sales tax table. Our aim is to calculate the sales tax for each item on the list. While the price is different for each item, the rate of the sales tax remains unchanged.

Step 1.

Select the Excel reference where you want the product sales tax to be calculated. In our example, this will be D3 for the Peppermint Tea item (A3).

Step 2.

Create the formula that will calculate your sales tax. In our example, this will be ?=(B3*D3)*$E$1?, in which B3 and D3 are relative references and E1 will be an absolute reference because the rate of the sales tax is identical for all items.

Step 3.

When the formula is used, the sales tax for the Peppermint tea is calculated according to the formula we have used.

Step 4.

We can extrapolate the formula for all the items in our list. Because we have established the E1 reference as an absolute reference in Excel, it will remain the same for all the items. As for the other references in the B and C columns, they will be adapted automatically to the formula because these are relative references.

To extrapolate the formula, all we have to do is click on the fill handle on the D3 cell, where the output of our formula has been moved, hold and drag it all the way down to D12.

Step 5.

When we release the mouse, the sales tax will be calculated according to the formula we have provided. The E1 cell will remain unchanged because it is our absolute reference in Excel.

Step 6.

To make sure that the E1 cell has remained an absolute reference in Excel, we can click on any of the cells between D3 and D12 to check the formula. While the B and C cells have been adapted to fit their location, the E1 cell has remained unchanged.

Now you are ready to answer any absolute reference in Excel question on your IT interview. We hope the information here will be helpful to you. Good luck on your job meeting from the IT Interview Guide team!

Image Source:?Wikimedia