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

Sometimes, we have to find and select cells with a certain value in Excel. In general, Excel carries numerous values as entries therefore at times certain values need to be found and selected. Let’s say, we have a dataset containing Product, sold Quantity and Prices. We want to select cells with certain value within any column that have value.

Dataset-Select Cells With Certain Value in Excel

 In this article, we select cells with certain value in Excel using Excel features as well as VBA macro.


Download Excel Workbook


5 Easy Ways to Select Cells with Certain Value in Excel

Method 1: Using Excel Find Feature to Select Cells with Certain Value

Excel offers the Find feature that finds any types of entries within a worksheet. The Find feature offers Formulas, Values, Texts, and Comments options to be found and selected in the Look in a dialog box. As we specifically want to select cells with certain values, we need to choose Values as Look in option.

Step 1: Go to the Home tab > Choose Find & Select (from the Editing section) > Select Find.

find feature-Select Cells With Certain Value in Excel

Step 2: The Find and Replace window appears. You can see you are in the Find section. Leave other options as they are except select Values as the Look in option.

Type any value (i.e., 30) in the Find what dialog box.

Click on Find All.

Find and replace window

Step 3: In a moment, all the cells containing a certain value (i.e., 30) get stacked below the Find and Replace window.

Press CTRL+A to select all the found cells at once. In the dataset, we see 2 cells containing the exact value 30 get selected.

desired cells

➤ Click on Close in the Find and Replace window. You see 2 cells containing a certain value (i.e., 30) in the dataset.

Final result

You can assign any value as certain to find them in the dataset.

Read More: How to Group Cells with Same Value in Excel (3 Methods)


Method 2: Selecting Specific Cells Using Conditional Formatting

Conditional Formatting is an effective tool to select cells with certain values. Conditional Formatting offers multiple options to select or highlight cells based on their types (i.e., text or value). Since we want specific cells with specific values to be selected, we can use a value between or equal options to select cells.

Step 1: Select a range or column (i.e., Quantity column) then, go to the Home tab > Conditional Formatting (from the Style section) > Select Highlight Cells Rules (from the option) > Select Between (from the option).

Conditional formatting -Select Cells With Certain Value in Excel

Step 2: The Between window appears. In the window, type 2 values (i.e., 50 and 100), which you want to select values in between.

Click on OK.

Between window

➤ Afterward, you click on OK, Excel Conditional Formatting cells that contain values between 50 and 100 as shown in the below screenshot.

Color formatting

You can change the cell highlight color if you wish to. Also, you can select cells containing values between any random numbers.

🔁 Select Cells Containing Exact Value

Conditional Formatting has multiple options, finding and highlighting equal value within a column or dataset is one of them.

Step 1: Go to the Home tab > Select Conditional Formatting (from Style section) > Choose Highlight Cells Rules (from options) > Select Equal To (from options).

Conditional formatting-Select Cells With Certain Value in Excel

Step 2: The Equal To window opens up. Enter any value in the (i.e., 30) in the Format cells that are Equal To dialog box.

Click on OK.

Equal to window

You see a similar selection of cells as you experienced earlier that contain values equal to 30. Also, you can assign any value to select cells within a dataset and can color format it using conditional formatting.

Read More: How to Select a Range of Cells in Excel Formula (4 Methods)


Method 3: Using Custom Filter Feature to Select Cell with Specific Value

In the previous method, we use Conditional Formatting to select cells that have values between two numbers. We can achieve a different kind of outcome representation using the Custom Filter feature.

Step 1: In the Home tab > Select Sort & Filter (from the Editing section) > Select Filter.

custom filter-Select Cells With Certain Value in Excel

Step 2: The Filter icon appears in the headers as depicted in the following image. Click on the Quantity header Filter icon > Select Number Filters > Select Custom Filter (from the options).

Filter

Step 3: The Custom AutoFilter window appears. In the Custom AutoFilter window, Choose Quantity column option Is greater than or equal to and Is less than or equal to. Type 50 as greater than or equal to and 100 as less than or equal to value.

Click on OK.

Custom AutoFilter window

➤ Excel filters values between 50 and 100 exist in the Quantity column. You can see Excel hides rows containing values not in between 50 and 100.

Final result

Read More: How to Select a Range of Cells in Excel (9 Methods)


Similar Readings


Method 4: Select Certain Value Cells Using Go to Special Feature in Excel

What if we want to select all values existing in a dataset? Excel’s Go to Special feature does the job.

In this case, we want to select all the values that exist within the dataset. From the below screenshot, we can say we have Order Date, Quantity and Unit Price column as Value. Other columns such as Product as Text and Total Price as Formula column.

Go to Special-Select Cells With Certain Value in Excel

Step 1: Select the entire dataset, then, Go to the Home tab > Select Find & Select (from the Editing section) > Choose Go to Special (from the options).

Go to Special feature

Step 2: The Go to Special dialog box appears. Select Constants under the Select option. Make sure to unselect options (i.e., Text, Logicals, and Errors) except Numbers.

Click on OK.

Go to Special window

Step 3: After clicking OK, you see Excel selects all the entries, values within the dataset as shown in the below picture.

Final result

The dataset containing occasional value entries in cells is appropriate for this method. Excel Go to Special selects all entries in the dataset.

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


Method 5: Using VBA Macro to Select Certain Value Cells in Excel

VBA macros are also capable of selecting cells with a certain value in them. We can assign a specific worksheet from which we can select certain value cells. Let’s say, we have a dataset (as shown below) named VBA that holds entries consisting of values, texts, and formulas. We want to select cells containing a specific value.

vba-Select Cells With Certain Value in Excel

Step 1: Hit ALT+F11 altogether, Microsoft Visual Basic window appears. In the window, Go to Insert (from the Toolbar) > Select Module (from the option).

Module insertion

Step 2: In the Module, Paste the following macro code.

Sub Selecting_specific_value()
Dim wrkSheet As Worksheet
Dim CellSelection As Range
Dim wrkcell As Object
Set wrkSheet = Worksheets("VBA")
For Each wrkcell In wrkSheet.UsedRange.Cells
If wrkcell.Value = 30 Then
If CellSelection Is Nothing Then
Set CellSelection = Range(wrkcell.Address)
Else
Set CellSelection = Union(CellSelection, Range(wrkcell.Address))
End If
End If
Next
CellSelection.Select
End Sub

macro

The macro takes all the cells as Object and the VBA worksheet as a working worksheet. A For loop searches all the cells for assigned value 30. Then, the VBA Select statement selects the specific cells within the specific worksheet.

Step 3: Use the F5 key to run the macro. Then, return to the worksheet, you see cells containing specific values (i.e., 30) get selected.

final result

You can use any value in the macro to select cells within the dataset. Also, you can assign any worksheet to select cells from.

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


Conclusion

In this article, we use multiple Excel features as well as VBA macro to select cells with certain value. However, these methods offer different outcomes in case of representation. Excel Custom Filter displays only cells that match the values otherwise hides them. The Go to Special feature selects all cells that contain numeric values. Hope you find these above-described methods workable with your dataset. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo