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!