Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight a row based on a drop-down value in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=B2=$F$1✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Category | Value |
| Fruit | Apple |
| Vegetable | Carrot |
| Fruit | Banana |
| Vegetable | Broccoli |
=B2=$F$1→FALSE
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Create a drop-down list in cell F1 with the values you want to use for highlighting.
- 2Select the range of cells you want to apply the conditional formatting to (e.g., A2:B5).
- 3Go to the Home tab, click Conditional Formatting, and choose New Rule.
- 4Select 'Use a formula to determine which cells to format'.
- 5Enter the formula `=B2=$F$1` and set your desired formatting style.
Tips
- Ensure the cell reference in the formula matches the column where your drop-down list is located.
- Adjust the range in step 2 to cover all rows and columns you wish to highlight.
- In Excel, make sure your drop-down list is created using Data Validation.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to highlight multiple rows based on different drop-down values?
Create separate rules for each value by repeating the process with different formulas.
Can this be done without a drop-down list?
Yes, but the trigger would be manual input or another condition rather than a drop-down selection.
How do I remove the conditional formatting once it's applied?
Select the formatted cells, go to Home > Conditional Formatting > Clear Rules.
More on Conditional formatting
See all →How-to
How to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
How-toHow to highlight cells greater than a value
Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight the lowest value in a range
Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.