Jan 27, 2015

Here are three keyboard shortcuts I find really handy in Microsoft Excel. These shortcuts each do something very useful they and are often hard to achieve using the mouse or by clicking in the menus. So without further ado here they are!

Ctrl+Page Up and Ctrl+Page Down

If you try these two out, you’ll find that you move left (page up) and right (page down) through the worksheets in the workbook. A whole lot easier than clicking on sheets, and especially effective if you have more sheets than fit down the bottom of your screen. Rather than clicking on the arrow buttons repeatedly to scroll the sheets left and right this can save you a lot of time navigating.

Ctrl+`

This one is formula related. In fact, it works really well on a file that someone else has sent you but you didn't create. If you are looking for the (`) key on the keyboard it is directly below the Escape (Esc) key on the keyboard. Some people remember this as “Control Squiggle” because the top half of the key has this symbol on it (~). This symbol is actually called a tilde, if you were wondering. What this keyboard shortcut does is double the widths of all the columns, but more importantly it shows the actual formula in the grid rather than the result of the formula. It is an on/off switch so if you go Ctrl+` again you go back to normal. (It also halves the column widths back to normal too!) I find doing this really useful when I am trying to work out where formulas are, or to work out what is going wrong with a formula. If you click on a formula while using Ctrl+` to show formulas, then Excel automatically shows you the ‘colour coding’.

Alt+;

You don’t need to hold down Ctrl for this one, it’s simply Alt+;. What does it do? It removes any hidden cells from a selected area. I’m sure we have all tried to copy a range of cells in Excel where there were hidden rows or columns in the middle of the area. When you paste, you find out the hard way that Excel has copied not only the visible cells but also the hidden ones and it pastes them in as well. After selecting an area that has hidden cells simply go Alt+; and the hidden cells are deselected from the area. You can tell because of the white lines through the selection (although this is harder to see in the 2007 version of Excel. Then you can copy only the cells that are visible and paste them elsewhere. Works just as well with rows hidden using a filter as it does with rows or columns you have manually hidden.