Hi, this is Wayne again with a topic “Excel Quick Tip: F4 for Absolute Cell References”.
In this microsoft, excel quick tip, i’m going to show you how to very quickly toggle between relative cell references and absolute cell references, and here in this excel spreadsheet. We have an employee list, including salary information, and let’s say the company has approved a bonus for each employee and let’s say that the bonus percentage is 2.4, so i’ll enter that here tap enter. Now, let’s calculate the bonus for each employee. I click on cell f2.
Tap equals. I want to multiply the employee’s salary in this case gina’s salary by the bonus so e2, in this case asterisk and then g2 and then i’ll tap, enter on the keyboard. There’S her bonus.
Now, if i click here on cell f2, i can double. Click on the auto fill handle the little green square in the lower right corner, and it applies that formula down the page. Now, why didn’t it work? The reason it didn’t work is because this cell reference is relative, in other words, in this formula, we’re referencing cell e2, but it’s relative.
If i copy this formula, e2 will change to e3 and then e4, as i copy the formula down the spreadsheet. The number two keeps changing to three four five, all the way down. Now, that’s actually exactly what i want. I want the salary, that’s getting multiplied to change as it goes down the spreadsheet, so that makes sense. But what about g2? This cell reference is looking here at cell g2, but it’s also relative, so g2 becomes g3 and there’s nothing there. So when 74 322 is multiplied by nothing, it produces nothing.
What i’d really like to do is to make g2 an absolute cell reference so that it always references this same exact cell g2 and it does not change as it’s copied down the spreadsheet. Probably the easiest way to do that is when you’re typing the cell reference or if you’re gon na click – that’s fine, too after typing or clicking to get the cell reference simply tap the f4 key on the keyboard. It adds dollar signs in front of the g and also the number two. Those dollar signs make this an absolute cell reference. The g is not going to change.
The two is not going to change as i copy this down the spreadsheet, so let’s tap enter and try it out now. If i double click on the autofill handle to copy the formula down the spreadsheet, it works perfectly. I can check this just by double clicking here. You can see that bob’s bonus is based on e 19 as it should be, but his bonus percentage is still tied to g2 because that’s an absolute cell reference.
It’S still referring to this cell, i’m going to undo this just to point out that with f4, if i had tapped it again, it would have taken off the dollar sign off of the g, but left it on two. If i tap it again, it puts it on g, but not on two, and so in this example. Having the dollar signs either on both g and two would have worked or just having it on 2 would have worked.
Let’S try that if i enter that formula and then double click the autofill handle notice that it worked. The reason why is because, even though g wasn’t set to be absolute, the number two was set to be absolute and g wasn’t moving around anyway. So the formula worked just the same thanks for watching this excel quick tip.
I hope you found it to be helpful if you want to learn more about relative versus absolute cell references. You should definitely watch my other video on that topic, but in the meantime please like follow and subscribe and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that by clicking the thanks button below the video or by supporting my patreon account or by buying channel merch and you’ll, see information about those options in the description below the video. Speaking of patreon, i want to give a quick shout out to my five dollar patreon supporters. Thank you. So much for all you do to support the channel.
.