Quickly Select Columns with Blank Cells in Between

Do you how to quickly select the columns with blank cells in between? In this blog, we would unlock the technique to quickly select the columns or rows that contain blank cells in between.

Let me explain this by taking a small example.

Suppose you have an Excel worksheet in which there is some data in a column as demonstrated the image below:

As you can see from the image above that there are multiple blank cells in between. 

One of the most commonly used ways which is generally known to all the Excel users is to use select the first cell in the dataset and then press the keyboard shortcut Ctrl + Shift + Down_Arrow.

However, this method is not as fast as expected and is sometimes irritating because while selecting the cells it would stop at every blank cell:

Then how can you actually do it quickly?

There are two ways that you can use to achieve this. Let us navigate with each of the methods one by one.

Select Data With Blank Cells Using Keyboard Shortcuts

Here I would show you two keyboard shortcut methods which will help you to select the rows or columns that contain blank cells in it.

Shortcut No. 1

Follow the below simple steps:

Select the first cell of the dataset. We have selected cell A1.

Now press Ctrl+Shift+End keys simultaneously on your keyboard.

As you can see, the dataset selects from its start to the end without stopping at the blank cells in between the dataset table.

However, this method has one limitation due to which it is generally not preferred. Where there is any filled cell in any column other than the dataset column, the excel would also include that filled cell during selection.

As you can see from the below screenshot, the cell C23 contains some text. When I try to select the dataset column using the above keyboard trick, the excel also includes this new cell C23 during selection.

This is taken care of by another keyboard shortcut.

Shortcut No. 2

Select the to most cell in the dataset. Therefore, I have selected the cell A1.

Now press Ctrl+G keys on your keyboard. This will open the “GoTo” dialog box as shown in the screenshot below:

In the “Reference” input field, enter the following string.

A1:A10000

Now click on the “OK” button.

This would select the cells from A1 to A10000.

In the above string, the part before the colon represents the start cell of the selection range and the one after the colon represents the last cell of the selection range. I have mentioned the second part after the colon as A10000 because I know that my last cell in the range would not go beyond the A10000. You can adjust this as per your data.

Now press Ctrl+Shift+Up_Arrow on your keyboard, to jump to the last filled cell in the range, as shown below:

Select Column With Blank Cells Using Mouse

Select the first cell of the dataset. I have selected cell A1.

Now click on the name box on the top left of the worksheet as shown in the screenshot below and enter the following string:

A1:A10000

Now press the “Enter” key on your keyboard. This would select the range from cell A1 to A10000.

Press Ctrl+Shift+Up_Arrow on your keyboard to select the range up to the last cell in the dataset.

This brings us to the end of this blog.