Excel Team Favorite Keyboard Shortcuts – Part 1 of 3

This post is brought to you by Chad Rothschiller a Program Manager on the Excel Team.

In this series of posts I’ve decided to walk through some of my favorite Excel keyboard shortcuts and a few other little tricks I’ve learned along the way that helps me work faster. In fact, when visiting some analysts in financial services, I learned that when they get trained on Excel, the instructor actually unplugs the mouse from the classroom computers in order to really drive home the efficiencies that can be gained from keyboard shortcuts.

But when I say these are my favorites what I really mean is our favorites, because I thought it would be fun to ask the folks who work here on the Excel team about their favorites as well, and summarize some of those answers here. I was pretty excited to get over 50 responses in the span of about 4 hours!

Knowing that many Excel blog readers are Excel experts in their own right, I highly encourage collaboration on these posts: what are your favorite shortcuts? My hope is to bring new people into the fold of Excel shortcut enthusiasts, as well as stimulate those who consider themselves keepers of the secrets.

I want to thank Daniel Mullowney, a tester who currently works on Excel Services, for co-authoring this series with me. We were both literally working on the *same* document at the *same* time - - we divvied up the work and began both editing the same document at the same time. And every time we clicked save, all our changes got synchronized up. You can read more about co-authoring in Word on the Word blog site, and you can read about the same topic using Excel Services in our collaborative editing post.

For a more exhaustive list of Excel shortcuts, see the blog post titled Charlie’s List, or “214 Keyboard Shortcuts”.

Part 1 of my series on Excel shortcuts is going to focus on navigating blocks of data. When I was working in financial services back before my days here at Microsoft, I often had to navigate through the blocks of numbers that make up an income and expense report or balance sheet, for example something that looks like this:

Note: this is Microsoft’s earnings report for the 2^nd quarter ended December 31^st, 2009. A link to the spreadsheet can be found here, in case you want to follow along.

In this layout there are spacer rows and columns: column D is blank, used to create some space between the three-month and six-month numbers. Row 20 is also blank, used to create some additional space.

Once I get this workbook open, some common things I want to do as I modify this income & expense report include:

· Insert a row / column to add more spacers or more data

· Get to the bottom of a block of numbers, e.g. to see the formula in the total row

· Select a block of numbers, e.g. to copy/paste

· Add a new formula column, e.g. to the right of the numbers, to compare 2009 vs. 2008

Insert a row / column

Let’s say I’m in column B, using down arrow to look through the numbers, and I stop at cell B11:

I want to insert a new row here, so I can add another category of expense. To do that, I type SHIFT + SPACE to select the row, and get:

Then I type CTRL + SHIFT + “+” to insert a row:

Now I can arrow over left/right to enter the label for the expense, and the associated amounts.

Incidentally, if I want to select the entire column instead of row, I type CTRL + SPACE instead, and then the same CTRL + SHIFT + “+” will insert a column instead of a row. Also, if I only had a few cells selected, then CTRL + SHIFT + “+” will result in a prompt asking whether I want to shift those cells down or right, as well as whole row/column operation options.

Get to the top/bottom or left/right edge of a block of numbers

Again, let’s say I’m in column B, using down arrow to look through the numbers, and I stop at cell B11:

If I want to quickly get to the bottom cell in this block of cells, I can use CTRL + ARROW DOWN:

That brings my active cell to the bottom of the current block of cells. Though there are cells with values below B19, the selection stops there because of the blank cell B20.

Similarly, CTRL + RIGHT ARROW would have stopped the active cell at C11 because of the blank cell D11. You can also imagine these methods being extremely useful if your block of data is especially long or wide, and you don’t want to just rely on scrolling to get to the bottom or right side of your data.

Select a block of numbers

If I use the above shortcut keys in addition to pressing down the SHIFT key at the same time, this has the effect of selecting all the cells in between the starting and ending point. So if from the same starting point B11, I type CTRL + SHIFT + DOWN ARROW:

Alternatively, if I had wanted B9 to B19 selected, from B11 I would have first typed CTRL + UP ARROW in order to get to the top of the block, then CTRL + SHIFT + DOWN ARROW to select the entire block. If instead of selecting all the way down to B19 I had wanted my selection to end at B18, all I need to do is let go of the CTRL key, and type SHIFT + ARROW UP to shorten the selection one cell. Again, these are immensely useful if you have hundreds or thousands of rows or columns to navigate.

