Google Sheets web app (rolling service) · Data preparation and inputs · Beginner
Highlight Values with Conditional Formatting in Google Sheets
Highlight records with conditional formatting in Google Sheets with a reproducible practice example and outcome checks.
Documentation-reviewed candidate; native application verification not performed. · Editorial standards
Quick answer
Use Format > Conditional formatting to highlight cells that meet a rule. For an entire record, apply a custom formula to the record range, fixing the decision column but allowing the row to change.
Prepare the review queue
Use an editable native spreadsheet in the desktop web app. Android supports editing rules through its formatting controls; iPhone/iPad can view rules but cannot edit them. This exercise needs no premium Workspace feature.
In a private practice file, enter this table at A1. Enter the scores as numbers, not text. Keep this starting data on a separate untouched tab before experimenting.
| Record | Score | Review note |
|---|---|---|
| Case A | 0.6 | Needs review |
| Case B | 0.8 | Boundary case |
| Case C | 0.9 | Meets target |
The decision is strictly below 0.8. Equality must not trigger the highlight. The note supplies meaning even when someone cannot distinguish the fill color.
Highlight the score, then the record
- Select B2:B4 and open Format > Conditional formatting.
- Under Format cells if, select Less than and enter
0.8. Choose a pale fill with readable text and click Done. - Check that only B2 is highlighted. B3 is the important boundary test: 0.8 is not less than 0.8.
- To finish with whole-row highlighting rather than overlapping rules, remove that first practice rule using its Remove control.
- Select A2:C4. Create a rule with Custom formula is and enter
=$B2<0.8. - Choose the same readable fill and click Done. Confirm the application range is A2:C4, not just the active cell.
The formula is written for row 2 because that is the first row of the application range. $B keeps every cell in a record looking at its score. The unfixed 2 lets the test move to rows 3 and 4. This is a formatting rule, not a formula to enter in A2.
Check an edit and keep a clean result
Change B4 to 0.7. Rows 2 and 4 should now be highlighted; row 3 should remain plain. Then restore B4 to 0.9 for the final example, leaving only row 2 highlighted. The underlying scores remain 0.6, 0.8 and 0.9; conditional formatting does not change them.
Leave the cell to commit the edit, allow online saving to finish, and reopen the file. Verify both the data and the rule’s A2:C4 range. Reopening is a reader verification step, not a test performed for this article.
Troubleshoot without damaging the final copy
- Every row follows the first score: inspect whether the formula accidentally says
$B$2. - The wrong row lights up: match the formula’s row number to the first selected row. A2:C4 requires row 2, not row 1.
- The color differs from the chosen style: inspect other rules. Sheets evaluates rules in order; the first true rule determines the format. Pasted formatting can bring extra rules with it.
- A new record is not highlighted: inspect the application range before changing the formula. Extend the rule only to intended records.
For a recovery drill, duplicate the finished tab, remove its rule, and confirm that its values survive. Keep the intact final tab unchanged. To restrict editing rather than highlight it, use sheet and cell protection; color is not access control.
Official references
- Use conditional formatting rules in Google Sheets - Computer - Google Docs Editors Help
- Use conditional formatting rules in Google Sheets - Android phone & tablet - Google Docs Editors Help
- Use conditional formatting rules in Google Sheets - iPhone & iPad - Google Docs Editors Help
Documentation basis: first-party extracted snapshots retrieved 20 October 2026. The fixture expectations were checked independently; no native Google Sheets session, account action, screenshot or export was executed for this article.