Data validation & conditional formatting

A sales register is only as good as what gets typed into it. Data validation controls what can be entered; conditional formatting controls what stands out once it is entered. Used together, they turn a fragile sheet into something a junior data-entry operator can be trusted with.

Data validation basics

Select the range, then Data → Data Validation. The Settings tab picks the rule, the Input Message tab shows a hint when the cell is selected, and the Error Alert tab decides what happens on a bad entry.

That last rule is the most valuable one in a billing sheet: it refuses a duplicate invoice number at the moment of typing, rather than during the month-end reconciliation.

Error Alert has three styles. Stop blocks the entry, Warning asks for confirmation, and Information merely notes it. Use Stop for invoice numbers and GSTIN, Warning for unusual-but-possible amounts.

Dependent dropdowns

When choosing a state should narrow the list of cities, put a spilled formula in a helper sheet and point the validation list at it:

'In Lists!B2
=SORT(UNIQUE(FILTER(Master[City], Master[State]=Entry!$C$2, "-")))

'Validation source for the City cell
=Lists!$B$2#

The older method uses named ranges and =INDIRECT($C$2), which still works but breaks on state names containing spaces.

Conditional formatting

The built-in rules (Highlight Cell Rules, Top/Bottom, Data Bars, Colour Scales, Icon Sets) cover simple cases: data bars inside an amount column give an instant sense of size without a chart.

The real power is Use a formula to determine which cells to format. The formula is written for the top-left cell of the applied range and Excel shifts it for every other cell, so the $ signs decide the behaviour:

Applies to: $A$2:$H$500

=$H2="Unpaid"                        ' whole row red when unpaid
=AND($E2<TODAY(), $H2="Unpaid")      ' whole row red only when also overdue
=COUNTIF($A:$A,$A2)>1                ' duplicate invoice numbers
=$G2>0.28                            ' impossible GST rate

Lock the column with $ and leave the row relative when you want to format an entire row based on one column. Using =$H$2="Unpaid" locks both and paints everything or nothing.

Rules are evaluated top-down in Manage Rules. Move the most important one up, and tick Stop If True so a lower rule does not overwrite its colour.

Common mistakes

• Writing the formula rule for the wrong anchor cell, for example starting the range at row 1 but writing $H2.
• Applying formatting to whole columns (A:H), which bloats the file and slows recalculation.
• Assuming validation protects the sheet: pasting into a validated cell bypasses the rule entirely. Protect the sheet or use Circle Invalid Data to audit.
• Too many colours. Three meanings at most: needs action, watch, fine.