It was brought to my attention how to select cells that are not next to each without the Control key and the mouse! Michael Castelluccio wrote an article for the Strategic Finance website that explains how to do it.

Most people know how to select multiple ranges in Excel using the Ctrl key and the mouse, but there are other methods that can be more efficient. The obscure technique Add to Selection lets you select many ranges without ever using the mouse. And Go To Special lets you quickly find and select all the cells of a specified type.

 ADD TO SELECTION

No Mouse Needed

Let’s say that you want to apply a Color Scale to the data cells in the figure below. This means you need to select cells B4:D8, G5:I8, and D11:F13. Start by selecting cell B4. To select the first block of data, hold down Ctrl+Shift and press the down arrow (↓) and then the right arrow (→). This common keyboard trick selects all the way down to the bottom and the right edge of the data.

NonContiguous selection 1

After selecting the first cell range, most people would switch to using the mouse. But it’s faster if you can keep your hands on the keyboard. Press Shift+F8. You’ll see the words “Add to Selection” in the status bar at the bottom of the Excel screen.

Move the cell pointer to the next section of data: Ctrl+↓, Ctrl+→, Ctrl+↓, and then ↓. The original range, cells B4:D8, stays selected while you move the cell pointer outside of the range.

To select the second range, hold down Ctrl+Shift and press ↓ then →. You’ve now selected B4:D8 and D11:F14 (see figure below). But now the “Add to Selection” is gone from the status bar. Press Shift+F8 to return to Add to Selection mode.

NonContiguous selection 2

Move the cell pointer to cell G5. Hold down Ctrl+Shift and press ↓ then → to select the third block of cells (see figure below).

NonContiguous selection 3

 

Extended Selection

In Extend Selection mode, any click of the mouse will select from the active cell to the newly clicked cell, obliterating any previous selection. While I’ve never found a good reason to use Extend Selection, there clearly are times when using Shift+F8 for Add to Selection will save time.

I will like to add my advice here as well.  I do not use Extend Selection (F8) because I use the Shift key and the arrow keys to do the same. F8 on my keyboard is dark blue on a black key and I need to use the FN key to make the function keys active. So, since the shift key replaces the left mouse button, I highlight the cells this way.  The mouse is so fast that it highlights more cells that I need.  I am in control of highligting. (Hint: I do the same method in Word).  So, Michael, this is quicker and better for those who have difficulty controlling the mouse and/or who can’t find the F8 in dark blue.

Advertisements