Maruf Islam
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 values within any column that have value.
In this article, we select cells with certain values in Excel using Excel features as well as VBA macro.
How to Select Cells with Certain Value in Excel (5 Easy Ways)
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.
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.
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.
➤ Click on Close in the Find and Replace window. You see 2 cells containing a certain value (i.e., 30) in the dataset.
You can assign any value as certain to find them in the dataset.
Read More: How to Select Non-Adjacent Cells in Excel
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).
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.
➤ Afterward, you click on OK, Excel Conditional Formatting cells that contain values between 50 and 100 as shown in the below screenshot.
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).
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.
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 Cells in Excel Using Keyboard
Method 3: Using Custom Filter Feature to Select Cell with Specific Value
In the previous method, we used 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.
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).
Step 3: The Custom AutoFilter window appears. In the Custom AutoFilter window, Choose the 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.
➤ Excel filter values between 50 and 100 exist in the Quantity column. You can see Excel hides rows containing values not in between 50 and 100.
Read More: Select All Cells with Data in Excel
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 the Order Date, Quantity, and Unit Price column as Value. Other columns such as Product as Text and Total Price as Formula column.
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).
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.
Step 3: After clicking OK, you see Excel selects all the entries, and values within the dataset as shown in the below picture.
The dataset containing occasional value entries in cells is appropriate for this method. Excel Go to Special and select all entries in the dataset.
Read More:
- How to Select Multiple Cells in Excel Without Mouse
- Multiple Excel Cells Are Selected with One Click
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.
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).
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
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.
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
Download Excel Workbook
Conclusion
In this article, we use multiple Excel features as well as VBA macro to select cells with certain values. However, these methods offer different outcomes in the 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
- How to Select Random Cells in Excel
- How to Select Blank Cells in Excel and Delete
- How to Select Cells in Excel Without Dragging
- How to Select Large Data in Excel Without Dragging
- How to Select Highlighted Cells in Excel
- How to Select Only Filtered Cells in Excel Formula
- How to Go to the End of Excel Sheet
- Excel Ctrl End Goes Too Far
- Excel Select Column to End of Data
- Excel Go to Last Non Empty Cell in Column
- Selected Cells Not Highlighted in Excel
