What are some basic Excel data selection and navigation shortcuts that use the keyboard and the mouse (hybrid shortcuts)?
Excel data selection and navigation skills are critical for becoming faster, especially if you’re an analyst working with exported data. Once you learn the fundamentals there are dozens if not over a hundred useful navigation shortcut combinations. If you manage a lot of files with large data sets, these shortcuts are absolutely indispensable and will save you a ton of time and help you keep your concentration. Once you’ve mastered them you will fly around your spreadsheets like nobody’s bidness and probably impress your boss in the process.
In this post I will start simple, then quickly move up to more advanced tactics.
It’s easy enough to move the cursor with the mouse – just reposition the cursor and click. I know it’s elementary, but let me state it:
Move To New Cell (mouse): move cursor to new location, then click.
It’s also easy enough to select a contiguous region with a mouse:
Select Contiguous Region of Excel Data (mouse): Click (and hold) while rolling cursor across screen, then unclick.
A slight variation which is a hybrid method (it combines mouse and keyboard action into one shortcut sequence) is to click on one cell, then click Shift-Click on the bottom right cell of the region. You get the same result.
Select Contiguous Region of Excel Data (hybrid): Click on first cell, then Shift-Click on end cell
It’s not that much harder to select multiple non-contiguous cells or regions with the mouse. You just add the Ctrl key to the sequence. After selecting your first region, you hit Ctrl and hold it down while selecting each additional cell/range with the mouse. In between selections you can keep Ctrl down or let it go – it makes no difference.
Select Non-Contiguous Cells or Regions of Excel Data (hybrid):
- For first selection: Click (and hold) while rolling cursor across screen, then unclick when completing the selection.
- For all remaining selections that you want to add on: Replicate Step 1, but this time while holding down Ctrl – you press Ctrl and keep it down until you’re done with your selection(s).
Once you let go of Ctrl, the regions/cells are still selected and you can perform actions (such as formatting) on all selected cells at once.
Below is the result if you do the following:
- Select B2:E2
- Hit Ctrl (and keep it down)
- Select B5:E6 (while keeping Ctrl down)
- Let go of Ctrl
Without adding the Ctrl key, you would lose the first region when moving to select the second one.
Question: Since selecting regions with the mouse (and Ctrl key) is so simple and fast, why would you ever resort to keyboard shortcuts? Why wouldn’t you just use the mouse and the Ctrl key all the time?
Answer: When the regions are very large (thousands of rows and dozens of columns). In this case, keyboard shortcuts are often much faster than the mouse.
I discuss shortcuts for navigating larger data sets in more detail in my Excel Shortcut System. I have 25 pages of detail, examples and advice. Below are a few examples:
Basic keyboard shortcuts for Excel data selection and navigation
We’ll start with the most basic and obvious – simply moving to a new cell.
Move To New Cell (keyboard): Arrow keys (←→↑↓)
You can select the cells you move through by adding the Shift key:
Select Contiguous Region of Excel Data (keyboard #1): Shift+Arrow key.
In other words, hold down Shift while moving around using the arrow keys (←→↑↓). By moving the arrow up and down you can basically emulate dragging the cursor around with the mouse. Just leave your finger on the Shift key until you’re done with your selection.
Now what if you want to select a large range – say 1,000 rows by 50 columns (ending at AX1000)? Using the only keyboard method we have so far (Shift+Arrow) would take a long time.
Select Contiguous Range of Excel Data (keyboard #1) — painfully slow: Shift+↓ until you get to the bottom, then Shift+→ until you get to the end (or vice versa)
The mouse isn’t much better. You can drag your mouse across the entire range (as discussed earlier), but that would be very time-consuming. You could potentially move somewhat faster by clicking on the scrolling arrows – see graphic below (select the first cell in the region, then scroll to the end and Shift+Click). But this is also slow because you still need to wait and wait until you get to your destination.
Select Contiguous Range of Excel Data (mouse #1) (slow): – select first cell, then click on scrolling arrows, then Shift+Click on last cell
A faster way is just to click on the space that’s between the scrolling arrows and the scrolling blocks – the “in between space.” This moves you one screen at a time (vs. one row/column at a time). When you do this, the space between the scrolling arrows and scrolling blocks will narrow as the scrolling block moves towards the arrow. Keep clicking – or just keep your mouse pressed – and the scrolling block keeps moving until it hits your cursor. To move as far as possible with this method, position your cursor as close as possible to the scrolling arrow before clicking. If you do this, the scrolling blocks should stop moving only when they get to the area of your last used cell.
Select Contiguous Range of Excel Data (mouse #2) (slow): – select first cell, click space between scrolling arrows and scrolling blocks, then Shift+Click on last cell
An even faster scrolling approach is to drag the scrolling block to the bottom and/or right of the pane.
Select Contiguous Range of Excel Data (mouse #3) (faster): select first cell, drag scrolling block to end, then Shift+Click on last cell
As with the other methods above, Excel seems to automatically know when it reaches the end of the data range and stops there.
While this last option is the fastest of the scroll bar options, you can go faster still by using keyboard shortcuts. Here’s a good one:
Select Contiguous Row/Column Of Cells (keyboard): Ctrl+Shift+Arrow.
All you’re doing is adding Ctrl to the Shift+Arrow shortcuts we already discussed. Ctrl allows you to jump to the beginning end of a contiguous section of data (thus saving you time). So to select the range B2:E2 start with your cursor on B2, then hit Ctrl+Shift+→. This selects B2:E2.
If you want to select all the way through H2, just press (and hold) Ctrl+Shift and then tap → until you get there (you will need to tap twice – once to jump from E2 to G2 (jumping over the blank) and then a second time to go from G2 to H2 (the end of the contiguous region G2:H2). You have now extended the selection to the full B2:H2.
Selecting non-contiguous ranges using only the keyboard is a little trickier. In between each selection you need to type Shift+F8 (its role is similar but not identical to Ctrl in this context). It’s a little quirky and we don’t have the space to cover any of the nuances here. But back to our earlier example of selecting two non-contiguous ranges, B2:E2 and B5:E6, here’s how you would do it with only the keyboard:
- Select B2:E2 (using Ctrl+Shift+→)
- Hit Shift+F8 (you should then see “Add To Selection” on the bottom left of the status bar)
- Arrow down to B5
- Select B5:E6 (using Ctrl+Shift+→ and Ctrl+Shift+↓)
Note: I plan to write Part II of this post at a later date (though it won’t be as detailed as my Excel Shortcut System).