You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s post is brought to you by Chad Rothschiller a Program Manager on the Excel team.
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!
CTRL+F1 is used both correctly and incorrectly in this article. The first instance is the incorrect usage: it does NOT bring up the Format Cells dialog that is CTRL+1. As presented later in the article CTRL+F1 does in fact toggle the display of the Ribbon. CTRL+1 is probably my most used shortcut; it's very handy to know. The rest of the article (and the entire series as a matter of fact) is very helpful. Keep 'em coming!
Yeah, this used to be my favorite, until it was hobbled in 2007. In earlier Excel versions it was possible to format one series, using dozens of clicks and keystrokes, then select another series in the same or a different chart, click F4, and save all of those clicks and keystrokes. In 2007+ if you're lucky you save one of those keystrokes, usually but not always the last one applied.