How Can Excel Shortcuts Help Me Defeat Fire Breathing Spreadsheet Dragons?
Ever have a massive spreadsheet that takes forever to navigate? Behold, I give you dragon-slaying, big-data-conquering Excel shortcuts that will save you massive amounts of time (and that’s not an exaggeration). When it comes to waging war with big data (moving around in big files with lots of data), keyboard shortcuts are indispensable — and far faster than using the mouse.
OK, Enough Excel Drama, What Are The Damned Shortcuts!?
Hold on, we’ll get there right away. First, I highly recommend that you follow these four guidelines that will take maximum advantage of Excel’s data-grabbing shortcuts and sorting features:
- Use a single header for every column (no more, no fewer).
- Format the headers in bold.
- Put the headers in row 1 if at all possible (if you must put them in another row, make sure the row immediately above it is blank).
- Don’t have any totally blank rows (though individual blank cells are fine — just not in the headers).
It should look something like this:
OK, now let’s move on to the shortcuts.
Excel Shortcuts For Selecting Data
Since data selection is a big topic in and of itself, I’m going to confine this post to selecting a range (or “contiguous region” or “block” of data). I’ll cover related big-data-dragon-slaying topics later.
To cut to the chase, Ctrl+A is generally the best shortcut for selecting a contiguous region, and you must type this shortcut when you’re already within the region (otherwise it selects the entire worksheet).
Select Contiguous Range Of Excel Data (keyboard #1): Ctrl+A (must be used within the range)
Now, on to the nuances, best practices and variations on the theme.
If you’re like me, you always want to double check to make sure you actually selected what you intended to select. I do this by looking at the bottom right side of my selection — to make sure no essential nearby fragments were mistakenly included or excluded. For example, you may have an empty row that you weren’t aware of that stopped Ctrl+A dead in its tracks in row 82,391. Maybe it’s a data set where, every month, you’re pasting in the latest monthly numbers — and you (or someone else) left an empty row by mistake. Or maybe you meant to insert a row of actual data but left it empty by mistake. Who knows what horrible accident may have occurred! But can under-select your data, and careers can be ruined by such accidents — so it’s best to avoid them by practicing good data selection hygiene.
Fortunately, there is a way to force Ctrl+A to take you to the last selected cell — where you can do your inspection. If you start in the first cell in the region, you’ll select the whole contiguous region and your cursor will go to the bottom right of the selection — see below.
Unfortunately, you can’t see very far. To see farther, simply zoom out to see if there’s anything nearby you need to be concerned with. Then zoom back in. You can zoom out and in by hitting the Ctrl key while spinning the mouse wheel.
Zoom In/Out (hybrid): Ctrl+Spin-Mouse-Wheel Out/In
Since you need to start in the first cell of the region, wouldn’t you like a shortcut to get you to that cell? It’s generally Ctrl+Home — this will generally take you to A1. From there you can do Ctrl+A and go to the last cell for inspection. However, there are some caveats:
Caveat 1: Ctrl+Home will not take you to cell A1 if you’re using Freeze Panes. If you’re using Freeze Panes, Ctrl+Home acts as if the frozen cells don’t exist and takes you instead to the first unfrozen cell (which might be B1 or C3). From there, you can hit Ctrl+Arrow until you get to the first cell. To avoid this complication you can also just avoid using Freeze Panes. However, since I love Freeze Panes like a brother, I won’t give it up. I am willing to do Ctrl+↑ — a small speed bump — for the privilege of using Freeze Panes.
If you don’t use Freeze Panes, then you’re in luck, because you can just do Ctrl+Home to get to cell A1. In fact, then you have a very handy sequence: Ctrl+(Home-A) will take you to the first cell and then immediately select the whole region and take you to the bottom right. That’s Ctrl+Home, then let go of Home and hit A, all the while keeping Ctrl pressed. So let’s note the two shortcuts we just covered:
Go to A1 (keyboard): Ctrl-Home (doesn’t work if you’re using Freeze Panes)
Go to A1 and Then Immediately Select Entire Region And Go To The Bottom Right Of It (keyboard): Ctrl+(Home-A) (doesn’t work if you’re using Freeze Panes).
Caveat 2: Ctrl+Home is not very useful if you’re dealing with a “remote” region — say, one starting in cell G50 (which is pretty far from A1). Luckily, there’s a solution for this situation, so let’s walk through it.
First, from within the remote region, you hit Ctrl+* (or Ctrl+Shift+8) to select the region. (Note: use the * that shares the 8 key – not the one on the number pad).
Select Entire Region (keyboard #2): Ctrl+* (or Ctrl+Shift+8)
This is a lot like Ctrl+A, except your cursor gets moved to the first cell in the region as part of the selection process (with Ctrl+A your cursor stays where it was when you hit Ctrl+A). But while your cursor is technically in the first cell, the whole region is selected (not just the first cell). And since the whole region is selected, you can’t just hit Ctrl+A and be done as you would if only the first cell were selected. Therefore, to “shake loose” the entire selection and only select the first cell, you need to hit ↑ then ↓ (or → and then ←). This leaves your cursor in the first cell with nothing else selected (this keystroke sequence reminds me of some of the moves needed when solving a Rubik’s cube). So the full sequence to get to the first cell of the “remote” region is as follows:
Go To First Cell Of “Remote” Region (keyboard): Ctrl+ *-↑-↓ (once inside region)
To be clear, you hit Ctrl and * simultaneously, then let go, then hit ↑ and then ↓ (without needing to press any other keys at the same time as the arrows).
From here – now that your cursor is in the first cell of the region — just do Ctrl+A and you’ll be looking at the bottom right of the region. So the whole sequence is as follows:
Select Entire Region And Jump To Bottom Right – For “Remote” Region (keyboard): Ctrl+ *-↑-↓, Ctrl+A (once inside region),
Using this method will take your viewpoint to the end of the region when you’re done (which will not happen if you stop after doing Ctrl+ *).
As I said, this resembles a Rubik’s cube move. It seems a little convoluted — but it’s infinitely faster than using the mouse.
Another Excel Shortcut For Selecting Big Chunks O’Data
Let’s discuss a decent alternative for Ctrl+A and Ctrl+* that also has its place: Ctrl+Shift+End, which selects everything from where you start through to the “last used cell.”
Select Everything Through The “Last Used Cell” (keyboard): Ctrl+Shift+End
Unfortunately, since any cell that was used and then cleared out or deleted is considered a “used cell.” So Ctrl+Shift+End can over-select by picking up extra blank rows and columns. It’s as if it has the memory of an elephant and insists on going to that “last used cell” even though there’s nothing in it.
Unlike Ctrl+A, Ctrl+Shift+End will conveniently take you to the last cell (for inspection) no matter where you start. But if you need to select a whole region, you need to go to the first cell anyway. And once you’re in the first cell, you might as well use Ctrl+A. So in this situation there’s no real advantage to using Ctrl+Shift+End. Ctrl+Shift+End only seems to be a good choice in two situations:
- If you want to select everything on the tab (not just the region you’re in) and you don’t mind potentially including some blank cells (that once had data but no longer do). Remember to start in cell A1.
- When you only want the region you’re in, but you want to be ABSOLUTELY sure you’re not leaving out any piece of it (even if that means over-including cells to the bottom and the right). Remember to start in the first cell of the region.
A Final Excel Shortcut For Selecting Chunks O’Data
Another tool you may use is Ctrl+Shift+Arrow — which only works well for big data sets if the data set doesn’t resemble swiss cheese.
Ctrl+Shift+Arrow jumps to the cell just before the next blank cell, and selects everything in between. If you’ve already selected a block of cells, then it will stop at blank rows or columns, not mere blank cells. You might call the selections you get at these intermediate stopping points “sub-regions” within the main region.
Jump To, And Select Through, Cell/Row/Column Just Before Next Blank Cell/Row/Column (keyboard): Ctrl+Shift+Arrow
If you have a perfectly filled in region (every cell has something in it), then you can start in the first cell of the region and type Ctrl+Shift+↓, followed by Ctrl+Shift+→, and your selection is complete. Moreover, you’re taken to the last cell for easy inspection. You can make it even faster by doing Ctrl+Shift+(↓- →). In other words, hit Ctrl+Shift, then (while keeping Ctrl+Shift pressed) hit → followed by →.
Where this approach can go wrong is when your starting column (or row) has blanks and/or is shorter than other columns (or rows). Each time you tap an arrow key, you’ll stop at a blank, which will slow you down. You have to keep tapping, and unless you’re intimately familiar with the data, you’ll be speculating as to how long it will take until you reach the end. With choppy data, this can be a real issue.
If one of your headers is blank, then after you’ve selected the first column, your attempt to select all the other columns by doing Ctrl+Shift+→ may fail if you have blanks in the wrong places in the remaining columns you’re trying to select. (You can avoid this situation by adhering to the best practice of having no blank headers).
Moreover, if the column (or row) you’re starting in is shorter than one or more other columns (or rows), then you’ll end up underselecting. You might Ctrl+Shift+↓ to the last cell with data, but that may not be the last row with data. So if you then Ctrl+Shift+→ to the right edge of the data, you could inadvertently be leaving out rows below you that do have data. You could say Ctrl+Shift+Arrow is “shortsighted” in that it doesn’t comprehend the idea of a region, and it only moves from one blank to the next (it doesn’t know enough to skip to the end and doesn’t know or care what’s in neighboring columns or rows).
There are some workarounds for Ctrl+Shift+Arrow, and some related techniques, but I’ll save them for another post. For now, just remember that you may end up under-selecting if you use this technique.
So in sum, to select a whole region and go to its last cell (for inspection):
For a plain vanilla region that begins in A1 and has no frozen cells: Ctrl+Home to get to the first cell, then Ctrl+A
For a region that begins in A1 but has frozen cells: Ctrl+Home, then Ctrl+Arrow to get to A1, then Ctrl+A
For a region that begins in a remote location (instead of in A1): Ctrl+ *-↑-↓, Ctrl+A
To select everything on a tab (including cells that are now blank but were once used): Ctrl+Shift+End
To select “sub-regions,” but risk getting hung up by blank cells and underselecting: Ctrl+Shift+Arrow
I hope these shortcuts help you move fast the next time a big data dragon is breathing fire upon you while you’re up against a pressing deadline. If you deal with big data sets a lot, these shortcuts really are indispensable, and are absolutely faster than using the mouse. As always, I welcome your feedback.