In this video we discuss what is absolute cell reference in excel, and what is relative cell reference. We go through a detailed example and show how to create an absolute cell reference
Transcript/notes
In excel, absolute reference is referencing a single cell all of the time. As an example, here is a worksheet with sales amounts in column A and commissions in columns B, and the commission rate in cell D2.
To calculate the commission amount, I left click on cell B2, then type in an equals sign. Next, I will left click on cell A2 and then type in a multiplication sign, shift 8 on the keyboard. From here, I will left click on the commission rate, cell D2. And to finish, hit the enter key and we have our answer of $758.63.
Next, to make things easy, I will go down to the bottom right corner of the cell, left click and drag the fill handle down to the bottom of the chart, then release the click and hold.
As you see, the other commissions are zero. This is because in our original formula cell, B2, we created a relative cell reference. If I double click on say cell B4, you can see that it is referencing cell A4, which is what we want, but it is referencing cell D4, which is not what we want.
To clean things up, I’m going to delete B3 thru B7.
To create an absolute reference we use dollar signs. I am going to left double click on cell B2. Now, in the formula I am going to left click before the D and type in a dollar sign, shift 4 on the keyboard. Then, I am going to go over in front of the 2 and type in another $ sign. Next, I hit the enter key. And now we are set.
Again, I’ll use the fill handle and pull down to the bottom of the chart, then release, and we have our commission amounts filled in.
Now, if I left double click on cell B4, you can see, it is referencing cell D2. And that is the basics on absolute cell referencing.
Chapters/Timestamps
0:00 What is absolute reference in excel?
0:10 Relative reference example
1:21 Absolute reference example