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. To select 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.

Download Practice Workbook

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

5 Methods to Select All Cells with Data in a Column in Excel

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 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 Find & Select option.
• Choose Go to Special from the list.

Step 2:

• 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.

Read More: How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)

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.

Read More: How to Select Cells in Excel Using Keyboard (9 Ways)

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

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

Read More: Multiple Excel Cells Are Selected with One Click (4 Causes+Solutions)

Similar Readings

• How to Delete a Cell in Excel (4 Easy Ways)
• Excel if One Cell Equals another then Return another Cell
• Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
• How to shift cells in Excel
• How to Shift Cells Down in Excel (5 Easy Ways)

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.

Read More: How to Select a Range of Cells in Excel Formula (4 Methods)

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.

Read More: How to Select a Range of Cells in Excel (9 Methods)

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 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 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.

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.com and give your suggestions in the comment box.

Related Articles

• How to Display the Cell Formulas in Excel (6 Methods)
• Lock Cells in Excel Formula (2 Easy Ways)
• How to Unlock Cells without Password in Excel (4 Methods)
• Group Cells with Same Value in Excel (3 Methods)
• How to Unlock Cells without Password in Excel (4 Methods)
• How to Divide a Group of Cells by a Number in Excel (3 Methods)