Updated June 12, 2023
Part -11 – absolute and relative references
Download Excel Templates – absolute and relative references
Excel reference – percentage and absolute references (without solution)
Excel reference – percentage and absolute references (with solution)
Transcript For The Video – percentage and absolute references
Video on “percentage and absolute references”: In this set of video tutorial let us try to calculate the percentage of annual salary which is drawn by individual employees. So Percentage of total annual salary which is drawn by Bruce or Jacob or Melvin that basically what we want to do in this set of video tutorial. So ideal way of looking at this is basically we are talking about Bruce’s salary which is 30,000 US dollars divided by the overall salary which is drawn by total employees. So in this case if we have to use the division formula we will start with equal to as usual as we have learned in the earlier videos we will click on G4 so that 30,000 US dollar is selected and we will choose the division sign and we will click on the total salary column which is D24 and we press enter. So with this what we will find is this number is in decimals and this decimal can be converted into percentages which we have learned earlier. So this can be expressed in the no decimals we can increase this decimal by 1 or 2. So basically Bruce is drawing 2.63% of the total salary. So that’s what we have done for Bruce. Now if you have to do the same thing for Jacob we will have to retype the formula which is = G5 / G24 so this turns out to be 0.04412 and when expressed percentages and the extending the decimal point this is now =4.41. Now earlier if you remember we also were trying to calculate the sum total of the monthly and annual by copying this formula here and pasting the same formula in the next cell. So this had worked well for us because we wanted to the same formula to be copied and pasted across. So why can’t we do the same thing here so if we have done this for Bruce separately why there is a need to calculate the formula separately for Jacob. So let’s try and figure out what probably comes out in terms of final numbers. So lets calculate the percentage of annual salary for Melvin and we right click and copy and we paste it and what we find here is that if he go into the cell we find that it is G6 which obviously is Melvin’s annual salary. However in the denominator you can see you know the denominator has changed by one row. It should have ideally taken total salary however it has taken maximum salary so I’ll press ESC to come out of this situation. And what we find is 100% which is obviously the wrong percentage of total salary. So probably we are missing here something. So the idea here is that if you copy this formula from Jacob cell and paste it across what happens is it the G5 changes to G6 and G24 changes to G25. So all the cells shift by 1 row that what we were kind of doing. We have to really avoid a situation where instead of G4 if we had this as G24 we would have been able to calculate the overall formulas for the other employees as well. So let us see you know how excel has designed the set of function in order to help us achieve this goal. So for this I will take you to sheet 4 and we will learn further about the referencing. Now we have done very normal referencing as of now let me type in some numbers 1, 2, 3 and 4 what do we understand by normal reference? A normal reference I want to link let say I’ll press = and I want to show in cell A7 whatever is inside cell A3 so the moment I press enter it shows me number 1. Which is inside the cell A3 so this is the normal reference equals to cell A3 so we are designing this so that it should be a mirror image? We can copy this function this is normal reference you can copy this reference and paste it. What we see is that this reference has shifted from A3 to B3 because we have shifted this formula from cell number A7 to cell B7 so it automatically takes care of the additional column shifted now let us shift this number one from row 7 to row 8 let us look at what happens when we copy this and paste it. So we find that it also shifts one row from A3 it shifts to A4 so what we essentially find is that it is really following the number of movements which we are doing in terms of normal reference so this is basically about normal reference. Let us understand what is meant by absolute reference? Now absolute references I’ll first show you how to, come to absolute references then I’ll comment on that. For absolute references we need to press = and let say this is the number that we want for absolute references what we mean is just have $ sign in front of A and type a dollar sign in front of 3 and press ENTER. So at this stage nothing changes here it is still referencing cell to E3 but now we have $ sign in between now let us see what happens when we try to copy and paste this formula from D to E so I am trying to do that and the moment I do it I find that this reference is still pointing to the original location which was A3 it has not moved from A3 to B3 like in this case it moved from A3 to B3. However In this case it is not moving from A3 to B3 However it is still pointing to the original location. Likewise let me copy, right click copy this and paste it. 1 row below the original location and what I find now is that it is still pointing to the original location. Let me copy and paste it in some random location let say G2, still points to the original location so what is the advantage of absolute references is that if you are using a term which let say needs to be referenced or anchored and you don’t want that cell to move you can use absolute references and lets go back and move back to our case study and see how it can be applied here. So let’s go and look at Bruce so Bruce percentage of total salary was G4/G24 so I want G4 to move when I move from Bruce to Jacob each time I change the formula it should move one row down. However when it comes to the denominator which is the total salary. This total salary number should be fixed so coming from our previous experience about what we have learned in absolute references we can put a dollar sign in front of G and we can also put a dollar sign in front of 24. So the moment we do that nothing would change especially for Bruce nothing changes however we should be in a position to copy this formula. Let say we do a right click and we copy and paste this formula so we find that numerator has changed from G4 to G5, however the denominator is still the same which is total salary likewise let us do the same thing for Melvin, right-click paste it and this time we find that it is giving the correct Answer. As G24 is anchored because of absolute references so what we can do is we can again use a shortcut we can just use the + sign which is at the bottom right-hand corner of the cell and drag it till the very bottom and you will find that all the formulas for the percentages are calculated automatically so this is how you know you can use absolute references.
