MS Excel - Cell Reference

in this video we'll be looking for cell reference different types of cell reference like say relative reference absolute reference and some more advanced mixed reference let's see that I am on the sheet let's say relative reference I have some data let's say month the total income total expense and the net income I can get a net income which I subtract expenses from the total revenue how do I do that it's a very simple formula equal to I'll subtract cell number b3 - cell number c3 NL press Enter now when I press ENTER if I drag down all the cells the formula will be copied like this and if I just double click on any cell what is happening is it has taken the relative reference what it was done previously it has subtracted the b3 cell number from C b3 minus c3 similarly they have done as b8 - e 8 if you see here also b7 - c7 be 6 - c6 so as you go in the horizontal or vertical manner the formula gets copied accordingly so that is what is called relative reference but now let's see I'll give you a shortcut method how to go for it suppose I have selected this entire column imagine you have thousand cells and if you type a formula in just one cell you don't have to drag again so first step is you have to select all the cells type equal to cell number b3 - cell number c3 and you you don't have to press Enter you have to press ctrl enter so once you press ctrl enter you see the formula is copied in all the columns in short if I say in selected cells so I've selected like say 5 to 10 cells in all the ten cells the formula is copied so ctrl enter you can use the function or a shortcut key next I have that is absolute reference in this absolute reference if I use the formula I have some data if you see the name the grades and the percentage John has 85 Rita is 65 Neeta is 75 now if I want to calculate percentage out of 100 if you see 80 565 so I'll type here equal to say lamby 4 divided by cell number b3 and I press Enter now if I want to see the formula I have to press control tilt from my keyboard tilt if you see on the keyboard that is 1 2 3 on the top of it is written on the left hand side of the one you have this tilt key cursor so you have to press control tilt to see the formulas if you don't understand this shortcut key what you can do is I can't give you a long cut key how to show in height formulas you can go to this formulas tab and if you see on the right hand side there's a show formulas now if you keep your cursor on this place you can see the shortcut is given as control tilt that's a tilt option given here if I click on this hide formulas if I click on this show formulas now what I'll do is I'll just drag down this cell and see whether the answers are getting copied or not yes and I'll say yes show from hide formulas now if you see it's saying as 85% absolutely correct 76 it's something sounding not correct 115-113 112 if I have 95 scores so I should have 95 percent bits it's showing something wrong answer what is the problem here let's see let's find out if I double-click what is happening is it is dividing b8 by b7 that means it is taking the relative reference but I do not want b8 I want cell number b3 to be fixed what I can do is I can make selim of b3 as fixed how do I make it I'll just keep my cursor here when the cursor is blinking at this place and the red corner b3 press f4 from a keyboard so once you press f4 that cell has been locked and you can replace Enter key and now what you can do is you can just drag down the entire cells and see if there's a 95 I should get 95% if it is 85 85 % let's see what is the function copied or the formula copied I'll go to the formulas and click on this show formulas this is the easiest way by weekends we can see the formulas in entire sheet you see here that is b3 it's giving us b3 here it is b5 by b3 b8 by B 3 and so on so this cell I have make made as fixed so this dollar B dollar 3 is known as absolute reference I have written here for your reference absolute reference we have seen the relative reference absolute absolute reference let's see the next one we which we have that is what you call mixed reference a small table where I am showing your multiplication row headers 10 to 50 and the column headers are 1 to 5 what I want is I want to multiply a row with the columns and get the answer for example I want to multiply 40 with November I should get us one Wendy similarly fifty with five I should get to 50 how do I go for it I just keep my cursor in the cell number I have cell number c6 and n type of formula equal to cell number B 6 multiplied by cell number that say c5 and press Enter so I've got the answer 10 which is correct and if I drag down it's showing something incorrect answer let's see what is the problem here I'll go to this formula and I'll go to this show formulas now if you see what it is doing exactly is it is multiplying this cell Emma b6 with b5 correct b7 with c6 so it is going down so if you see it's a relative reference now what do I want is I want this row to be fixed okay and this column to be fixed similarly so what I'll do is I'll just go at this place and I'll keep my cursor on this c5 and press f4 from a keyboard and press Enter now if I drag down if I drag down the cell so it says the answer is coming absolutely correct so if you see this cells are fixed right if I press control tilt from a keyboard let's see the answer yeah so your answers like correct here but now the last one your last one I didn't drag it so it's not showing the correct answer I have dragged it so these answers are correct now what if I do is if I drag down on the right hand side right let's see what is the answer if you see the answer is not absolutely correct it should show as 50 into 5 which is 250 let's find out what is the problem I'll press control tilt from a keyboard and if you see if I keep my cursor here what it is doing is it is multiplying relatively the next cell that is c7 with this C $5 if I keep my cursor you just focus on the blue line this is the blue line and this one has the red line what exactly has to be done now so what I have to if you observe carefully in this column I want to multiply b6 b7 b8 b9 b10 right so if you observe the word the thing is B is common here so what I'll do is I'll lock the B column right so I'll first delete this and equal to this cell number and I'll lock this B column how do i lock it this way so dollar b 6y dollar b6 if you observe b6 be 7b8 benign so roles are changing 6 7 8 9 B is common and here if I do the second option multiplied by this cell number and if you see I want to multiply a c5 d v e5 f 5 g 5 so 5 is common in entire all the rows but if you see the row what you call the columns are changing so what I'll do is I'll lock the record so how do I log required press f4 f4 from a keyboard twice and it's CC dollar 5 wherever I put a dollar if I put a dollar before 5 that means the row is recorded that is locked now C is open c6 d c5 d v e5 that is entirely open that's all so that is the formula by which you can get the answer and I will press ENTER so I'll just drag down on the right hand side and I will drag down at the bottom right now I'm able to see all the formulas let's see if I hide the formulas by clicking on this show formulas now if you see 50 into 5 which is 250 if I double click you see the answers correct the red color is shown here that this cell is selected similarly the blue color is selected now similarly if I click on this cell if you see the red one and the blue one which is after you selected the mixed reference is a bit difficult but it's very easy to understand right and you can see your relative absolute mixed reference so that's a topic which you can go for it this is rarely used but morally we focused on the relative and absolute reference and here's the example for mixed reference similarly the columns are locked and the rows are locked so I hope you have understood here the how to go for relative reference absolute difference and mix reference that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 890,872

Keywords: cell reference in excel, cell reference in ms excel, absolute cell reference, excel dollar sign, ms excel in hindi, relative and absolute cell reference excel, mixed cell reference, absolute cell reference excel 2016, relative cell reference excel 2016, excel tutorial in hindi, ms excel formulas with examples, relative cell reference vs, cell references in excel formulas, cell reference formula in excel, microsoft excel tutorial, ms excel

Id: LFIykJmL4M8

Length: 8min 13sec (493 seconds)

Published: Sun Jan 14 2018