Select All Cells with Data in Excel (5 Easy Methods)

In this article, I will discuss how you can select all cells with data in excel. When a large dataset contains data in discontinuous cells, it is tough to select all data containing cells one by one. So, I will show you some easy tricks to select all randomly located data cells. Besides, I will also discuss how you can select all contiguous cells that have value on them.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


5 Easy Methods to Select All Cells with Data in Excel

1. Select All Cells with Values Using ‘Go To Special’ Command in Excel

Let’s consider, I have a dataset (B4:E14) that has several area’s fruit sales data date-wise. But, the dataset has some blank cells. Now I will select all the non-contiguous cells with data using the ‘Go To Special’ command in excel.

Select All Cells with Values Using ‘Go To Special’ Command in Excel

Steps:

  • First, select the entire dataset (B4:E14).

Select All Cells with Values Using ‘Go To Special’ Command in Excel

  • Then form Excel Ribbon, go to Home > Editing > Find & Select > Go To Special.

Select All Cells with Values Using ‘Go To Special’ Command in Excel

  • As a result, the ‘Go To Special‘ dialog box will appear. Now, choose the Constants option and press OK.

Select All Cells with Values Using ‘Go To Special’ Command in Excel

  • Once you press OK, by default excel will highlight all the cells that are with data.

Read More: How to Select Cells with Certain Value in Excel (5 Methods)


2. Excel VBA to Select All Cells with Data

You can use VBA to select all the non-contiguous cells that contain data. Let’s follow the below steps to do the task.

Steps:

  • Go to the worksheet that contains the dataset. Then right-click on the corresponding sheet name and select View Code.

Excel VBA to Select All Cells with Data

  • As a consequence, the VBA window will show up. Type the below code in the Module and run the code using the F5 key. Or you can use the Run Sub/UserForm icon to run the code.
Sub SelectwithData()
Dim abc As Range
Dim WorkRange As Range
Dim OutRange As Range
On Error Resume Next
xTitleId = "Select Cells with Data"
Set WorkRange = Application.Selection
Set WorkRange = Application.InputBox("DataRange", xTitleId, WorkRange.Address, Type:=8)
For Each abc In WorkRange
    If abc.Value <> "" Then
        If OutRange Is Nothing Then
            Set OutRange = abc
        Else
            Set OutRange = Union(OutRange, abc)
        End If
    End If
Next
If Not OutRange Is Nothing Then
    OutRange.Select
End If

End Sub

Excel VBA to Select All Cells with Data

  • Upon running the code the following message box will pop up. Enter/select the dataset range here and press OK.

Excel VBA to Select All Cells with Data

  • Consequently, you will see that all the cells that have data are highlighted below.

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


3. Select All Cells with Specific Data Using Excel Find Option

You can select cells that have a particular text, number, value, etc. using the Find option in excel. The advantage is, by using the Find option, you can select all the particular data containing cells whether they are contiguous or not. For example, my dataset contains the fruit ‘Apple’ several times. Now I will select all the cells that have ‘Apple’ in them.

Steps:

  • Firstly, select any cell in the sheet where you have the dataset.

Select All Cells with Specific Data Using Excel Find Option

  • Next, press Ctrl + F from the keyboard to bring up the Find and Replace dialog box. Now, from the Find tab, type ‘Apple’ in the Find what field and press Find All.

Select All Cells with Specific Data Using Excel Find Option

  • Clicking on Find All will give you the list of cell references that have ‘Apple’ in them. Press Ctrl + A to select the entire list. Finally, you will see all the cells with ‘Apple’ is highlighted when the result of Find All is selected.

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


Similar Readings


4. Select All Cells with Data Using Mouse Cursor

Now, I will show you how to select all cells with data simply by dragging the mouse cursor. Suppose we have the below dataset that has data in multiple contiguous cells.

Select All Cells with Data Using Mouse Cursor

Steps:

  • Put the mouse cursor in the first cell (here, B4) of the dataset.

Select All Cells with Data Using Mouse Cursor

  • Then just drag the cursor to the end cell (here, E14) of the dataset. Consequently, excel will auto-select the entire dataset.

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


5. Use Keyboard Shortcut to Select All Cells with Data

You can select all the contiguous cells with data using the keyboard shortcut. Let’s see how to do that. I will use the same dataset which I have used for the previous method.

Steps:

  • Select any cell of the dataset. For instance, I have selected Cell C5.

Use Keyboard Shortcut to Select All Cells with Data

  • Then, press Ctrl + A. As a consequence, the whole dataset is highlighted as we have data in each cell of this dataset.

Read More: How to Drag Cells in Excel Using Keyboard (5 Smooth Ways)


Conclusion

In the above article, I have tried to discuss several methods to select all cells with data in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Tags:

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo