← Previous lessonFree lesson 2 of 9 · lesson 2 of 123Next lesson →

Unlock all 123 lessons of Medical Data Analytics & VisualizationFull course ₹40,000 · paid

Price & syllabus
Free lesson · 29 min · no payment

Relative, Absolute and Mixed Cell References in Excel

A free lesson from Skill-Lync's Medical Data Analytics & Visualization course, on Grow by Skill-Lync · Last updated 16 Nov 2026 · Session: “Relative and Absolute Cell References in Microsoft Excel”

What will you learn in this free Medical Data Analytics & Visualization lesson?

The lesson explains why A1 is a relative reference and $A$1 is an absolute one. The instructor builds a marks total with a formula, then multiplies by a fixed cell using dollar signs, and ends by describing mixed references such as $A2.

  • Writing a formula to add cell values
  • Relative references change when copied
  • Dollar signs lock a column and row
  • Mixed references fix only one direction

What comes after lesson 2?

The next free lesson is lesson 3, “Common Excel Shortcut Keys for Files, Editing and Printing” (13 min). Later in Skill-Lync's Medical Data Analytics & Visualization course (paid): lesson 4, Excel Formatting Techniques; lesson 5, Customizing the Quick Access Toolbar in Microsoft Excel; lesson 6, Data Analysis and Visualization using Microsoft Excel. The full course has 123 lessons (54 h 21 min of video); see the course overview or its price and syllabus.

Lesson transcript

Machine transcript of the lesson audio (lightly cleaned). Technical terms may contain errors; the video is the reference.

Okay everyone so next topic what we are doing here now relative and absolute cell references. So believe me it's this is very important topic and very interesting one. So you're amazed about knowing like how actually this Microsoft Excel can actually help us. So let's try this. So what is relative and absolute cell reference? So first we will see or first we will learn about relative cell references and then we will come to absolute one. So what is relative and absolute cell reference? So when we are talking about A1 this is called as relative cell references and when we are writing something this $A, $1 so this is known as absolute cell reference. So we have total four number of ways to do cell references that is available with Microsoft Excel.

So let's come to the first one first. So let's come to the first one first. So let's come to the first one first. This is that is relative cell reference. So once you are done with relative cell reference we will go to absolute one. So I'll come to my Excel sheet. You remember we entered the first one first. So now what I am doing. So now what I am doing I want to make some changes. So I actually want to do I want to make entry in the total 20 plus 30 plus 40 that I want in this particular cell.

So what is the value of this cell? So what is the value of this cell? The cell is known as G3. You will not call it 3G. You will call it G3. So this is first the name of the column and then the number of the row. So if I am talking about this cell. So if I am talking about this cell is known as H2. So in this way we can address the particular cell. This particular cell addressing is done here in the Excel by including the predefined column name and using the row number.

So what I want to do I want to do I want to actually make some addition here using physics mathematics and chemistry here. So I want to have the total marks with me. So what I will do just understand this. So I'll I want to add 20 plus 30 plus 40 40 plus 60 plus 40. You don't need to open calculator and doing the addition here and after doing the addition you enter here. This is not the way Microsoft Excel is with us. So we don't need to worry about this calculation part. So what I will do I click on the cell either you do the F2 or click on the cell double click write equal to here.

Once you are writing the equal to now. Now you tell the Excel which cell value you want to add. So what I want to add I want to add this January's mark. This is my student. So I want to add January's physics maths and chemistry marks and what I want to have that total in the G3. Okay. That is the case. So what I will do I will write here D. I will make it capital for now. There is no difference in that but for you know for the simplicity I want to make it use the capital D.

D and D3. So what is D3. So once you are writing this D3 you can have this kind of selection here. Okay. So this is known as reference. Reference of that particular cell that is D3 plus which cell value I want to add here E3. This E3 I will write here again E3. In that case I am again selecting that mathematics marks. Again I am adding something that is chemistry's marks. So this is F3. I will write here F3. Once I am done with the getting the exact number of the cells. Exact value of the cells. I will click on enter button.

