Spotting Data Entry Errors With Conditional Formatting

Data validation tries to stop a bad value from going in. Conditional formatting does something humbler and often more useful: it lets the value in, and then points at it. A red cell next to an order that has no customer, an amber date that is three years in the future, a duplicate invoice number lit up in yellow. People fix what they can see, and they fix it while the paper is still on their desk. This article gives the rules that catch the most common entry errors, written as formulas, with the reference mistakes and performance traps to avoid. It complements controlling what goes into a spreadsheet and uses the mechanics from writing conditional formatting from code.

How formula rules work

In Home, Conditional Formatting, New Rule, choose "Use a formula to determine which cells to format". Select the range first. Write the formula as if for the top left cell of the selection. Excel evaluates it for each cell and adjusts references the way a copied formula would.

The reference style decides what gets flagged. Suppose the selection is A2:F500 and you want to highlight the whole row when column C is blank:

=$C2=""

The $C fixes the column so every cell in the row looks at column C; the 2 is relative, so each row looks at its own row. Without the dollar sign (=C2=""), cell D2 would look at D2, E2 would look at E2 and the highlight would land on the wrong cells. Mixed references are the single most common source of "the rule highlights the wrong thing".

The rules that catch the most errors

Assume a table of stock counts in rows 2 to 500 with columns A site, B location, C sku, D qty, E counted_on, F counted_by.

Duplicate keys. Highlight rows whose SKU and location already appeared:

=COUNTIFS($B$2:$B$500, $B2, $C$2:$C$500, $C2) > 1

This marks both copies, which is what you want when the reader must decide which is wrong. The absolute ranges $B$2:$B$500 stay put while the row-relative $B2 moves. On a few thousand rows this is fine; on tens of thousands it slows down, because each cell scans the column, which is quadratic. For big sheets, count duplicates in a helper column once, or leave this check to the loader.

Required cell is blank. Highlight the cell itself:

=AND($C2<>"", D2="")

The AND stops flagging rows that are entirely empty, so the empty rows below the data stay clean. Without it, 400 empty rows light up in red.

Number outside range.

=AND(ISNUMBER($D2), OR($D2<0, $D2>100000))

ISNUMBER keeps text from raising comparison oddities; add a separate rule for text in a numeric column:

=AND($D2<>"", NOT(ISNUMBER($D2)))

Date outside the period. For counts that must be within the last seven days:

=AND(ISNUMBER($E2), OR($E2 < TODAY()-7, $E2 > TODAY()))

TODAY() is volatile, which is acceptable in a rule on a modest range. For a fixed reporting period, compare against two cells holding the period start and end ($H$1, $H$2) so the rule shows its parameters.

A date typed as text. It will not sort or compare:

=AND($E2<>"", NOT(ISNUMBER($E2)))

Excel left-aligns text, so the misaligned cell is often a visual clue as well (importing Excel dates correctly explains why this matters downstream).

Stray spaces. Text with leading or trailing spaces breaks lookups:

=AND(ISTEXT($C2), $C2<>TRIM($C2))

Non-breaking spaces survive TRIM, so add a check ISNUMBER(SEARCH(CHAR(160), $C2)) when pasted data is common (trimming spaces and non breaking spaces).

Value not in the list.

=AND($C2<>"", COUNTIF(Lists!$A$2:$A$2000, $C2)=0)

This is the safety net behind a dropdown: it flags values that arrived by pasting. Cross-sheet references in conditional formatting work in current Excel; in very old versions use a named range.

Pair checks. A location that does not belong to the site: =AND($B2<>"", COUNTIFS(Locations[Site], $A2, Locations[Code], $B2)=0). Structured references are not allowed in conditional formatting formulas directly, so use a named range for the table column, or an INDIRECT-free reference like Lists!$B$2:$B$200.

Inconsistent case or format. For codes that must be upper case: =AND(ISTEXT($C2), NOT(EXACT($C2, UPPER($C2)))).

