Sure, you probably know that the F4 key can be used to create absolute references in a formula. When you are not in formula entry mode, the F4 key also becomes a shortcut key for the last command issued. Episode 353 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel podcast, I'm Bill Jelen.
Today we're going to talk about the F4 key, don't go away!
I know that a lot of experienced people know about the F4 key, but I'm going to show you a new use for the F4 key today.
Usually, the F4 key comes up when we're about to enter a formula that we wanted to use an absolute reference.
Here I have a formula, I need to copy it down to all the rows, so I'll double-click the fill handle, and of course it works perfectly.
But then if I create a formula, let's say that's that total times the tax rate, and the tax rate is only in cell I1.
When I copy that formula down, I'm going to get 0’s, and why did I get 0’s?
Because, as I copied the formula, Excel correctly changed F2 to be F3.
That’s perfect, but it also changed cell I1 to be I2, and we don't want that to happen.
So the solution is, as you're entering the formula, and your insertion point is right After the I1, hit the F4 key to put the dollar signs in.
The $ says “Hey, no matter where I copy this formula, the dollar sign on I says we're always going to point at column I, and the dollar sign on 1 says we’ll always point at Row 1.” Now, when we double-click the fill handle to copy it down it copies perfectly, because the F2 changes to F3, but the I1 continues to point to I1.
That's the F4 key, the F4 key actually toggles between the four various states of relative, absolute, mixed, and mixed formulas.
So that's the usual use for the F4 key, but I learned a great new trick for the F4 key last week.
Let’s say that you select a cell, and you perform any command on that cell, for example, we’ll turn it bold.
Then, as you select additional cells, you can hit the F4 key to repeat that command, it remembers the last one command you did.
So for example, select a cell, hit italics, and then, as you hit other cells, and the F4 key, Excel will apply that command to the cell.
The F4 key: great for creating absolute references, but also great for remembering the last command you issued.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
Today we're going to talk about the F4 key, don't go away!
I know that a lot of experienced people know about the F4 key, but I'm going to show you a new use for the F4 key today.
Usually, the F4 key comes up when we're about to enter a formula that we wanted to use an absolute reference.
Here I have a formula, I need to copy it down to all the rows, so I'll double-click the fill handle, and of course it works perfectly.
But then if I create a formula, let's say that's that total times the tax rate, and the tax rate is only in cell I1.
When I copy that formula down, I'm going to get 0’s, and why did I get 0’s?
Because, as I copied the formula, Excel correctly changed F2 to be F3.
That’s perfect, but it also changed cell I1 to be I2, and we don't want that to happen.
So the solution is, as you're entering the formula, and your insertion point is right After the I1, hit the F4 key to put the dollar signs in.
The $ says “Hey, no matter where I copy this formula, the dollar sign on I says we're always going to point at column I, and the dollar sign on 1 says we’ll always point at Row 1.” Now, when we double-click the fill handle to copy it down it copies perfectly, because the F2 changes to F3, but the I1 continues to point to I1.
That's the F4 key, the F4 key actually toggles between the four various states of relative, absolute, mixed, and mixed formulas.
So that's the usual use for the F4 key, but I learned a great new trick for the F4 key last week.
Let’s say that you select a cell, and you perform any command on that cell, for example, we’ll turn it bold.
Then, as you select additional cells, you can hit the F4 key to repeat that command, it remembers the last one command you did.
So for example, select a cell, hit italics, and then, as you hit other cells, and the F4 key, Excel will apply that command to the cell.
The F4 key: great for creating absolute references, but also great for remembering the last command you issued.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
