Software

A quick way to select an Excel data range

Here's a quick method, and the equivalent keyboard shortcut, for selecting an entire data range.

Working with a complete data set is a common task in Excel. You might want to move the data, apply a filter or common format, or convert the data to a table. There are many reasons for selecting a data range, but doing so might require jumping through a few selection hoops, especially if you're working with a large range that spans several screens. Fortunately, there's a quick and easy way to select an entire data range.

To select a data range, use the Go To feature as follows:

  1. Click any cell in the data range. For instance, to select the data range A1:C9 (shown below) you could select any cell from A1 to C9.
  2. Press [F5].
  3. In the Go To dialog, click the Special button in the bottom-left corner.
  4. In the resulting dialog, click the Current Region option.
  5. Click OK, and Excel will select the current data range (the current region).

In this context, current region means contiguous data, but the block of contiguous data can contain blanks. For instance, using the above data range, if there's data in cell D10 and E7, this feature will include columns D and E and row 10 in the selection. Blank cells in the data range won't matter.

This feature makes selecting a data range easy, but there's a keyboard shortcut that will do the same thing: [Ctrl]+[Shift]+8. If you do this often, it's worth committing that keyboard shortcut to memory.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
farondean
farondean

Select a corner cell of the region - top left for example, then hold down the shift key and double-click on the right side of the cell (this selects the row of data), then double-click on the bottom of any cell in that row, while still holding the shift key, to select all rows of data. Dean

mvdarend
mvdarend

though not by much, just press Ctrl + A for the same effect. Mike

ssharkins
ssharkins

I knew you could double-click the border to extend a selection to the end by clicking the borders. I don't think I'd considered combining the two to select a data range. Thanks for sharing this one with us.

ssharkins
ssharkins

The only difference I've noted is this: if the current cell is in a blank region, Ctrl+A will select the entire sheet and Ctrl+Shift+8 won't select anything. Of course, you probably wouldn't use it in a blank region to select the current region, so it's a small difference -- probably one that wouldn't really make any difference in regular usage. I wonder if anyone else has noticed any differences between the two?

BlueCollarCritic
BlueCollarCritic

Actually there is a more of a difference between using [CTL]+[SHIFT]+8 and [CTL]+A then what has been discussed so far but I???m not sure exactly what it is over all, only the different results you get with each under several scenarios. EXAMPLE: Using the exact same example as posted in this article where you have a contiguous range of data from A1: C9 and then data in cells E7 and D10. Copy the range A1:E10 to A14 which will fill the range A14:E23. Now select cell D11 and press [CTL]+[SHIFT]+8. That will select the range A1:E11. Select Cell D11 and press [CTL]+A and the entire worksheet is selected. This same effect is repeated when selecting cell C11 or E11. However if you select cell B11 or F11 then [CTL]+[SHIFT]+8 selects nothing and [CTL]+A selects the entire worksheet. Now select cell E8 and press [CTL]+[SHIFT]+8 and the selected range is E7:E8, a very different result from when you did this with cell D11 selected. If you put some value into cell D7 and then select Cell E8 and press [CTL]+[SHIFT]+8 it will select the entire range again. Delete the contents of Cell D7 from the previous step. Now select cell E9 which is surrounded with empty cells and press [CTL]+A and the entire range is once again selected even though cell E9 is surrounded by empty cells. If however you select cell E8 which is NOT surrounded by empty cells and press [CTL]+A the entire worksheet is selected. It would seem like as if this is a contradiction. SUMMARY: I???m not yet sure what logic each method uses; [CTL]+[SHIFT]+8 vs [CTL]+A but its clear that they do not exactly mimic each other. NOTE: This was done using Excel 2010

Editor's Picks