In Part 3 of my series on Excel shortcuts, Iâ€™m going to focus on a few miscellaneous shortcuts that help out with daily tasks.
F4 â€“ Repeat
Use F4 to repeat the last thing you did, a great shortcut especially if the action just took several clicks. For example:
Â· Inserting cells to the right
Â· Applying complex formatting to a cell (could also use the format painter)
Â· Inserting more rows or columns (after the first insert, see Part 1 of this series for an example)
F12 â€“ Save As
The Save As dialog will come up when typing CTRL + S as long as the book youâ€™re working on has never been saved. But if youâ€™re working on a workbook thatâ€™s already been saved, and now want to save it with a new name (sometimes I do that for versioning my work and maintaining backups of my progress), itâ€™s useful to be able to quickly bring up the Save As dialog. F12 or ALT + F, then A (if you like the file menu) will automatically bring up the Save As dialog.
ALT + F1 â€“ Insert a Chart
I love this one â€“ just highlight some data in the grid and type ALT + F1 to get a chart inserted onto the same sheet youâ€™re working with. If you donâ€™t like the default column chart that gets inserted, notice that on the Insert Chart dialog there is a button named â€œSet as Default Chartâ€, so all you need to do is pick the chart type you like and click that button:
If you wanted a chart sheet instead of a chart object on a worksheet, type F11 instead.
CTRL+F1 â€“ Format Cells Dialog
While CTRL + F1 brings up the Number tab of the Format Cells, CTRL + SHIFT + F brings up the same dialog, but with the Font tab active.
AutoFill a Series
AutoFill is a really powerful feature that saves a lot of time typing repetitive series. One of the most common ways that I use it is to create a numbered list. I type 1 in A1 and 2 in A2, select both cells, click and drag the fill handle down and now I have a numbered list as large as I need.
Excel can automatically fill a large variety of series including the months, days of the week, dates, text, and formulas.
There are also some handy series commonly used in finance that can automatically be filled in like 1Q2010. This will fill the Quarter number from 1 to 4 and then update the year that follows:
Type 1Q2010 and start filling down:
Once you get to the end value youâ€™re looking for, let go of the mouse:
Try it yourself with your own values, or type Q1, or Q1FY09, or 1Q2009 and drag fill it down (grab the lower right corner of the selected cell and drag).
You can also fill a series of numbers using linear or growth progressions, as well as specify your own custom list to use as the basis for a fill. See this help topic for even more details on how to work with series AutoFill: http://office.microsoft.com/en-us/excel/HP012163661033.aspx
CTRL + SHIFT + L for AutoFilter
As long as the active cell is in a range of data, typing this keyboard shortcut will turn on AutoFilter for that range, putting filter dropdown arrows at the first row of data.
Select a cell in the range you want to filter:
Then type CTRL + SHIFT + L:
CTRL + ALT + F5 for Refresh All External Data
Just open a workbook and type this keyboard shortcut to trigger a â€œRefresh Allâ€, which refreshes all external data connections.
CTRL + P â€“ Print
This shortcut is simple enough; it brings up the Print dialog / user interface.
CTRL + F1 for Collapse / Expand Ribbon
I use this to get more of a â€œfull screenâ€ effect in Excel (and when reading Word documents).
After CTRL + F1:
CTRL + Page Up/Down for Sheet Navigation
Use these shortcuts to navigate through sheet tabs in the current workbook. See Part 1 of this series for an example.
OK, thatâ€™s all I have to say (for now) about Excel keyboard (and other) shortcuts. I hope you enjoyed this series!