Back
Excel

Excel Team Favorite Keyboard Shortcuts – Part 3

untitled 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:

clip_image002

If you wanted a chart sheet instead of a chart object on a worksheet, type F11 instead.

CTRL+F1 – Format Cells Dialog

clip_image004

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:

clip_image005

Once you get to the end value you’re looking for, let go of the mouse:

clip_image006

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:

clip_image008

Then type CTRL + SHIFT + L:

clip_image010

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).

Expanded ribbon:

clip_image012

After CTRL + F1:

clip_image014

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!