Add a column of formulas on the right

Now let’s say I want to use a formula to compare the cost of revenue line items, for the six month period, 2009 vs. 2008. There are a couple ways to do that using shortcut keys, but I’ll tell you my favorite, because this method transfers between rows and columns just as easily, and gives you fine control over the result. The basic concept is to select a block of data that’s exactly the size of the range where I want to put my formula, then copy that data into the range where I want my formula, and finally, enter the formula there. Here’s the detailed steps:

First, I want to start with the active cell on F9, so I use the F5 function key, which brings up the “Go To” dialog. Type in F9 then ENTER . Then, since I only want the operating expenses, I will SHIFT + DOWN ARROW a few times to get F9:F14 selected:

Then, I’ll copy (CTRL + C) and paste (CTRL + V) that data into H9:H14:

Lastly, I will simply enter the formula by typing =E9/F9 and then typing CTRL + ENTER, which puts the formula in all cells of my selection, with the proper range adjustment for each row.

An alternate way to get this done would be to start by selecting the cell directly adjacent to my data (in this case, G9, not H9), type the formula, then double click the little fill handle in the bottom right corner of the selected cell. This will fill down the formula as far down as the data next to it goes, in this case, down to G19. While that can be useful in lots of cases, here it’s not that great because column G is one of those narrow spacer columns, and if I don’t want to mess with the layout of the sheet then the formula result ends up getting rounded up to “1” for most of those calculations, which isn’t that helpful.

Putting the above into practice on a larger data set

I really want to drive home the power and utility of the above shortcuts. Once your hands get the natural feel for it, navigating around large sets of data is actually quite effortless. Here’s another example that puts everything together. I’ll use fewer words to describe it this time, let’s see if you can follow along without using your mouse!

1. Starting with a new book, create the data set:

a. F5 (Go To), A1:K25000, ENTER

b. =RAND(), CTRL + ENTER

2. Insert 4 new rows just above the bottom row and copy the formulas into the new rows:

a. CTRL + DOWN ARROW, SHIFT + SPACE, CTRL + SHIFT + “+”, F4 (repeat last action), F4, F4

b. UP ARROW, CTRL + SHIFT + RIGHT ARROW, CTRL + C, DOWN ARROW, SHIFT DOWN ARROW (3 times), CTRL + V

3. Insert a column just left of the right-most column and copy the formula down in the new column:

a. CTRL + RIGHT ARROW, CTRL + SPACE, CTRL + SHIFT + “+”, LEFT ARROW, CTRL + UP ARROW

b. CTRL + SHIFT + DOWN, CTRL + C, RIGHT ARROW, CTRL + V

4. Add a new formula column on the right side (column M) that sums across rows:

a. CTRL + RIGHT ARROW, CTRL + SHIFT + DOWN ARROW, CTRL + C, RIGHT ARROW, CTRL + V

b. “=SUM(“, LEFT ARROW, CTRL + SHIFT + LEFT ARROW, “)”, CTRL + ENTER

5. Add a totals row at the very bottom, across columns:

a. CTRL + DOWN ARROW, CTRL + LEFT, CTRL + SHIFT + RIGHT, CTRL + C, DOWN ARROW, CTRL + V

b. “=SUM(“, UP ARROW, CTRL + SHIFT + UP ARROW, “)”, CTRL + ENTER

6. Select all the data, copy / paste it to the next sheet:

a. CTRL + A (select all), CTRL + C, CTRL + PAGE DOWN (to navigate to the next sheet), CTRL + V

As they say, there’s more than one way to skin a cat. My habits tend to drive me towards keystrokes that I can use all over the place, so I tend to use the combinations of CTRL, SHIFT, and ARROWS to navigate and select blocks of data, and I even extend those to do formula fill-down and fill-across instead of using the fill handle or other methods, because my fingers have already figured out how to quickly access those keys.

That’s the end of Part 1 of my Excel shortcuts series, Navigating blocks of data. In Part 2 of my post we’ll cover formula-related shortcuts.