Highlighting Rows that Meet Criteria in Excel’s Search Box
Highlighting Rows that Meet Criteria in Excel‘s Search Box. This article discusses how to highlight rows in Excel that meet specific criteria in the search box. Previously, we learned how to highlight entire rows if any cell in the row contains specific text. Now, we will focus on highlighting rows if a cell in a particular column contains a specific value. We can achieve this using conditional formatting.
Highlighting Rows that Meet Criteria in Excel’s Search Box
To highlight rows based on a criteria in Excel, we can use the following generic formula:
= (cell with relative row = criteria)
Here, the “cell with relative row” refers to a cell reference where the column is absolute and the row is relative (e.g., $A1). You can learn more about referencing in Excel here. The “criteria” represents the value or condition that you want to match and highlight the rows.
For example, let’s consider a dataset of fruits. We want to highlight entire rows if the fruit rate is greater than 100. To do this, follow these steps:
- Select the first row of the table (e.g., A2:C2).
- Go to conditional formatting and choose the “New Rule” option (you can also use the shortcut ALT>H>L>N).
- Select “Use a formula to determine which cells to format.”
- Enter the following formula: =$C2>100
Note that it is necessary to select the entire row before applying conditional formatting. 5. Click on the “Format” button and choose the fill color (e.g., yellow). Click “OK.”
Now, copy this range and paste the special format to the entire table.
As a result, each row with a rate greater than 100 will be highlighted. In the given example, we hardcoded the criteria, but you can also use cell references.
How does it work? It’s simple. We check if the value in $C2 is greater than 100. If it is, Excel applies the formatting to highlight the entire row. The column reference ($C) is absolute to maintain consistency, while the row reference (2) is relative to adjust accordingly when copying the conditional formatting.
Understanding the concepts of absolute and relative referencing in Excel is key to applying these formatting techniques easily.
We hope this article about highlighting rows that meet criteria in Excel’s search box has been explanatory.
