Save Time with Excel Range Selections

One of the big differences between early-stage Excel users and gurus is that gurus can do simple things in Excel much more quickly than other users can.

They can do this because they understand how Excel works in situations that most Excel users experience frequently. For example…

Change the Boundaries of a Selection

If you want to use your keyboard to extend or reduce the range shown here, you hold down your Shift key and then press an arrow key.

Suppose you press Shift+DownArrow, what would your new range be? And why?

I’ll answer the “why” question first, because it’s the most important…

When you use Shift plus some arrow key, Excel always moves the corner of your selected range that’s opposite your active cell.

Therefore, in this case, pressing Shift+DownArrow one time will move the opposite cell from C4 to C5…extending your selection to range B2:C5.

Now, returning to the original range shown above, suppose you want to extend your selection to the left. That is, suppose you want to select the range A2:C4. How would you do it with your keyboard?

You can do it with two keystrokes…

Keystroke 1: Press Ctrl+. (period) to move your active cell clockwise to the next nearest corner. Doing so, of course, also moves the opposite cell, as well…to cell B4.

Keystroke 2: Press Shift+LeftArrow to move the newly positioned opposite cell to the left, extending your selection to the range A2:C4.

Copy and Paste Long Columns of Formulas

This image shows a situation that I face frequently…

Here, column B contains many values. There could be hundreds of values, or even thousands of them in the column.

And cell C3 contains a formula that I want to copy and paste parallel to the data in column B. How could you do it quickly?

Here’s the slow way to do it, a way that I’ve seen many Excel users use…

1. Press Ctrl+C to copy cell C3.
2. Select cell C4.
3. Hold down your Shift key.
4. Tap on the Page Down key many times until you can see the cell in column C that’s adjacent to the last cell of data in column B.
5. Move your selection up or down with your arrow keys until the opposite cell of your selection is in the adjacent cell where your column of formulas should end.
6. Press Ctrl+V to paste your data.

Here’s a fast way…

1. Press Ctrl+C to copy cell C3.
2. Tap your LeftArrow key once to select cell B3.
3. Press Ctrl+DownArrow, which causes your active cell to slide down your column of data to the last cell that contains contiguous data in column B.
4. Tap your RightArrow key once to select the adjacent cell in column C.
5. Press Ctrl+Shift+UpArrow to extend the selection up column C until the opposite cell bumps into cell C3.
6. Press Ctrl+V to paste your data to the selected area of column C.

And here’s the fastest way, which works only on vertical lists…

1.  Select cell C3.
2. Double-click on the tiny black square in the bottom-right corner of the cell.
3. After you do so, Excel will copy and paste your formula parallel to the Value column.

Yes, each of the first two methods uses six steps. But step 4 of the slow way describes what could be many, many keystrokes. And the fast way describes just six keystrokes.

Of course, the fastest way is best.

Select Cells Based on Their Content

You easily can select all cells in a range—or in an entire worksheet—that contain empty cells, numbers, text, error values, formulas, and more.

To do so, you use the Go To Special dialog, shown here.

To launch the dialog, press either the F5 key or choose Ctrl+G. to launch the Go To dialog. Then, in the bottom-left corner of the dialog, choose Special…

If you’ve not used this dialog very often, here are some ideas to get you started…

Select Constants in a Report

Suppose someone gives you a large workbook and you want to make sure that its report sheet contains only formulas linked to data, not hard-coded values.

To do so, you’d first make sure that only one cell in the report worksheet is selected. (If more than once cell is selected, the dialog checks only the selected range.)

Then you’d launch the Go To Special dialog, and choose Constants. When you do so, you could choose any combination of Numbers, Text, Logicals, and Errors, as shown in the grayed-out section above. But in this example, you’d choose only Numbers.

Then, when you press OK, Excel would select every cell in your worksheet that contains a number.

Select Column Differences

Suppose you get another worksheet that looks like this. Here, the Values column appears to contain numbers, and the two formula columns appear to contain formulas.

You can check both those assumptions by using the Go To Special dialog twice. First, you can use it to select cells with constants that are numbers. Second, you can use it to select cells with formulas.

However, you also can use it to check that all the formulas are consistent. To illustrate, here are the formulas in the cells shown:

B2:  =A2*2
C2:  =A2+B2

But how are you to ensure that all formulas in each column contain equivalent formulas?

To do so, first select the range B2:C6, as shown in the left figure here:

Launch the Go To Special dialog. Then select the Column differences option, which selects every cell in the two columns that don’t have a formula equivalent to the formulas in the row of the active cell…with each column treated independently.

The right figure shows that Excel selected three cells, which have these formulas:

B4:   =1+5
C5:   =3+3
C6:   =30-3

Each formula, as you can see, is quite different from the formulas in cells B2 and C2.

Select Empty Cells

Finally, suppose you get a table of data that looks like this, with sales for each product listed as shown. This layout needs to be changed, because you can’t sort the data, and you can’t summarize it with functions like SUMIFS.

What you need to do, of course, is to fill in the empty cells in column A with the product names above each cell.

You can do this easily…

1. Select the range A2:A9, which is the range of cells you need to modify.

2. Launch the Go To Special dialog; choose the Blanksoption; then press OK. After you do so, Excel selects the empty cells, as shown here.

3. In your formula bar, type the equal sign followed by a reference to the cell immediately above the active cell. So, in this example, you would type =A2.

4. Hold down your Ctrl key and then press Enter. After you do so, Excel enters the same formula in every selected cell. That is, it fills the empty cells with formulas that return a reference to the cell immediately above each cell. By doing so, it fills the empty cells with the correct product labels.

5. To turn all the formulas into values, select the range A2:A9 again, copy the range, and the choose Home, Clipboard, Paste, Paste Special, Values, OK.