Selecting Entire Columns That Have Blank Cells

If your selection is at the top of a column, and you want to quickly select the entire column, you can use the keyboard shortcut Ctrl + Shift + . However, this ONLY works if there are no blank cells in the column.

Ctrl + Shift + Down Arrow

Columns that have blank cells require you to repeatedly hit the down arrow to make it all the way down to the end.

Down Arrow Multiple Times

So, is there a workaround?

There are two things you could do to achieve the same result and potentially save you some time.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

Option 1

The first is to hit Crtl + Shift + End. This will take you all the way to the bottom right cell in your data range.

Ctrl + Shift + End

From there, you can hold Shift down while you hit the left arrow until only your desired column is selected.

Shift + Left Arrow

Option 2

Another option is available if you have a column with no blank cells directly adjacent to your desired column selection. If so. You can start from the top of that non-blank column and hit Ctrl + Shift + .

Ctrl + Shift + Down Arrow

Then Shift + .

Shift + Right Arrow

Then hit Tab to put the active cell at the top of your desired column.

Tab

And finally, Shift + to deselect the original column.

Shift + Right arrow

Excel Tables

If you are using an Excel Table, the process is much easier. With any cell in the desired column selected, all you have to do is hit Ctrl + Space. That will select the entire column.

Ctrl + Space with Excel Tables

Do you use any of these methods, or do you find yourself just hitting the down arrow multiple times? Or maybe you tend to use the mouse instead? I'm curious to know your preference. Tell me in the comments, and feel free to leave any questions there as well.

8 comments

Your email address will not be published. Required fields are marked *

  • I tend to left click then scroll down with my mouse and then shift+left click to selct the entire column. I didnt realise you could tab across while the column was selected and then ctrl+shift+right to make the single column selection! Will be doing that from now on. Cheers

  • Thank you for those great workarounds! Until now, I would just keep hitting ctrl shift down arrow till the end. Now I think option 1 will be the fastest way to go in most cases. What about a quick way to highlight only the non blank cells. I either use sumif or a data filter to filter out the blanks.
    When will the new CHECK BOX in cells feature be available to all 365 users?
    thank you

  • Thank you Jon, love it. Did not know this one and gonna make user of this one a lot. Enjoy these short quick info session.

  • Hi. If the cells are filled manually it’s easy just to put a space in emty cells.
    This option works and doesn’t disturb formulas.

  • I have used all of these shortcuts in the past, but for years I’ve been using a much easier solution, though it does require a 3rd party extension.

    ASAP-Utilities is a cheap ($50 for commercial use; free for non-profit, student or personal use) suite of macros that streamlines a ton of common tasks (and lots of uncommon tasks). Two of these tools extend the current selection to the last row or column of data, and can be linked to personalized keyboard shortcuts. It’s an enormous time-saver!

    Disclaimer: I have no connection to ASAP, except as a long-time, satisfied user, and wish to spread the news of a great tool to other Excel users.

  • Since my datasets are not overly huge, I usually just click on the top cell of the column, then use my mouse wheel to scroll down to the bottom cell of the column. Then while holding the Shift key down, I click on the bottom cell. This highlights the column for me. I know that’s not what you were looking for, but with smaller datasets, it is easy.

  • Happy holidays, Jon! I usually freeze the top row(s) so that I can scroll all the way down, select the bottom-most cell of the first column, shift + click the cell right above the frozen line, then shift + down. This is particularly useful when I need to select several columns in a big table for a vlookup function (I know, so old school of me lol). I also use this as a way to select column(s) in non-tables and/or ctrl + space.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools