Formatting Issues

Automatically Color an Excel Column When a Cell Equals Zero

Muhammad TalhaMuhammad Talha Nov 14, 2026 868 views

Question details

Automatically color cells in each Excel column when that column's completion value in row 78 equals zero.

Automatically Color an Excel Column When a Cell 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the 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.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.

3
Enter the formula

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.

4
Choose the color

Select Format, choose a color on the Fill tab, and click OK. Click OK again to apply the rule.

Create a Conditional Formatting Rule for Row 78
Exclude blank completion cells: A blank cell can be treated as zero in this comparison. To color only columns with a nonblank zero in row 78, use =AND(A$78<>"",A$78=0), adjusting A to the first selected column.
Format columns automatically

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. 1. Open the workbook: Open your spreadsheet in WPS Spreadsheets and find the completion values in row 78.
  2. 2. Select the cells: Select the range to format, such as A1:C79.
  3. 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. 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.
Automatically update column colors when completion values changeUse a mixed cell reference to check row 78 in each columnOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.