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

In this article we’ll discuss 5 methods to select all cells containing data in a column, and 3 related keyboard shortcuts. We’ll use the following dataset to illustrate.

Method 1 – Using Go To Special Command

Steps:

Select the cells of the Name column.
Go to the Editing group from the Home tab.
Click on the Find & Select option.
Choose Go to Special from the list.

The Go to Special window will appear.

Choose Constants from the list.

Click OK.

The cells with data are selected.

There are alternative ways to access the Go To Special tool:

Press Ctrl+G or F5 to open the Go To dialog box.
Click the Special option.

The Go To Special window will appear.

Method 2 – Using Excel Table Feature

Steps:

Press Ctrl+T to create a table.

The Create Table dialog box will appear.

Choose the column range from the dataset.
Tick the My table has headers box.
Click OK.

A Filter sign will show in the Name heading cell.

Press the down arrow sign.
Untick the Blanks option from the list.
Click OK.

Only cells with data are displayed.

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

Method 3 – Using Filter Command

Steps:

Select the Name column.
Go to the Editing group in the Home tab.
Select the Sort & Filter option.
Choose Filter from the list.

A Filter is available on the Name heading.

Click on the down arrow.
Untick Blanks from the list.
Click OK.

Only cells with data in the Name column are showing.

We can also access the Filter via the keyboard shortcut Ctrl + Shift + L.

Method 4 – Using Conditional Formatting

Steps:

Select the cells of the Name column.
Go to Conditional Formatting on the Home tab.
Choose More Rules from the Highlight Cells Rules list.

A New Formatting Rule dialog box will appear.

Set No Blanks from the Format only cells with drop-down list.
Click Format.

A Format Cells dialog box will appear.

Go to the Fill tab.
Choose a color.
Click OK.

Click OK to implement the condition.

Cells with data are highlighted.

Method 5 – Using VBA Code

Steps:

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

Click the Macros option.
Select the macro and Step Into it.

Copy and paste the following VBA code into 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