MrExcel's Learn Excel #353 - Fabulous F4

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 12, 2009.
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!
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top