Excel Hidden Shortcut to Select Column with Blank Cells

Excel has many shortcuts.

If you’ve ever worked with someone who’s learned a figurative ton of shortcuts, that person moves through spreadsheets faster than seems humanly possible.

Because Excel has a massive landscape (16K columns × 1M rows), learning shortcuts to navigate and select data can be among the most time-saving shortcuts in the program.

But sometimes our data is structured in a way that brings these shortcuts to their knees and makes them beg for mercy.

This post will show you a common data structure problem and a clever workaround.  Let’s get started.

There are two keys on our keyboard that aid in navigation and selection: CTRL and Shift.

  • The CTRL key tells Excel to move the cursor (the green box) to the end of the currently selected data set.
  • The Shift key tells excel to highlight cells.

When used with the arrow keys, we can perform very rapid movements and highlights.

Using the CTRL Key

If you have a large data set, using CTRL-↓ will place your cursor on the last row.  Using CTRL-→ will place your cursor in the last (right-most) column.

The opposite is true.  CTRL-↑ moves you to the top row and CTRL-← moves you to the first (left-most) column.

Excel stops the cursor when it encounters the first blank cell or sheet edge, whichever comes first.

Working with Multiple Tables

If you have several tables separated by blank rows/columns, repeated pressing of the CTRL-arrows would “walk” you through the tables.

  1. CTRL-↓ to move to the last row in the first table.
  2. CTRL-↓ to move to the first row of the second table.
  3. CTRL-↓ to move to the last row in the second table.
  4. CTRL-↓ to move to the first row of the third table.
  5. etc, etc, etc…

This assumes you don’t have any blank cells in your table(s).

Using the SHIFT Key

The Shift key is how we tell Excel to highlight.

If we hold the Shift key and repeatedly press one of the arrow keys, we will select cells as we move.

Using CTRL and Shift at the Same Time

By pressing CTRL-Shift and then using the arrow keys we are telling Excel to “move to the end and highlight along the way”.

It’s a fast and easy way to select a column or row of data.

You could even select an entire table using these keys.

  1. Select the upper-left cell of the table
  2. Press CTRLShift-↓
  3. Keep pressing CTRLShift
  4. Press →

This is good practice to get your finger acrobatics skills perfected.

NOTE: A simpler way to select a table is to click in the table and press CTRL-A.

Moving to the Last Used Cell

Additional navigation shortcut keys are the END and HOME keys.

  • CTRL-End will place the cursor in the last used cell.
  • CTRL-Home will place the cursor in cell A1.

If we start our table in cell A1, we could place our cursor in cell A1 and press CTRL-Shift-End to select the table.

NOTE: There is a slight catch to using the CTRL-End keyboard shortcut.  This will be discussed at the end of the post.

Encountering Data Speed Bumps

Take a look at the data set below.

Our goal is to highlight the Region data in column B.

The problem is there are empty cells scattered throughout the column.  If we try to use our CTRLShift-↓ trick, we are prematurely halted during our selection process.

Granted, we could keep repeatedly hitting CTRLShift-↓, but with dozens or hundreds of empty cells, we could be pressing keys for hours.  It would be faster to highlight the cells with a mouse.

The Trick to Selecting with Blanks

The trick to selecting a set of cells that contain blanks is to over-select.

Over-selecting is where you select more cells that you need, then reduce the selection to what you originally wanted.

There isn’t a keyboard shortcut for this over-selection act, so we’ll use the Name Box.

The Name Box has many useful features:

  • Tell us the cell address for the currently selected cell
  • Navigate to a cell by typing the cell address in the Name Box and pressing Enter
  • Select a range of cells by typing the range in the Name Box and pressing Enter (e. A1:F100)
  • Apply a name to a cell (like naming cell A1 “Tax Rate”) for use in formulas

We will type an address to select a range of cells.

  1. Click in the Name Box; the current cell address will turn blue.

  1. Erase the current address and type the address range you want to highlight (e. B1:B10000). Select an ending cell address that you know will place you well beyond your last row of data.  Any value will do as long as it is past your data.

  1. Press Enter.
  2. With the range selected, press CTRL-Shift-↑.

That’s it!  You have over-selected with the Name Box then reduced the selection to the last row of your data using the CTRLShift-↑ keyboard shortcut.

Another Way to Over-Select Cells

If you don’t want to use the Name Box for range selection, you can also use the “Go To” dialog box.

Press CTRL-G to launch the “Go To” dialog box.

In the Reference field, you can type the same cell range reference you previously typed in the Name Box.

The Problem with CTRL-End

I mentioned earlier that the CTRL-End keyboard shortcut has a small catch.  The catch is that CTRL-End places the cursor in the last used cell.  That sounds like what we want, but Excel isn’t thinking “last used” the way you are.

We think of “last used” as the “last cell holding data”.  Excel thinks of “last used” as the “last cell that ever had data, even if that data has been deleted.”

This can cause navigation issues.

Imagine a data set where you have placed data far to the right or below your table.

If you press CTRL-End, the cursor is placed in the last used cell, not the bottom-right corner of your data.

If we delete the renegade data outside of the table, we will think we have solved our problem; we haven’t.

The cursor is still placed in the cell that held the renegade data because Excel remembers that we have visited this place before.

This can be very frustrating for users who never saw the original renegade data and wonder why the CTRL-End shortcut is throwing their cursor into no-man’s land.

Resetting the End of Data Range

Using the above data set as an example, to get Excel to ignore the blank rows and columns where data used to exist, perform the following steps:

  1. Select the range of columns where data used to exist (e. E1:Z1) and delete the selected cell columns.
  2. Select the range of rows where data used to exist (e. A11:A100) and delete the selected cell rows.

At this point, you think you’ve won, but a quick retry of the CTRL-End trick shows you have yet to succeed.  Here are the steps that users don’t think to perform.

  1. Save the workbook.
  2. Close and reopen the workbook.

If we press CTRL-End the cursor is now resting in the last occupied cell (lower-right corner).

It’s the saving/closing/re-opening that resets Excel’s historical perspective.

Addendum

Due to several suggestions made after the publication of the above video/blog, I want to show you additional ways to achieve the same result when working with data that may have different structures or layouts then was demonstrated.

Extra Method #1

Here are the parameters:

  • The data begins in row 1
  • The data is not in a proper Data Table format, rather a plain table

  1. Select the first cell (row 1) of the column you are attempting to highlight
  2. Press CTRL-Space to select the entire column, all one million-plus rows
  3. Press CTRL-Shift-↑

Extra Method #2

Using the same parameters as Method #1:

  • The data begins in row 1
  • The data is not in a proper Data Table format, rather a plain table

  1. Select the first cell (row 1) of the column you are attempting to highlight
  2. Press CTRL-End to select to the last used cell in the sheet
  3. Repeatedly press Shift-← to move left to the originally selected column

Extra Method #3

Here are the parameters:

  • The data does not begin in row 1
  • The data is not in a proper Data Table format, rather a plain table

  1. Select the first cell in the data of the column you are attempting to highlight (typically the header)
  2. Press CTRL-Space to select the entire column, all one million-plus rows
  3. Press CTRL-Shift-↑
  4. Press CTRL-. (period) to move the selected cell to the last highlighted cell
  5. Press CTRL-Shift-↓

Extra Method #4

Here are the parameters:

  • The data is in a proper Data Table format

  1. Select the first cell in the data of the column you are attempting to highlight (typically the header)
  2. Press CTRL-Space

Because the data is in a proper Data Table format, the highlight is restricted to the table’s column boundaries.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials