Sometimes you may need to select an entire row based on any specific data in a cell of that row. You can do the task in multiple ways. In this article, I’ll show you 4 easy and simple ways to select row in Excel if a cell contains specific data.
Download Practice Workbook
4 Ways to Select Row in Excel If Cell Contains Specific Data
For demonstration purposes, I have got a dataset of the Owners of different books. Now, I’ll show you how to select rows if a cell contains specific data by selecting the entire rows for a specific Owner.
1. Utilize Filter Feature to Select Row Based on Specific Data in Excel
The easiest way to select rows based on the specific data of a cell is by using the Filter feature.
- Firstly, select the entire dataset and go to Home > Editing > Sort & Filter > Filter.
- After that, we will see small downward arrows appear beside the row headers.
- Then, click on the arrow beside the Owner.
- Simultaneously, it will open a dropdown menu.
- Moreover, select Harold from this dropdown menu and click on OK.
- Finally, you will see only the rows which contain Harold.
- Lastly, select those rows manually pressing Ctrl first.
Read More: How to Highlight Active Row in Excel (3 Methods)
2. Select Row If Cell Contains Specific Text Using Conditional Formatting
You can also select rows based on specific data in a cell by using Conditional Formatting.
- Firstly, select your entire dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
- Consecutively, we will see a window named Text That Contains opened.
- Then, type the data based on which row will be selected in the box Format cells that contain the text.
- For this dataset, I’ve typed Ana.
- Also, in the with section, select your preferred formatting styles and press OK. I’ve selected Light Red Fill with Dark Red Text.
- Finally, you will see the cells which contain the data highlighted.
- In addition, you can select the rows by pressing CTRL and clicking on the row numbers of the highlighted cells.
Read More: Highlight Row If Cell Contains Any Text
Similar Readings
- How to Unhide Top Rows in Excel (7 Methods)
- Data Clean-up Techniques in Excel: Randomizing the Rows
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- Hidden Rows in Excel: How to Unhide or Delete Them?
- How to Freeze Rows in Excel (6 Easy Methods)
3. Applying Excel Find & Select Features
Find & Select features can also be used to select the rows if a cell contains specific data.
- Firstly, select your entire dataset and go to Home > Editing > Find & Select > Find.
- Then, you will see a window named Find and Replace opened.
- Further, type the data based on which rows will be selected in the Find what box.
- For this dataset, I’ve typed Harold.
- After that, click on Find All.
- After that, you will see the cells which have that specific data shown at the bottom of the Find and Replace window.
- Later on, select the cells and close the Find and Replace window.
- As a result, you will see the cells which contain the Harold selected.
- Also, you can do it for the owner Ana obviously.
- Finally, select the entire rows by pressing CTRL and clicking on the row numbers of the cells.
Related Content: Excel Alternating Row Color with Conditional Formatting [Video]
4. Incorporating VBA to Select Row
Using Microsoft Visual Basic for Applications (VBA), you can easily select the rows if a cell in those rows contains specific data.
- Firstly, press ALT+F11 to open the VBA window
- After that, from the left panel of this window, right-click on the sheet name and go to Insert > Module.
- Simultaneously, It will open the Module (Code) window.
- Afterward, insert the following code in this Module (Code) window
Sub select_rows_with_given_data()
Dim Rng As Range
Dim myCell As Object
Dim myUnion As Range
Set Rng = Selection
searchdata = InputBox("Please Enter the Search data")
For Each myCell In Rng
If InStr(myCell.Text, searchdata) Then
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCell.EntireRow)
Else
Set myUnion = myCell.EntireRow
End If
End If
Next
If myUnion Is Nothing Then
MsgBox "The data was not found in the selection"
Else
myUnion.Select
End If
End Sub
- Afterward, close the VBA window, select your dataset and go to View > Macro to run the Macro.
- Instantly, a window named Macro will be opened.
- Then, select select_rows_with_given_data from the Macro name box and click on Run.
- Instantly, it will open a custom box.
- Later on, In the Please Enter the Search data box type the specific data and click on OK.
- After running the macro, the code will open a custom box where you can insert the data. If the data is found in your selected cell ranges, the macro will select the entire row. If the data is not found, it will give an error message.
- Finally, you will see all the rows which contain the specific data in one of its cells are selected.
Related Content: VBA to Hide Rows in Excel (14 Methods)
Conclusion
You can select a row in Excel if a cell contains specific data by following any of the above-described methods. Please leave a comment if you have any confusion. Additionally, you can follow our website ExcelDemy to explore more.
Related Articles
- How to Delete Blank Rows in Excel (6 Ways)
- [Fix]: Unable to Unhide Rows in Excel (4 Solutions)
- How to Hide Rows in Excel (6 Effective Methods)
- How to Group Rows in Excel (5 Easy Ways)
- Shortcut to Unhide Rows in Excel (3 Different Methods)
- How to Select Large Data in Excel Without Dragging (5 Easy Ways)