Outliers. A quantity more than three standard deviations from the mean of its column: =AND(ISNUMBER($D2), ABS($D2-AVERAGE($D$2:$D$500)) > 3*STDEV($D$2:$D$500)). Use sparingly; outliers are sometimes real.

Colors and meaning

Use at most three colors with fixed meanings, and write the legend on the instructions sheet. A workable scheme: red for "must fix before submitting" (blank required cells, duplicates, invalid values), amber for "check" (outliers, a date near the edge of the period) and no color for fine. Light fills with dark text read better than saturated fills. Remember that about one man in twelve cannot tell red from green, so use a second signal: bold text, a pattern fill or a status column that says "Fix". The conditional formats in the status column can then be words, which also survive a CSV export.

A helper status column in the table does double duty:

=IF([@sku]="", "missing SKU", IF(COUNTIFS([sku], [@sku], [location], [@location])>1, "duplicate", "ok"))

It gives text that a loader can read, and it gives a conditional format something simple to key on (=$G2<>"ok"). The count of non-"ok" rows then becomes the number at the top of the sheet.

Order of rules and conflicts

Several rules can apply to one cell. Excel evaluates them in the order listed in Manage Rules, and when two set the same property (say, fill color), the higher one wins unless "Stop If True" is set. Put the most severe at the top. If a cell is both a duplicate and out of range, red should beat amber. Check the order after any edit, and avoid dozens of overlapping rules; consolidate with OR.

Copying and pasting cells copies their conditional formats too, and over time sheets accumulate fragments: rules that apply to A2:A7, A9:A55 and A57:A500, the result of rows inserted and deleted. Open Manage Rules for "This Worksheet" now and then, delete the fragments and recreate one rule over the exact range. A file with hundreds of fragments is slow and mysterious.

Performance

Conditional formatting is evaluated whenever the sheet redraws. A few simple rules on 5,000 rows cost nothing you will feel. Trouble starts with:

• Whole-column ranges (A:A) in the "applies to" box, so Excel evaluates a million cells.

• Quadratic formulas, like COUNTIF over the whole column for every cell, on large tables.

• Volatile functions (INDIRECT, OFFSET, TODAY) across large ranges.

• Many overlapping rules from copy and paste.

Limit the range to the data (or to a table, where it expands with the table), prefer one helper column that calculates a flag once, and keep rules that need a scan for the loader. If the sheet becomes sluggish after adding rules, switch the rules off one at a time to find the culprit.

Writing the rules from code

A generated template should carry its rules:

red = wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
ws.conditional_format("A2:F500", {
    "type": "formula",
    "criteria": '=AND($C2<>"", $D2="")',
    "format": red,
})
ws.conditional_format("C2:C500", {
    "type": "formula",
    "criteria": '=COUNTIFS($B$2:$B$500,$B2,$C$2:$C$500,$C2)>1',
    "format": red,
})

XlsxWriter's criteria strings are written exactly as in the dialog, relative to the top left cell of the range. Keep them in one module with comments naming the reason for each, so that a change is reviewable (version controlling the query behind an Excel report applies to templates too).

A visible count of problems

Add a cell at the top of the sheet that counts flagged rows, and make it big:

=SUMPRODUCT(--($G2:$G500<>"ok"), --($C2:$C500<>""))

with the label "Rows to fix". When it reads 0, the sheet is ready to submit. The same formula, evaluated by the loader, tells you whether the file passed. People respond to a number they can drive to zero far better than to a general request to check the data.

When highlighting is not enough

Highlighting only works if someone looks. A returned file can arrive with 40 red cells, because people ignore the color or submit in a rush. Highlighting does not stop the file from being sent, does not apply to values added by a script and does nothing in the loader. So pair it with an import check that rejects the file or the rows, and returns the list of problems (checking an incoming workbook against a template by code). It is the right tool for the person filling in the sheet, and the wrong tool for the person responsible for the database. The openpyxl formatting guide covers rules in that library, and the XlsxWriter conditional format documentation covers the code.