Shortest Excel Tip Ever: <F4> and <Ctrl>-Y
I’ll put my standard big, fat, hairy disclaimer here that this blog is not about Excel tips. There are lots of resources for that. As a matter of fact, the Contextures blog is one that I stumbled across after Debra commented on my last Excel tip.
Nevertheless, here’s a handy one that requires no customization of Excel, but that I guarantee you’ll be hooked on if you start using it: <F4>
<F4> and <Ctrl>-Y do the same thing, actually, and they work in MS Word, too. What do they do? Pretty simple:
Repeat the Last Action Taken
That’s it. If you’ve just formatted a cell or set of cells with a new border and background color, then you can click on a cell and press <F4> and it’ll apply the same formatting to the new cell. And then do it again! In this case, it does the same thing as the Format Painter…but does it faster (with limitations, as described below).
If you’ve just inserted a row and you want to insert another row lower down on the spreadsheet, highlight the next row and press <F4>.
As you can imagine (if you stop and try to imagine it…and I recognize it’s got to be a pretty bleak day of creativity for this to bubble up as worthy of your imagination), this is particularly handy when doing some oddball work on non-contiguous cells.
This is handier than you might think. And, it does have it’s limitations. The main one is that it only repeats the immediately preceding action. In the Format Painter example above, <F4> is no use if you have a cell already formatted as you want and you want to copy that format to other cells. That’s what the Format Painter is for.
And, another limitation is that it doesn’t work with every possible action. For instance, if you type a value into a cell and then want that same value in another cell…<F4> doesn’t work. You’ll have to copy and paste. It becomes pretty intuitive in a hurry as to where it works and where it doesn’t.
Happy repetition!
That’s my favourite shortcut! I wish it worked in Microsoft Access too.
Nice, Tim! I’ll use it…
I found out F4 while doing charts and going mad as I color-coded months in my daily trended bars. I figured there had to be a better way.
Strangely, though, I’ve never bothered while doing routine things like insert rows and such. Doh
Thanks for the tip! That’s a huge help. For some reason, F4 doesn’t work in my Word 2007, but the Y does. Any suggestions on the F4 key?
@Leslie Double-check that you’ve got the Function Lock (or F Lock) turned on — should be a random key somewhere on your keyboard that you pretty much never use.
Thanks Tim!!! you just saved loads and loads of my time! I am working with 30 workbooks and about 500 spreadsheets and this advice is the most helpful tool I could ever imagine.
You are doing a great job with your site, and you earned a fan here!