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 one cell value in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=$C2="Complete"✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Task | Owner | Status | Due |
| Design | Alice | Complete | Jan 5 |
| Dev | Bob | In Progress | Jan 12 |
| Testing | Carlos | Complete | Jan 18 |
| Deploy | Diana | Pending | Jan 25 |
=$C2="Complete"→TRUE
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
- 1Select the full row range you want to format, e.g. A2:E20.
- 2Open Conditional Formatting and choose 'Use a formula'.
- 3Enter =$C2="Complete" (dollar sign before the column, not the row). Choose a fill color and save. The entire row will highlight when column C says 'Complete'.
Tips
- The mixed reference =$C2 is critical — locking the column ($C) but not the row means the rule correctly evaluates each row against its own C value.
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
Can I highlight a row based on a number condition instead of text?
Yes. Use =$D2>100 to highlight any row where the value in column D exceeds 100. Any comparison operator works in the formula.
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.