3.5 Conditional formatting
Checked against Microsoft's Excel for Windows keyboard references, August 2026
What you'll be able to do
Make the number that matters find the reader's eye on its own. Overdue rows marked, extremes brought out, sizes you can compare by eye — with formatting that updates itself as the data changes.
Before you start
Open the pivot sales workbook, or any sheet of yours with a few hundred rows of numbers.
The work
- No documented keyboard shortcut for this — use the menu: start with the quick rules. Select the amounts and go to Home → Conditional Formatting. Look through Highlight Cells Rules, which holds greater than, between and duplicates, and Top/Bottom Rules, which holds top 10 and above average. Apply "greater than 10000" and then edit a value. The highlight arrives or leaves by itself, and that is the whole point of the tool.
- Same either way: three visuals sit inside the cells, and they are not equal. Data bars turn a column into bars you can compare by length, and they are the best of the three for sizes. Colour scales paint the numbers as a heat map, which is good for seeing structure in a big grid and vague for reading one value. Icon sets put a symbol in each cell, so use the smallest set that says what you mean. Remember the accessibility lesson: colour alone carries nothing, and icons and bars survive a grey-scale printout where red-and-green shading does not.
- Same either way: the rule type professionals use is driven by a formula. Select the whole data rows, not one column, and add New Rule → "Use a formula". Write the test for the first row, with the column held and the row free:
=$E2>10000. Set the format. Every row whose own column E is above 10000 now lights up completely. The mixed reference is doing the work, and it is the reference rule from the formulas module put to use. - Same either way: overdue rows are the classic case.
=$D2 < TODAY()on the whole table marks every row whose due date has passed, and the report keeps itself current. - No documented keyboard shortcut for this — use the menu: when rules pile up, go to Home → Conditional Formatting → Manage Rules. It lists every rule on the sheet, in the order they are applied, with a Stop If True box for each. Two rules fighting over one cell are settled by that order, and this is where "why is this yellow?" is answered and fixed.
- Same either way: restraint is part of the craft. Three colours, not eleven. Formatting points at something. It does not decorate. If everything is highlighted then nothing is, and the reader's eye should land on one thing per screen. That means most cells stay plain.
Try it
On the sales sheet, put data bars on the amounts and bring out the top 10 per cent. Then mark every overdue row, or every row in the oldest month, using a formula rule that colours the whole row. Then open Manage Rules, read your own list in order, and delete everything that decorates rather than points.
Check yourself
What does conditional formatting do that colouring cells by hand cannot?
It stays true. The rule is worked out again as the data changes, so highlights arrive and leave with the facts. Hand colouring is a snapshot that starts lying at once.
Why does highlighting a whole row need =$E2>10000 rather than =E2>10000?
The rule is tested cell by cell across the selection. Holding the column with the dollar sign makes every cell in a row test that row's column E, so the whole row colours together. Without it, each column would test itself.
Two rules disagree about one cell. What decides?
The order in Manage Rules. The first rule wins where formats clash, and Stop If True halts the rest. That dialog is where the order is read and rearranged.
Which in-cell visual for comparing sizes down a column, and why?
Data bars. Length is the one thing people compare accurately by eye. Colour scales show structure and icons show categories, but bars show size.
Go deeper
We haven't checked most of these for screen reader use yet.