Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, a dataset usually contains data in each cell. But there may be some cells without data or blank. Selecting the cells with data manually is time-consuming work. Here, we will show 8 methods to select all cells with data in a column in Excel.

Table of Contents hide

How to Select All Cells with Data in a Column in Excel (5 Easy Methods)

1. Select All Cells with Data from a Column Using Go To Special Command

2. Use Excel Table Feature to Select All Cells with Data

3. Select Data Cells of Column Using Filter Command

4. Apply Conditional Formatting to Select Cells with Data in a Column

5. Excel VBA to Select All Cells with Data in a Column

3 Keyboard Shortcuts to Select All Cells with Data in a Column in Excel

1. Select All Cells in a Column in Excel

2. Choose Contiguous Data Cells

3. Select All Cells in the Dataset

Conclusion

How to Select All Cells with Data in a Column in Excel (5 Easy Methods)

We want to select only cells with data in a column in Excel. Here are 5 methods and 3 keyboard shortcuts for this operation. We will use the following dataset in this article.

1. Select All Cells with Data from a Column Using Go To Special Command

We will use the Excel Go To Special tool to select all cells that contain data from a column.

Step 1:

First, select the cells of the Name column to check data availability.
Go to the Editing group from the Home tab.
Click on the Find & Select option.
Choose Go to Special from the list.

Step 2:

The Go to Special window will appear now.
Choose Constants from the list.

Step 3:

Now, press OK and look at the dataset.

You can see that cells with data are selected.

We have other ways to avail Go To Special tool.

Press Ctrl+G or just press the F5 button.
Go To dialog box will appear.
Then click the Special option.

Then Go To Special window will appear and next follow Steps 1 and 2.

2. Use Excel Table Feature to Select All Cells with Data

We will use the Excel Table tool in this section to select cells with data in a column.

Step 1:

First, press Ctrl+T to create a table.
Create Table dialog box will appear.
Choose the column range from the dataset.
Put a tick mark on My table has headers box and click OK.

Step 2:

A filter sign will show in the Name heading cell. Press the down arrow sign.
Untick the Blanks option from the list and press OK.

Now, look at the dataset. Only cells with data are shown here.

We can also use Ctrl + L to create a table.

3. Select Data Cells of Column Using Filter Command

We will use the Filter tool in this section. Data cells of a column are easily selected in this way.

Step 1:

First, select the Name column.
Go to the Editing group from the Home tab.
Select the Sort & Filter option.
Choose Filter from the list now.

Step 2:

We can see that Filter is available on the Name Click on the down arrow.
Untick Blanks from the list and then press OK.

Look at the dataset now. Only cells with data in Name column are showing.

We can also avail of the Filter feature using a simple keyboard shortcut. Just press Ctrl+Shift+L.

Similar Readings

How to Select Non-Adjacent Cells in Excel

4. Apply Conditional Formatting to Select Cells with Data in a Column

Conditional Formatting will highlight the cells with data in a column.

Step 1:

First, select cells of the Name column.
Go to Conditional Formatting from the Home tab.
Choose More Rules from the list of Highlight Cells Rules.

Step 2:

A new dialog box will appear. Set No Blanks on the Format only cells with the field.
Then, press Format.

Step 3:

Go to the Fill tab of the Format Cells
Choose a color and press OK.

Step 4:

Again, press OK to implement the condition.

Look at the dataset. Cells with data are highlighted.

5. Excel VBA to Select All Cells with Data in a Column

We will apply a VBA code to highlight cells with data in a column.

Step 1:

Go to the Developer tab first.
Select the Record Macro option.
Set the name of the Macro and press OK.

Step 2:

Now, click the Macros option.
Select the macro and Step Into it.

Step 3:

Copy and paste the following VBA code on the module.

Sub Select_Data_Cells()
Dim range_1, range_2, range_3 As Range
Set range_2 = Application.Selection
Set range_2 = Application.InputBox("Range", xTitleId, range_2.Address, Type:=8)
For Each range_1 In range_2
    If range_1.Value <> "" Then
        If range_3 Is Nothing Then
            Set range_3 = range_1
        Else
            Set range_3 = Union(range_3, range_1)
        End If
    End If
Next
If Not range_3 Is Nothing Then
    range_3.Interior.ColorIndex = 37
End If
End Sub

Step 4:

Press F5 to run the code.
A dialog box will appear to input the range. Select the range from the dataset.

Step 5:

Now, press OK and look at the dataset.

The cells containing data are highlighted in the dataset.

3 Keyboard Shortcuts to Select All Cells with Data in a Column in Excel

1. Select All Cells in a Column in Excel

We want to select all cells of the entire column. We will apply a simple keyboard shortcut for this.

Steps:

Willing to select the Cells of Column D. Go to Cell D7 first.
Now, press the Ctrl + Space bar.

Look at the dataset. The entire column is selected here.

2. Choose Contiguous Data Cells

This keyboard shortcut is applicable when we have contiguous data in a column. When any blank is found this operation will stop.

Steps:

Go to Cell B5 first.
Now, press the Ctrl+Shift+ Down arrow.

Look at the dataset. The selection operation stops when there is a blank.

3. Select All Cells in the Dataset

We want to select all the cells of the dataset in this section. A simple keyboard shortcut will be used for this.

Steps:

Select any cell of the dataset. Go to Cell B5.
Now, press Ctrl + A.

We can see that all the cells of the data set are selected. If we press Ctrl+A again that will select the entire worksheet.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Select All Cells with Data in Column.xlsm

Conclusion

In this article, we showed how to select all cells with data in a column in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy and give your suggestions in the comment box.

Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)

How to Select All Cells with Data in a Column in Excel (5 Easy Methods)

1. Select All Cells with Data from a Column Using Go To Special Command

2. Use Excel Table Feature to Select All Cells with Data

3. Select Data Cells of Column Using Filter Command

4. Apply Conditional Formatting to Select Cells with Data in a Column

5. Excel VBA to Select All Cells with Data in a Column

3 Keyboard Shortcuts to Select All Cells with Data in a Column in Excel

1. Select All Cells in a Column in Excel

2. Choose Contiguous Data Cells

3. Select All Cells in the Dataset

Conclusion

Related Articles

Leave a reply Cancel reply