Conditional Formatting? Basics
You scan a spreadsheet hunting for overdue dates, low stock, or top performers — row by row, eyes glazing. Conditional formatting colors cells automatically based on their values: red for overdue, green for done, gradients for hot spots. It turns a data wall into a dashboard. Here is the five-minute setup in Google Sheets.
TL;DR / Quick Fix
Quick fix: select cells in Google Sheets, go to Format → Conditional formatting, and add a rule — e.g., 'less than 10' turns low stock red. Rules apply colors, scales, or data bars automatically as values change. Start with one rule on one column; layer more once it clicks.
Follow these steps
- Select and open the rules. Highlight the cells to format → Format → Conditional formatting. The rules panel opens on the right. Rules attach to ranges — select precisely the column or block you mean.
- Build your first rule. Under 'Format cells if,' pick a condition like 'Less than' and enter 10; set the style to red fill. Click Done — matching cells color instantly. Overdue dates, low inventory, missed targets: pick your pain point.
- Use color scales for gradients. Instead of one threshold, apply a color scale (green to yellow to red) across a range — instant heatmap. Performance scores and temperatures read at a glance. Scales beat binary rules for 'how bad' questions.
- Try custom formulas. The 'Custom formula is' option unlocks real power: =A1<TODAY() highlights overdue dates; =$B2="Done" colors whole rows. Formulas reference the top-left cell of your range. One formula, whole-column intelligence.
- Manage rule order. Multiple rules on one range evaluate top-down; drag to reorder in the panel. Put specific rules above general ones. Conflicting rules produce confusing colors — order is the fix.
- Copy formatting to other ranges. Paint format (the roller icon) copies conditional rules to new ranges, adjusting references. Or duplicate the rule and change its range. Build once, apply everywhere.
What NOT to do
- Do not rainbow the sheet. Five colors with no legend is noise — two or three meaningful colors beat a carnival.
- Do not format entire columns (A:A). Full-column rules slow large sheets — bound rules to your actual data range.
- Do not use color as the only signal. Colorblind colleagues miss red/green — pair color with icons or text where it matters.
- Do not stack contradictory rules. Overlapping rules on one range fight — keep the rule list short and ordered.
Frequently asked questions
Can I format a whole row based on one cell?
Yes — select the full row range, use 'Custom formula is' like =$D1="Overdue" (lock the column with $). The row lights up based on that column's value.
Why isn't my rule working?
Usual causes: numbers stored as text, wrong range selected, or a higher-priority rule overriding it. Check the rule list order and the cell's actual data type.
Does it work in Excel too?
Yes — Home → Conditional Formatting, same concepts. Rules transfer reasonably when converting between Sheets and Excel.