Conditional Formatting

Conditional formatting applies a visual style to cells that meet a rule. Microsoft describes it as determining “the format of cells based on their values”, and the important half of that is what it does not do: the appearance changes and the stored value does not. It can therefore mark possible problems for inspection before anyone decides whether to change the data.

The rule types cover most needs directly. Highlight-cells rules match text, numbers, or dates; top and bottom rules surface extremes and above-average values; data bars and color scales show distribution inside the column; icon sets group values into bands. For cleaning, the useful cases are usually the plainest — highlight blanks to see missing values, highlight anything outside an expected range, highlight one category to check its spelling is consistent.

Formula-based rules cover everything else, with one precise requirement: “the formula must return a logical value of TRUE (1) or FALSE (0)”. A rule flagging every identifier whose length is not six is a single expression, and it converts an invisible defect into a color without touching the data. Rules evaluate live, so new rows entering the range are checked as they arrive.

Two limits keep it honest. Color is a signal, not a stored data value — ordinary cell formulas cannot read it and an export such as CSV does not carry it (some scripting interfaces can read applied formats, but that is not a reliable data field), so a finding that matters needs a real column or a note. And a sheet where everything is colored communicates nothing; the value comes from restraint, from rules whose meaning is written down somewhere, and from a palette that still works for readers who cannot distinguish red from green.

References

• Use conditional formatting to highlight information
• Use conditional formatting rules in Google Sheets