So once enter is clicked you are seeing here 90. So how it is happened. This Microsoft actual Excel worked according to your instruction. So that instructions how it is happened. So it actually added the cell values. Three cell values. These were D3, E3, F3 and you got the result over here. I will click on that double click. Then you can see the formula here or another way you just click on that and see on the formula bar. So if you are seeing on your workbook there you can see the value actually.

And if you are going to formula bar. This formula bar is showing this. Which formula has been used for calculating the values. That is 90. This is equals to D3 plus E3 plus F3. If I click on this 40. 40 is a normal value. No formula is associated with this value 40. So we can see this normal 40 in this formula bar. Another way what I can do. I can see the formulas here also. Just see it carefully. Click on the formula tab. Okay. So we are here. And then look for show formula. Go to the ribbon formula auditing.

And click on show formulas. So once I click that. So whatever available is the width the value that is shown in the form of the values. And the formula will be displayed here. If you want to write it here a normal value you will see the normal value. If I am writing here the formula then I can see only the formula not the value. So it is showing the workbook with the formula. I can just deselect this again. So I can have a normal view with the values. Whether it is available with the particular formula or it is not available.

You can actually see the value. And you can use the shortcut also. The shortcut key is control plus the symbol of tilt. And you can actually see the formulas. So if you are not you know comfortable seeing the formula in the formula bar. You can see in the workbook as well whenever you want to see. You can do it by going to formula tab. And going to the ribbon formula auditing. And click on show formulas. Or you can use the shortcut key also. That is control plus tilt.

Okay. So we got the formulas here. Now what I want to do. I want to use same kind of addition for another cell also. So same thing you need to do. So you need to write the formula. Okay. Let's try that. So this time I have the value D4 plus E4 plus F4. I click on the enter and I will get the addition again. This time it is 140. Okay. That is fine. So if you have the records for 10,000 students. In that case. If you need to write the formula every time. So it is better to calculate it in calculator or do it with some another way.

Yeah. That is very right. But this Microsoft Excel is so smart to do that. You can actually copy the formulas as well. So what I am doing here. Now just see it carefully. I went to that cell. The first cell where I already provided the formula. The formula was D3 plus E3 plus F3. That is the same formula you want to have for another cells also. That is G4 and G5, G6, G7. Yes. The same kind of formula I need that. But this time the values are G.

What I will do. I will go there and I will hover my mouse on the boundary areas of that cell. And I will wait for changing the symbol this to this. And I will stretch it down. So once I am stretching I got the result. Is it right? So just see the formula. What I will do. I will go to the show formulas. So are we got the same kind of the formula? Yes. You just see this this time D4, E4 plus F4, D5 plus E5 plus F5, D6 plus E6 plus F6 and similarly D7 plus E7 plus F7.

So Microsoft Excel is not only recognizing the values pattern. It is actually recognizing the formula patterns as well. So whatever you have given accordingly change the formula and got the result. So you can do it for n number of the records here. Whatever you want to do. You want to have records for 100 students. Yes. You can do it with the formula. Just you need to detect the same kind of pattern you want to do. And accordingly you can change the values. So in this way you can have the result.

So this kind of referencing is known as relative cell lip. This is known as relative cell reference. Why it is called as relative cell reference? Because the reference is provided for every cell relatively. Relatively means you are dependent on another value and that is the previous one. In this case we stretch the copy or the properties of this cell, this particular G3 to other values as well. This is G4, G5, G6, G7. And accordingly you got the result. This is known as relative cell reference. So similarly you can have it for multiple records also.

And normally it is used, you know, in demanding nature when we are working with the Microsoft Excel. So I will come back to the normal display. I will not see the formulas here again. Okay, so I can... Now, the output like this. I am just doing some formatting by doing the part for the column width. When fixing, giving the column width. Now, another case need to talk about the absolute cell reference. So, in the absolute cell reference, what happens? Now, let's understand this with the example. I have the total width P. So, maybe we want to make it multiple of 5.

