Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Select Row in Excel If Cell Contains Specific Data (4 Ways)

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.

excel select row if cell contains

 


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.

dataset to select row

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.

selecting filter feature

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

using filter option

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

result of excel selected row if cell contains specific data

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.

Select Row If Cell Contains Specific Text Using Conditional Formatting

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

giving input to text that contains box

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

result of excel select row if cell contains specific text

Read More: Highlight Row If Cell Contains Any Text


Similar Readings


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.

selecting find and select feature

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

using find and replace to select text

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

selected text in find and replace box

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

selected row having specific text using find and replace

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.

inserting new module in vba window

  • 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

VBA CODE

  • Afterward, close the VBA window, select your dataset and go to View > Macro to run the Macro.

selecting data and opening 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.

running macro

  • 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.inserting specific text in custom box
  • 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.

result of excel select row if cell contains using vba

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

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo