Hosne Ara
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.
Steps:
- First, select the entire dataset (B4:E14).
- Then form Excel Ribbon, go to Home > Editing > Find & Select > Go To Special.
- As a result, the ‘Go To Special‘ dialog box will appear. Now, choose the Constants option and press OK.
- 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.
- 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
- Upon running the code the following message box will pop up. Enter/select the dataset range here and press OK.
- 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.
- 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.
- 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
- How to Display the Cell Formulas in Excel (6 Methods)
- 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 Down in Excel (5 Easy Ways)
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.
Steps:
- Put the mouse cursor in the first cell (here, B4) of the dataset.
- 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.
- 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
- How to Divide a Group of Cells by a Number in Excel (3 Methods)
- Unlock Cells without Password in Excel (4 Methods)
- How to Group Cells with Same Value in Excel (3 Methods)
- Move Selected Cells in Excel with Keyboard (4 Methods)
- How to Make a Group of Cells Negative in Excel (5 Methods)
- How to Lock Certain Cells in Excel (4 Methods)