I want to multiply this sum 90 multiply by 5, 140 multiply by 5, 190 multiply by 5, 240 multiply by 5, 290 multiply by 5. So, in this case, what is my requirement? My value G5, this G2 is fixed. This is my fixed value. For that fixed value, I want to have multiplication result for the n number of the n number of n number of values. So, this n is how many values we have? We have total 5 values. So, what will be the formula? So, this is F3. Okay. So, when we are doing the multiplication, we have the asterisk sign with us, we have the asterisk sign with us. Okay. This is for the multiplication. So, you want to multiply it with what? The fixed value which is placed in this particular cell that is G2. So, I want to multiply it with G2. So, this is again selected. I will click on the enter. So, we got the result. Very perfect. And now, similarly, you want to do it with next cell. Next cell also that is G4. So, same formula. What will be the formula now? I will mark it. I can write it here again. That is F4 multiply by a value is G2. So, I can have the result here. But, I don't want to do it. I want to do cell referencing again. So, what I will do, I will go to that particular cell and the same manner, I will just stretch it to down. We got the results. But, whether this result is right or not. Or, we can check that. I will go again to the show formulas. This time, let's try the shortcut. I am doing control plus tilt. So, I can see actually the values here. Now, just understand the result here. So, what has happened? F3 is multiplied by G2. Okay. G2 is this one. And then, F4 is multiplied by G3. F5 is multiplied by G4. F6 is multiplied by G5. F7 is multiplied by G6. But, what was our requirement to do? Our requirement was to keep this. F5 is a fixed. So, every time, I should have G2 here. Not the G3, G4, G5, G6. But, when we copied the cell reference, in that case, it is actually having the changes accordingly as we had in the previous. We had for C3, D3, E3. We had for C4, D4, E4. We had for similarly for the 7th row as well. And similarly, we are having when we are doing the cell reference. But, our case is not the same. Do we need to do separate kind of editing of the formula? No, we don't need to do. In that case, we have the another type of cell reference that is known as absolute cell reference. This is known as absolute cell reference. So, this is absolute cell reference. When we are keeping something fixed, in that case comes to absolute kind of referencing. So, how I can keep this fixed? For that, I need to use another type of referencing, which we were talking about, this $1. So, but, in that case, what do you want to make fixed? You want to make fixed this particular value. So, let's use that. So, what will be the formula here? I am making the changes in the formula. I don't want to make it change. I want to change, make changes in F3. Yes, these changes are allowed. So, I want relative cell reference in the first case. And, in the second number, I want the fixing. Fixing is allowed with the absolute cell reference. So, we will multiply two numbers by using relative cell reference in the first number and the absolute cell reference in the second number. So, we want to keep this changing and keeping the another number as absolute. So, what I will do, need to make it absolute, click on $ and again $. So, now I am making these as $G and $2. So, G2 was the number, but what I have used, I have used two dollars here. One is before the column name and another is before the row number. In that case, what I am doing, I am clicking on enter and same I will copy with the other cells also. So, now you just see when you stretch that row part, when you stretch that formula part, now you are getting the result. This F3, F4, F5, F6, F7 is changed because we have used the relative cell reference and this G2 is fixed. How it is fixed?

Because we are using absolute cell reference here. So, let's see the result. This time we are having a correct result because 90 multiply 5 is 450, 140 multiply 5 is 700, 190 multiply by 5 is 950, 240 multiply by 5 is 1200 and same 290 is multiplied by 5 is 1450. You got the result? So, we learnt about relative and absolute cell reference. In relative cell reference, we want to make changes according to the detected pattern. And in the absolute cell reference, we are keeping it the same for making changes in the formula. So, in this way, you got the result here. Let's see the formulas once again.

Let's try it with the shortcut that is control plus tilt and you got the result. And what else you can do if G2 is written and once come to this starting of this G and click on function F4. So, when you are clicking on F4, this is a shortcut. That time you don't need to enter $ again. Automatically Excel will do that. So, if you want to have absolute cell reference, what you can do? You can actually write that particular cell. Suppose I am writing here G4 and I want to make it absolute cell reference. What I can do? I can click on function F4 key and I can have the absolute cell reference here. Automatically, I don't need to use a $. So, for using absolute cell reference, what you can do? You can click on $2 as well or you can go with the function F4 key. So, this was the case of relative and absolute cell reference. Let's understand this time we have only fixed that particular cell. You have the situation where you need to maybe you need to fix the row or you need to fix the number of a particular column name. In that case, what we need to do? We need to use a single kind of cell referencing that is a part of again absolute cell reference. But this time you are using the way A$1 or $A1. So, how we can use that? Let's try that also. I'll take one more example. One easiest example so you can have the clear understanding of what we want to do with the mixed cell referencing. And believe me you guys, this is very important topic and very useful topic and very easy to do also. So, this time my case is I want to have multiplication. I want to multiply 10 with maybe I'll just with 500, 15, 20. So, this is my case. In fact, I can take another sheet also. How I can take? I can click on plus and get the new sheet. We will learn about this in more detail in next upcoming slides. For now, you just learn that how after clicking the plus sign, I can have the sheet too. So, I'm working in the same document but working in another sheet. You can actually rename it also. We will learn that also. So, this time my question is I want to do some mixed kind of reference absolute and the relative one. So, what I want to have I want to multiply 10 with 500, 15 and 20. So, this is my case. Now, in that case, I want to have formula here as well here, here as here. So, this time my value will be fixed here but every time I'm changing the value for the different columns. In fact, I can have it 20, 30, 40. So, this 10 should be multiplied by 5. 10 should be multiplied by 100 writing it here. 10 should be multiplied by 15 writing it here and similarly 10 multiply by 20. So, what is our case? Let's understand the question as well first. So, what I want to make the changes now. So, I want to multiply here 10 multiply 5. 10 multiply I'm just writing here so that you can understand the question. Then 10 multiply by 15.

10 multiply by 15. 10 multiply by 15. 20 x 5, 20 x 100, 20 x 15, 20 x 20. Another row, 30 x 5, 30 x 100, 30 x 15, 30 x 20, 40 x 5, 40 x 5, 40 x 10. So this is my question. How I can do that? So I'll just write it here so that I can understand it. I'll copy it down. Copy is ctrl plus c. I'll copy this column also. So now I want to have changes in the formula. So what will be the first case? The first cell value. So I want to have this kind of multiplication. What I'll write here? I'll write equals to and the case is c12. C12. Okay.

So this time I'm just using a normal referencing. So I'll come to the formula, show formula. So this is the formula is c12 is multiplied by d11. Yeah. This is the thing we wanted here. This is the thing we wanted here. This is the c. Now I'm copying this to another values. So let's see after stretching what is happening. This is making changes according to the relative reference. That is c3 and d are actually copied according. So this is now I am making changes in c12, c13, c14, c15. So that is fine. But this time this d11, d12, d13, d14 is also changing.

But I want to make it fix. But I want to make it fix. But for this particular column not. And I just don't want to make the changes like that as we are changing here. So we have learned here about absolute cell reference. So I'll try that absolute cell reference as well here. I'll use the function f4. So I want to fix that something here. So what I have done? I have fixed the this 5. This d11 I fixed. I click on enter and I'll make the changes.

So when I am making the changes is the answer is correct? Let's try that. 10, 20, 30, 40. So c12, c13, c14, c15. That's perfectly fine. Are we multiplying it? Are we multiplying it? 5. That is d11? Yes. We are doing it with the d11. So we got the correct answer. Now same thing I want in another column as well. So I'll make this stretching. So when I am making this stretching, what is happening now? Is this is the answer which I wanted here? So we got the result here d12. D12 is what?

