Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Use a custom conditional-format rule to color a full row when its status cell says Done. The fictional tracker shows why the dollar sign must lock the status column.
Use the fictional practice data and answer check. Work in a copy.
Before you start
The companion video uses clearly labelled illustrative diagrams based on the downloadable example. Menu wording may vary with app version or account. Keep the source file available for comparison.
1. Use a three-task practice tracker
Paste the fictional CSV into a new Google Sheet. Draft brief is Done; Review copy is In progress; Publish page is Queued. The task, owner, status, and due date occupy columns A through D. Keep the header in row 1. You can create a dropdown in C2:C4 if you wish, but the color rule also works with exact text values.
Check: A:D contain four fields; C2 = Done; C3 and C4 are unfinished.
2. Select the whole display range
Highlight A2:D4, then open Format > Conditional formatting. Confirm the Apply to range box contains A2:D4. This range controls which cells receive the color, whereas the formula controls which rows qualify. If you select only C2:C4, only the status cells will be styled even with a correct formula. Choose a restrained fill that keeps black text readable.
Check: Select A2:D4; Open Conditional formatting; Check Apply to range.
3. Use a column-locked custom formula
For Format cells if, choose Custom formula is and enter =$C2="Done". The dollar sign fixes the status column while the row number changes as Sheets evaluates each row. Apply the fill color and finish the rule. Google's documentation uses the same pattern to format an entire row based on a value in one column.
Check: Custom formula is; =$C2="Done"; Apply fill to A2:D4.
4. Check the before and after
Only the Draft brief row should be colored initially. Change C3 from In progress to Done and watch the Review copy row gain the same fill across all four cells. Change it back to In progress and the fill should disappear. This reversible test confirms that the rule follows the status value rather than a one-time manual paint action.
Check: Initially row 2 colored; Set C3 to Done; Row 3 colors, then resets.
5. Understand the dollar-sign error
If you enter =C2="Done" without locking column C, the reference can shift as Sheets applies the rule across columns A through D. Some cells may test D or E instead of the intended status. If you lock the row as $C$2, every task may follow the first status. The correct pattern is $C2: one fixed column, changing rows.
Check: $C2 fixes C only; C2 may shift sideways; $C$2 fixes wrong row.
Expected result
Apply the rule to A2:D4 with custom formula =$C2="Done". Only the Draft brief row is colored. Change C3 to Done and the Review copy row should color automatically.
Common mistakes
- Apply the rule to the complete row range, not only the status cells.
- Lock column C with $ but leave the row number relative, so each row tests its own status.
- The exact text Done must match the dropdown value, including spaces and case behavior.
Official references and evidence
References checked 15 October 2026. The sample and answer check are original fictional material. Video panels are illustrative, not app screen recordings. The documented workflow is sourced to the product owner; this lesson has not been reproduced in a fresh signed-in session for this batch.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.