Excel Everest

View Original

The Top 5 Keyboard Commands You Really Should Know

Well, I’ll be honest here. There are more than five. In fact, there are probably fifty. On the other hand, five are easy to remember, and the title is a lot more catchy.

In all seriousness though, what we are talking about in this Excel tutorial are commands that are inordinately useful and for the most part, little known. But get to know them hear, master them, and watch productivity zoom ever upwards.

The SPACEBAR Series

 CTRL+SPACEBAR is a particularly gratifying shortcut to get acquainted with, since it selects the entire row in which a cell happens to be active. Particularly useful for copying for large swathes of data from one column to another. ALT+SPACEBAR does exactly the same thing when it comes to rows, while CTRL+ALT+SPACEBAR does exactly what you’d expect it to – go ahead and give it a try! The SPACEBAR series works like a charm for large amounts of data, when you’d rather not take the trouble of scrolling to the end of the data in order to select it entirely.

SHIFT+END+DOWN

 Which brings us rather conveniently to the next shortcut. If you want to select a range of data that (it would seem) extends down for many rows altogether, you want to select the first cell in the data, like so:

…and simply press SHIFT+END+DOWN (the down arrow, that is). You’re through! The entire data is selected. This works in all directions, so if you are at the start of a particularly large and cumbersome array, pressing SHIFT+END+RIGHT, followed by SHIFT+END+DOWN will select the entire array. The only thing you need to keep in mind here is that this works assuming there are no missing rows/columns in your data. So long as you keep that in mind, you’ll find this is a pretty useful trick to know.

ALT+SHIFT+RIGHT

The “Group” feature in Excel has been a boon for many an intrepid spreadsheet traveler. It helps you literally group two or more columns/rows together – something that comes in particularly useful for large complicated models. Well, the ALT+SHIFT+RIGHT command helps you group rows/columns together in a jiffy. ALT+SHIFT+LEFT ungroups them. Simple, eh?

CTRL+SHIFT+’

 This one’s not all that well known, and getting used to it is a useful, pleasant surprise. All of us have faced situations in which we need to enter the same value in a cell as is present in the one above. For example, let’s say cell B3 has the value 4. Well, B4 should also have exactly the same value. Enter CTRL+SHIFT+’. It does exactly what is required, no more, and certainly no less. (By the way, you might want to give CTRL+SHIFT+>, and CTRL+SHIFT+< a try as well – they can copy formulas over from the left and the top of the active cells respectively)

CTRL+SHIFT+{

Imagine a spreadsheet that is large, complex and let’s be frank here, horrendously complicated. It’s reached you via email at the wrong end of a Friday afternoon, and making sense of it is a task at which Hercules would have quailed. How best to go about unraveling this monstrosity?

Well, here’s a trick that you’ll be able to teach old Hercules if ever the need arises. Simply point your mouse over to the last cell in which the final result resides, and press CTRL+SHIFT+{

It highlights all the cells which the active cell refers to while calculating it’s result.

230 is the result of adding all of the cells above. If one were to select that cell and press CTRL+SHIFT+{, one would see this:

There, that’s all for this time around. Know any snazzy hidden gems of your own when it comes to Excel and keyboard shortcuts?!