D12. Are you want to make the changes like this? No. Now again we are fixing something that is this the value as d10, 20, 30, 40. So now the case what you are understanding here. In this thing you want to make the column as the same column. You want to keep it as same column but you want to make the changes in the rows. When we are talking about these rows, these values. And the another case you want to make changes in the same row but not in the, with the, when we are talking about the columns.

So now when we are talking about first value, when we are talking about first value, first value should be changing the rows but not the columns. When we are talking about the second value, then it means we are, what we want to make changes, we want to make changes in the columns but not in the rows. So this is the case of the mixed reference where you cannot use one particular reference and you need to make the mixed reference. So now understand the second case. First case you want to keep the, when you want to keep the change same, you want to keep the same columns.

You want to make it, make the changes in the rows. So how you can change the rows or the case you can understand. This is the first value case. What you want to have? You want to have a value, a reference where column does not change. Where column does not change. Where column does not change. So what I will do, I will use single dollar here. I will use single dollar here. I am typing here dollar, dollar sign. But you will not use the F4 key here.

You will write here single dollar. So single dollar with only the C. So this is the case where you want column does not change. I will write it again. So this time I am just fixing the, what I am fixing, I am fixing the column. So when you want to fix something, so you just want to fix the column here. Okay. In the second case, in the second value, what you want to fix? You want to fix the row. And when you want to make changes in the row, what is the row here first?

The row is D11. Yes, that is D11. And in that D11, what kind of reference you want to do? You want to fix the row. And that is when you want to fix something, just put the dollar here. So this is the case. Now I will put enter. And I will copy this here by stretching. Similarly, I will copy. Same with another fields as well. So now you just see the result. So this is C12, C13, C14, C15. Yes, this was the case. And for the another value, you want D11, D11, D11, D11.

That is fine. And again, when you are doing it with the another number, and this is the perfect result, you want to do it with the, your value. So this is called the mixed cell reference, where you are not completely fixing one particular value. Either you are fixing the rows or you are fixing the columns. So it depends on your case. So how you are using, there are two ways to do that. When we are putting the values like, I will write it here one example.

When you are writing something like, A$2, in that case, row does not change. Row does not change. Another case is, when we are talking about mixed reference, we have $A2 maybe. In that case, column, the value for the column does not change. So you need to decide what kind of cell referencing you want to use, what is useful for you. You want to use normal referencing that is allowed relative cell reference that we used here. You want to have mixed cell reference or you want to have fixed cell reference.

If you have something fixed value, in that case, you will use complete fixing of the rows and columns. If you want to make changes in particular direction, either it is row or column. Accordingly, you will choose row or column wise referencing. For this kind of referencing will be used where row does not want to change. This kind of referencing will be used where you don't want to make the changes with a column. So column and row don't change where relative reference is the default cell references in Excel.

It is simply the combination of column name and the row number without any dollar sign. And when we are using dollar sign, if we are using for both rows and column, in that case, it is used with the text value. And accordingly, you can choose row versus column as. So I hope this is clear to you.

Want the rest of Medical Data Analytics & Visualization?

Get the full course syllabus and fees.

The full course is paid (₹40,000). We'll send the syllabus and call you back within one working day.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.

Full course · paid

Unlock the full Medical Data Analytics & Visualization course

The remaining 114 lessons, mentor-reviewed projects and a certificate come with the paid course (₹40,000). Leave your details: we'll send the syllabus and fees and call you back.

Get the syllabus and fees

A counsellor calls you back within one working day.

By submitting you agree to our Terms and Privacy Policy.

Grow by Skill-Lync · free lesson

Start watching

Free, no payment. Leave your details once and every free lesson on Grow plays straight away. A Skill-Lync counsellor may call you about the full Medical Data Analytics & Visualization course.

By submitting you agree to our Terms and Privacy Policy.

Talk to a career counsellor

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.