How to AutoSelect a Cell Range in Excel 2016

By Greg Harvey

Excel’s AutoSelect feature provides a particularly efficient way to select all or part of the cells in a large table of data. AutoSelect automatically extends a selection in a single direction from the active cell to the first nonblank cell that Excel encounters in that direction.

You can use the AutoSelect feature with the mouse and a physical keyboard. The general steps for using AutoSelect to select a table of data with the mouse are as follows:

  1. Click the first cell to which you want to anchor the range that you are about to select.

    In a typical data table, this cell may be the blank cell at the intersection of the row of column headings and the column of row headings.

  2. Position the mouse pointer on the edge of the cell in the direction you want to extend the range.

    To extend the range up to the first blank cell to the right, position the mouse or Touch pointer on the right edge of the cell. To extend the range left to the first blank cell, position the pointer on the left edge of the cell. To extend the range down to the first blank cell, position the pointer on the bottom edge of the cell. And to extend the range up to the first blank cell, position the pointer on the top edge of the cell.

  3. When the pointer changes shape from a cross to an arrowhead, hold down the Shift key and then double-click the mouse.

    As soon as you double-click the mouse or Touch pointer, Excel extends the selection to the first occupied cell that is adjacent to a blank cell in the direction of the edge that you double-clicked.

To get an idea of how AutoSelect works, consider how you use it to select all the data in the table (cell range A3:J8) shown in the following figures. With the cell cursor in cell A3 at the intersection of the row with the Date column headings and the column with the Part row headings, you can use the AutoSelect feature to select all the cells in the table in two operations:

  • In the first operation, hold down the Shift key and then double-click the bottom edge of cell A2 to highlight the cells down to A6, selecting the range A2:A6. (See the following figure.)

    Selecting the cells in the first column of the table with AutoSelect.

    Selecting the cells in the first column of the table with AutoSelect.
  • In the second operation, hold down the Shift key and then double-click the right edge of cell range A2:A6 to extend the selection to the last column in the table (selecting the entire table with the cell range A2:J6, as shown here).

    Selecting all the remaining columns of the table with AutoSelect.

    Selecting all the remaining columns of the table with AutoSelect.

If you select the cells in the first row of the table (range A2:J2) in the first operation, you can then extend this range down the remaining rows of the table by double-clicking the bottom edge of one of the selected cells. (It doesn’t matter which one.)

To use the AutoSelect feature with the keyboard, press the End key and one of the four arrow keys as you hold down the Shift key. When you hold down Shift and press End and an arrow key, Excel extends the selection in the direction of the arrow key to the first cell containing a value that is bordered by a blank cell.

In terms of selecting the table of data shown, this means that you would have to complete four separate operations to select all of its cells:

  1. With A2 as the active cell, hold down Shift and press End+down-arrow key to select the range A2:A6.

    Excel stops at A6 because this is the last occupied cell in that column. At this point, the cell range A2:A6 is selected.

  2. Hold down Shift and then press End+right-arrow key.

    Excel extends the range all the way to column J (because the cells in column J contain entries bordered by blank cells). Now all the cells in the table (the cell range A2:J6) are selected.