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

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.

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 the Name column are showing.

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

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