Selecting A Column With Blank Cells In Excel: The 2 Best Keyboard Shortcuts

In this video I explain 2 keyboard shortcuts to select a column that contains blank cells in Excel.

-- LINKS --

*** JOIN OUR COMPREHENSIVE EXCEL TRAINING PROGRAM***

-- Keyboard Shortcuts for Selecting Columns with Blank Cells --

It can be difficult to select a column of the used range or current region of data in Excel. Especially when the column contains blank cells.

The first technique requires that the data be in an Excel Table. The keyboard shortcut to select an entire column in a Table is Ctrl+Space.

When the active cell (the cell the user has selected) is in the body of the table, the entire column of the data body range will be selected when pressing Ctrl+Space.

Press Ctrl+Space a 2nd time to include the header cell in the selection.

Press Ctrl+Space a 3rd time to select the entire column of the sheet.

The 2nd technique uses a macro to attempt to select the column of used data on a regular range.

We can store the macro in our Personal Macro Workbook and assign a keyboard shortcut to it to run the macro.

Other videos mentioned in this video:
00:00 Introduction
00:44 Using an Excel Table
01:07 Control Space
02:03 Current Region
03:42 Select Entire Column

[joanhauff]

Thank you for the excellent video. I didn't realize that within a table (Ctrl + T), choosing Ctrl + Spacebar toggles from selecting a column > to selecting a column + table header > to selecting the entire column in the worksheet. Fabulous :).

[remikid]

It's really helpful. I've been looking for this. I tried it in excel 2007 and it works perfectly.👍

[ExcelCampus]

Thanks for watching! Here are the links mentioned in the video.

Other videos:

Please leave a comment below with questions or suggestions on alternative ways to select a column like this. Thanks again and have a great day! :-)

[nric]

Thanks Jon! Excel training in youtube is great. Looking forward to more video from you.

[muslahalshaiki]

Thank you very much Mr. Jon, your channel is making life easier :)

[matthaft]

Thanks so much Jon, great video!! looking forward to watching more of your videos!

[mcmielnik]

There is another option to select a column with blank cells (unfortunately with header). 1. Select a specific column 2. Click shift + ctrl + up (arrow), and you have highlighted column with blank cells, but as I mentioned with a header.

[Time_utilize]

Will it work with increasing data? Eg same working for 100 rows? Till end of data?

[etienne]

Dear Sir,
I have to compile a report from multiple reports. Including groups, with individual clients and sales representatives. I'm struggling to use pivot tables due to the amount of data. Any suggestions please?

[arunkumarkgr]

can you give me VBA code for alt + esv, for pasting values

[abduldangara]

Show keyboard hortcut keys to select columns required in blank sheet

[Vernad]

Sub
Range(cells(ActiveCell.CurrentRegion.Row, ActiveCell.Column), + ActiveCell.CurrentRegion.Row - 1, ActiveCell.Column)).Select
End Sub