Established onExcel 365 and 2016 and later: filter and sort by colourGoogle Sheets, web: same, and no colour function eitherBoth: delimited exports carry no formatting at all
Colouring a cell records nothing. The cell's value is unchanged, and the meaning lives in the appearance, which is the one part of a spreadsheet that no formula can read.
What a colour cannot do
It cannot be counted. There is no function in either product that returns the fill colour of a cell, so a total of the red ones has to be produced by hand or by scripting.
It cannot be exported. Delimited files carry no formatting at all, so the meaning is lost the moment the data leaves for another system, and lost silently, because the export succeeds.
It cannot be read by everybody. Red and green are the usual pair and are the pair that a substantial minority of readers cannot distinguish; figures around one man in twelve are commonly reported for the red-green deficiencies. A convention that a portion of your colleagues cannot see is a convention that will be misread.
What a colour can do
Both products can filter and sort by cell colour, which makes the practice half-workable and is the reason it survives. Half-workable is the dangerous condition: it works well enough that nobody replaces it and badly enough that it eventually fails.
The correct direction
Conditional formatting reverses the relationship. The status is a value in a column, and the colour is derived from it by a rule. The data is the truth and the appearance is a view of it.
Everything then works: the status can be counted, filtered, exported, pivoted and read by anybody, and the colour still appears for the people who find it useful. There is no trade-off here, only an ordering.
The same mistake in number formatting
A cell displaying 3 may contain 3.4. A cell displaying a date contains a number. A cell formatted as a percentage displays a value multiplied by a hundred and stores the original.
Formatting changes what is drawn and never what is stored, which is the mechanism behind the reconciliation of a penny described in the entry on floating point, and behind a great deal of confusion when a column is copied somewhere the formatting does not follow.
The diagnostic worth knowing
By default numbers align right and text aligns left. A column of dates where some entries sit left and some sit right is a column half parsed and half text, which the entry on dates in the previous part is about.
That single visual cue answers the commonest question in this whole subject, is this a number or text, without touching anything.
The rule, stated once
If it means something, it goes in a column. Colour, bold, italics, strikethrough and cell borders are all appearance, and any of them used to carry information is information that cannot be computed with.
Strikethrough for cancelled rows is the most common offender after colour, and it has the same three failures.
Where appearance is the right answer
Where it is genuinely presentational: making a report readable, distinguishing a header, drawing the eye to a total. Nothing depends on those being machine-readable and formatting them is exactly correct.
The test is whether anybody would ever want to count them.
Converting a coloured sheet
Filter by colour, which both products allow, and type the status into a new column for each group. That is the whole migration, it takes minutes, and it is worth doing the moment somebody asks how many of the red ones there are.
Then add the conditional formatting rule so the appearance survives and becomes derived.
What we cannot verify
The absence of a colour-reading function, the filter and sort by colour features, and the behaviour of number formatting are documented by both vendors and demonstrable directly. The figure for red-green colour deficiency is a commonly reported clinical estimate that varies by population and by how it is defined; it is offered as an order of magnitude rather than a precise number.
The one thing colour is genuinely good at
Marking the three kinds of cell described earlier in this part. Typed inputs, calculations and links elsewhere are not data about the business; they are facts about the file, and nobody will ever want to count them.
So the modelling convention survives this entry's argument intact, which is worth saying, because the rule against colour-as-data is otherwise easy to over-apply.
Checking whether anybody relies on it
Before removing a colour convention from an inherited file, filter by each colour and count. A colour applied to four cells in five years is decoration; a colour applied to three hundred is a column somebody has been maintaining by hand, and removing it without replacing it destroys data.
In short
- Colouring a cell records nothing, and the meaning lives where no formula can read it.
- It cannot be counted, cannot be exported, and cannot be read by everybody.
- Filtering by colour makes the practice half-workable, which is why it survives.
- Conditional formatting reverses it: the status is a value and the colour is derived.
- Formatting changes what is drawn and never what is stored, including for dates and percentages.
- Numbers align right and text aligns left, which answers the commonest question at a glance.