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.

Here, I’ve 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 where the Owner is Harold.

data


Download Practice Workbook


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

1. Filter to Select Row Based on Specific Data in Excel

The easiest way to select rows based on the specific data of a cell is using the Filter feature. First,

➤ Select the entire dataset and go to Home > Editing > Sort & Filter > Filter.

filter

After that, small downward arrows will appear beside the row headers.

➤ Click on the arrow beside Owner.

It will open a dropdown menu.

filter

➤ Select Harold from this dropdown menu and click on OK.

filter

As a result, you will see only the rows which contain Harold are selected in the list.

excel select row if cell contains

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.

First,

➤ Select your entire dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.

conditional formatting

A window named Text That Contains will be opened. Now,

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

➤ In the with box, select your preferred formatting styles and press OK. I’ve selected Light Red Fill with Dark Red Text.

excel select row if cell contains

As a result, you will see, the cells which contain the data will be highlighted.

highlight

Now, you can select the rows by pressing CTRL and clicking on the row numbers of the highlighted cells.

excel select row if cell contains

Read More: Highlight Row If Cell Contains Any Text


Similar Readings:


3. Excel Find & Select Features

Find & Select features can also be used to select the rows if a cell contains specific data.

First,

➤ Select your entire dataset and go to Home > Editing > Find & Select > Find.

find

Now a window named Find and Replace will be opened.

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

find and replace box

Now, the cells which have that specific data will be shown at the bottom of the Find and Replace window.

➤ Select the cells and close the Find and Replace window.

select cells

As a result, you will see, the cells which contain the data will be selected.

➤ Select the entire rows by pressing CTRL and clicking on the row numbers of the cells.

excel select row if cell contains

Related Content: Excel Alternating Row Color with Conditional Formatting [Video]


4. Using VBA to Select Row

Using Microsoft Visual Basic Applications (VBA), you can easily select the rows if a cell in those rows contains specific data.

➤ First, press ALT+F11 to open the VBA window

➤ After that, from the right panel of this window, right click on the sheet name and go to Insert > Module.

VBA

It will open the Module(Code) window.

➤ 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

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 entire row will be selected. If the data is not found, an error message will be shown.

CODE

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

excel select row if cell contains

As a result, a window named Macro will be opened.

➤ Select select_rows_with_given_data from the Macro name box and click on Run.

MACRO WINDOW

It will open a custom box.

➤ In the Please Enter the Search data box type the specific data and click on OK.

excel select row if cell contains As a result, you will see, all the rows which contain the specific data in one of its cells are selected.

excel select row if cell contains

Related Content: VBA to Hide Rows in Excel (14 Methods)


Conclusion

You can select 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.


Related Articles

Prantick

Prantick

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