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 value within any column that have value.
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.
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 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).
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 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.
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 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 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.
Read More: How to Select a Range of Cells in Excel (9 Methods)
Similar Readings
- How to Drag Cells in Excel Using Keyboard (5 Smooth Ways)
- Divide a Group of Cells by a Number in Excel (3 Methods)
- How to Unlock Cells without Password in Excel (4 Methods)
- Make a Group of Cells Negative in Excel (5 Methods)
- How to Display the Cell Formulas in Excel (6 Methods)
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.
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, 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 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.
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 (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
- How to Move All Cells Down in Excel (5 Methods)
- How to Click One Cell and Highlight Another in Excel (2 Methods)
- [Fix]: Arrow Keys Not Moving Cells in Excel (2 Methods)
- How to Lock Cells in Excel When Scrolling (2 Easy Ways)
- How to Move a Group of Cells in Excel (4 Easy Ways)
- How to Shift Cells Down in Excel without Changing Formula (4 Methods)
