Absolute Cell Reference in Excel

January 07, 2020 Excel tips,

Absolute Cell Reference in Excel

In my personal opinion, your understating level of references in excel will decide how quickly you can understand the working style of Excel software. If you are quick enough to understand the reference styles in excel probably you will go long way in excel.

However, in Excel, it is not just referenced, we have absolute, relative and mixed references (but what-is-this-stuff)

Excel worksheets made up of rows and columns, which contains cells. Each cell represents the particular column and particular row. For example, cell A1 refers first column (A) and first row (1). Similarly, C15 refers third column (C) and fifteenth row (15).

The real advantage of Excel is in the usage of these cell references in other cells when creating formulas.

You can use three types of cell references in Excel: relative, absolute, and mixed. Using the right type of references could save you tons of work time and make you feel like a hero.

Relative Reference
Absolute Reference
Mixed Reference

In this article, I will cover the Absolute Reference in Excel.

When we work with formulas, we usually work with cell references and making relative reference, absolute references and mixed references are very pivotal.

=A1 means a relative reference. Both the row and column changes when we copy the formula cell.
=$A1$ means an absolute reference. Both the row and column are locked when we copy the formula cell and remains constant.
=$A1 means the only column is locked and row keeps changing in that particular column.
=A$1 means only row is locked and column keeps changing in that particular row.

The Absolute Reference Shortcut key to insert those important dollar signs is F4.

Absolute Cell Reference

Relative Reference keeps changing along with moving cells but Absolute Reference in Excel does not change at all. However, the tuning you need to do here is you need to lock the cell completely.

In Absolute Cell Reference, a dollar sign ($) means that a particular column or cell is locked. You can only lock either a column or cell. If the dollar symbol is in form of an alphabetic means column is locked and if it is in front of the serial number that means row is locked.

Using Absolute Reference in Excel

Below are some of the practical Absolute Reference examples..

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

Absolute Reference in Excel – Example #1

Assume you have a data, which includes hotel costing for your project and you want to convert the entire US dollar amount to INR at 72.5 US dollars. Look at the data below.

In cell C2, we have our conversion rate value. We need to multiply the conversion value to all the USD amounts from B5:B7. Here the C2 value is constant for all the cells from B5:B7. Therefore, we can use an absolute reference in excel.

Follow the below steps to apply the formula.

Step 1: Type equal (=) sign in cell C5 and apply the formula =B5*C2. As soon as you give reference to the cell C2 type F4 once.

This would do the conversion for the first USD value.

Step 2: Now drag and drop the formula to the remaining cells.

Look at the dollar symbol for the cell C2 ($C2$) that means the cell C2 is absolutely referred. If you copy paste the cell C5 to the below cell it will not change. Only B5 will change to B6, not the C2.

However, in Relative References, all the cells keep changing but in Absolute Reference in excel whichever the cells locked with the dollar, the symbol will not change.

Absolute Reference in Excel – Example #2

Now let us look at the absolute cell reference example of absolute along with mixed references. Below is the sales data across months for 5 salespeople in the organization. They sold multiple times in a month.

Now we need to calculate the consolidated summary sales for all the five sales managers in the organization.

Apply the below SUMIFS formula to get the consolidated all the five people.

The result will be:

Closely look at the formula here.

First thing is our SUMRANGE, we have selected from $C$2:$C$17. A dollar symbol in front of both the column and row means it is an Absolute Reference.
The second part is Criteria Range1, we have selected from $A$2:$A$17. This is also Absolute Reference
The third part is the criteria, we have selected $E2. That means the only column is locked when you copy the formula cell only thing that changes is the row reference, not the column reference. Irrespective of how many columns you move to the right, it always stays the same. However, when you move downwards the row numbers keeps changing.
The fourth part is Criteria Range2, we have selected from $B$2:$B$17. This is also an Absolute Reference in Excel.
The final part is Criteria, here the cell reference is F$1. This type of reference means row is locked because the dollar symbol is in front of the numeric number. When you copy the formula cell only thing that changes is the column reference, not the row reference. Irrespective of how many rows you move down it always stays the same. However, when you move to the right side the column numbers keep changing.

Play With Reference from Relative to Absolute or Mixed Reference.

We can change from one type of reference to another. The shortcut key that can do the job for us is F4.

Assume you had given a reference to cell D15 pressing the F4 key would do the following changes for you.

If you press F4 only once, the cell reference changes from D15 to $D$15 (becomes ‘absolute reference’ from ‘relative reference’).
If you press F4 twice, the cell reference changes from D15 to D$15 (changes to mixed reference where the row is locked).
If you press F4 thrice, the cell reference changes from D15 to $D15 (changes to mixed reference where the column is locked).
If you press F4 for the 4th time, the cell reference becomes D15 again.

When copying formulas in Excel, absolute addressing is dynamic. Sometimes you do not want cell addressing to change but rather absolute addressing. All you need to do is make the cell absolute by pressing the F4 key once.

A dollar sign is all about! If you copy this cell from place to place, the formula will not move with it. So if you type =$A1$+A2 into cell A3, then copy and paste that formula into cell B3, one cell automatically change but the other does not change, =$A1$+B2.

In Absolute reference in Excel, each referred cell will not be changing along with the cells you move either left, right, downward and upward.

If you give an absolute cell reference to cell $C$10 and move to one cell down it won’t changes to C11, if you move one cell upward it won’t change to C9, if you move one cell to the right it won’t change to D10, if you move on cell to the left then it won’t change to B10

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1