Automatically Color an Excel Column When a Cell Equals Zero
Question details
Automatically color cells in each Excel column when that column's completion value in row 78 equals zero.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has completion values in row 78, and each column should be formatted according to its own value.
- Observed behavior
- The selected cells in a column should change color automatically when the value in row 78 of that column is zero.
Identify the range to color and confirm that row 78 contains the values to check. Note the first column of the selected range; the formula must start with that column.
Create a Conditional Formatting Rule for Row 78
Use a formula-based rule to color each selected column according to its own row 78 value.
The dollar sign in A$78 locks the row at 78 while allowing the column reference to change across the selected range.
Select the cells you want to color, such as A1:C79. To format a single column, select only the desired cells in that column.
Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
Enter =A$78=0 for a range starting in column A. If your selection starts in another column, replace A with that column's letter.
Select Format, choose a color on the Fill tab, and click OK. Click OK again to apply the rule.

Color Columns Based on Completion Values in WPS Spreadsheets
Use a formula-based conditional formatting rule in WPS Spreadsheets to color each selected column when its row 78 value is zero.
- 1. Open the workbook: Open your spreadsheet in WPS Spreadsheets and find the completion values in row 78.
- 2. Select the cells: Select the range to format, such as A1:C79.
- 3. Add a formula rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula. Enter =A$78=0, replacing A if the selected range starts in another column.
- 4. Apply a fill: Choose a fill color in the rule's formatting options and confirm the rule to apply it to the selected cells.

Frequently Asked Questions
Why does the formula use A$78 instead of $A$78?
A$78 locks row 78 but lets the column change across the selected range. $A$78 would make every selected column check cell A78.
How do I prevent blank cells from triggering the color?
In the formula-based rule, use =AND(A$78<>"",A$78=0). Replace A with the first column of the selected range.
Will the color update when the completion value changes?
Yes. Conditional formatting reevaluates the rule as worksheet values change, so the fill appears when the condition is met and disappears when it is no longer met.
How can I change the range or fill color later?
In Excel, go to Home > Conditional Formatting > Manage Rules. Select the rule to edit its format or change its Applies to range.




