Excel Hidden Shortcut to Select Data Column including Blanks

You probably know the quickest way to select a column of data in Excel is with the shortcut CTRL + Shift + ⯆. But what do you do if that column includes blank cells and you'd like to highlight the entire column of data? CTRL + Shift + down arrow key will stop the moment it comes across a blank cell.

This is often a very annoying problem especially if it's a large data set or there are a lot of blank cells. In this video I'm going to show you a trick which EASILY gets this done.

A shortcut which is often used is CTRL + End which jumps to the end of the data set. However, that may not always work if there is some additional input in other cells outside of the data set.

Here's the Excel Keyboard Shortcut(s) to Select Column with Blank Cells:
A better alternative is to use the Name Box in Excel. Just put in the range you want and use a random, very high "TO" value where you know you will not have any data anymore, e.g. B1:B10000. Press Enter. This will highlight the entire range. Then use the short cut CTRL + SHIFT + ⯅. This will highlight the data set including the blank cells.

The same thing can also be done with just the keyboard, no mouse. Press CTRL + G to get to the Name Manager. In the reference field type in the range, e.g. B1:B10000 and press Enter. Then use the short cut CTRL + SHIFT + ⯅. This will highlight the data set including the blank cells.

This video was sponsored by Skillshare.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[ctanexai]

Hats off. Been following you since some times now, have learnt a lot.
I Use Ctrl+Space then Ctrl+Shift+Up

[vida]

Thanks for this video, which also generated so many useful comments. Assume data set starts not in the first row but in row 4 and ends in row 225 and has blanks in the middle. To select only rows 4 - 225: 1. select whole column with mouse; 2. Ctrl Shift Up; 3. Ctrl period; 4. Ctrl Shift Down

[gbanik]

Another variation that I use regularly... click the column and then press Ctrl+Shft+Up. Tad bit easier.

[davidferrick]

I love the wealth of knowledge you share with us Leila, even with little things like this. But, I also love how many of your followers share other tips and trips and @Gautam K Banik's way of select the items in a column with blanks was a fantastic contribution.

[hariramkumar]

I used to convert it to table (ctrl + T), and use the down arrow near column header to select the specific column range

[edge]

Hi Leila, this how I do it.
Type only the last cell reference e.g. B10000 (no need to type colon : then another cell reference) then press SHIFT+Enter and this will highlight column B until row 10000. Then press CTRL+Backspace to go back to the first cell.

[dhaval]

Thank you Very Much Madam You solved My problem. I Prayed for you. God Bless You.

[stephaneenglish]

I don’t know why everyone isn’t using structured tables in Excel, by now. There are so many benefits! One (of the many)is that you can position your mouse cursor at the top of a column, the cursor turns into a downward pointing arrow and then a single left-click will select the whole of that column.
Positioning the mouse at the left of a row in a structured table will do the same thing for selecting a whole row. Positioning the mouse in the top-left corner gives a diagonal arrow cursor symbol and allows you to click to select the whole of the table contents, without the header row. Finally, hovering the mouse anywhere along the border of the table gives a four-way arrow cursor and clicking will select the whole table, including the header row.
Structured tables and structured referencing in Excel are brilliant!

[karhukivi]

Excellent, Leila! And thank you to all the helpful comments from viewers showing other ways to select columns in specific ways - I've learnt a lot!! :-)

[paulthesaintsfan]

Might be a longer way around but if there’s a full column next to the one I need, I’ll highlight the full column using Ctrl+Shift+Down Arrow, then use Shift+Right Arrow to include the column I need, then Ctrl+Period to move active cell to the column I need and then Shift+Right Arrow again until the only column highlighted is the one I want

Really enjoy the videos Lelia thank you

[yashmalde]

Crtl+Space then Ctrl+Shift+Up works for me.😃

[Stryyder]

You can also shift+ctrl+end. Then hold shift and close the range by tapping the left arrow key while holding shift. It is typically faster than what you have shown, and uses the keyboard only

[harshpadia]

Thanks a lot for this wonderful gift. Its very good.

[fredphlogiston]

you can improve this further by selecting the whole row. insteat of selelcting b1:b10000 you use b:b. This has the advantage to be shorter and you don't have to think which low end cell you have to select to get last number.

[wayneedmondson]

Hi Leila.. great tips.. thanks for sharing them. Thumbs up!!

[amitkapse]

TONS of Thanks!! that's really helpful...

[ukjonas]

I wonder if this could be written into a dynamic VBA macro and added to the personal workbook, and an Icon added to the tool bar.... may have to have a play :)

[jerichojon]

Hey, thanks a lot for this short one, but really useful. Make tasks less annoying.

[Softwaretrain]

Thanks.
I have an easier way.
Select first cell of your data>
Alt+F3 (to go to name box in new version) or Ctrl+G or F5 (in all version)>
Just put address of last cell (Ex: A1000) and instead of Enter, press Shift+Enter>
Then Ctrl+Shift+Up

[RogerStocker]

Hi Leila

You get a thumb-up from me, even when I thing my way is easier.
1. Move cursor anywhere into column w/o any empty cell an press Ctrl+↓
2. move with ← or → to climn to be selected.
3. press ctrl+shift+↑ (it's done)
4. optionally you can make top cell active by pressing tab-kay