Bottom Line: Learn 5 different shortcuts that use the F4 key in both the Windows & Mac versions of Excel.
Skill Level: Beginner
Download the Excel File
The Excel workbook that I use in the video can be downloaded here:
Using F4 for a Variety of Shortcuts
The F4 key has a lot of different uses in Excel. All of these shortcuts can save you time with common daily tasks. So make sure to share them with your friends & co-workers as well.
Before we dive into the five shortcuts that use the F4 key, I want to mention that if you are using a laptop, you might have to hold down the Function key, often abbreviated Fn, in conjunction with the F4 key since those F keys can double for other purposes on your keyboard.
Here's a post on the best keyboards for Excel shortcuts that explains what to look for in both a laptop and regular keyboard.
1. Repeat Last Action
Windows: F4 (or Ctrl+Y)
When you select a cell and hit F4, your last action will be repeated. That includes formatting, inserting or deleting rows, and making changes to shapes. In the image below, I highlighted cell A6 in yellow, then selected cell A8 and hit F4. The cell immediately was changed to yellow because it repeated the last thing I did.
It's important to note that F4 only repeats the last single action you took. If you made multiple formatting changes to a cell like fill color, font color, and borders, and wanted to apply those changes to other cells, then the Format Painter is a good alternative. I explain how to use the Format Painter in point #11 in this post.
2. Toggle Absolute/Relative References
When writing formulas, any portion of the formula that denotes a cell or range of cells can be made absolute by hitting the F4 key while your cursor is on that cell reference. This means you can use F4 to add $ dollar symbols in front of the column letters and row numbers, $A$12.
By continuing to hit the F4 key, you can toggle or cycle through mixed references (column absolute while row relative, or row absolute while column relative), and then back again to a completely relative reference.
Starting with the reference A17, here are the references that will be created each time you hit F4:
If you have a range reference (A4:D12), you can first select the text of the entire range reference, then press F4 to apply the absolute/relative references to the entire reference. Or just select the text in one of the cell references to apply it to that portion of the range reference only.
The formula being used in the above example is a VLOOKUP formula. If you're interested in learning more about VLOOKUP, you can use this tutorial: VLOOKUP Example Explained at Starbucks or our VLOOKUP Tutorial on YouTube.
3. Find Next/Previous Cells
Windows: Shift+F4 for next and Ctrl+Shift+F4 for previous
Mac: Cmd+G for next and Cmd+Shift+G for previous
When Shift+F4 is used, it will use whatever qualifiers (values, formatting, etc.) were last entered into the Find and Replace Window to find the next cell with those criteria. Adding Ctrl to Shift+F4 will make the find go in reverse to locate the previous cell.
This means that you can close the Find Window and use these shortcuts to find and select the next/previous match on the sheet or workbook. It saves you from having to click back to the Find Window to navigate to each matching cell. Checkout the video above to see it in action.
4. Close Current Workbook
Windows: Ctrl+F4 (or Ctrl+W)
If you'd like to use your keyboard instead of your mouse to close out the workbook you are using, you can hit Ctrl+F4. You may be prompted to save your workbook if you haven't done so recently. Excel and any other open workbooks will remain open.
5. Close Excel
If you want to close out of Excel completely, including any open workbooks, this is the shortcut to use. It's the same as clicking on the X in the upper right corner. Again, you may be prompted to save your work before it closes.
On a related note, here are 3 Tips to Save and Close All Open Excel Workbook Files + Macro.
There are five shortcuts that should definitely save some time with common Excel tasks.
Did you know all of these? Did you learn something new? Please leave a comment below to let us know and/or any additional tips you have for F4.
And feel free to share this post with a friend or co-worker if you feel like getting a virtual high-five. 🙂