The cheapest control is the one that stops a wrong entry from being typed in the first place.
Data validation limits what can be entered in a cell. Use dropdown lists for categories, statuses and names; date limits so entries fall in the period; whole-number or decimal limits for amounts; and custom formulas for rules like unique IDs or reviewer different from preparer. Add input messages, and remember validation does not check pasted data.
Put the allowed values in a list on a setup sheet, give the range a name (for example Categories), then select the input cells and choose Data > Data Validation > Allow: List, Source: =Categories.
Use dropdowns for anything that feeds a SUMIFS or a status test: account categories, cost centres, reconciliation status, and the names of preparers and reviewers. A free-typed "Travel " with a trailing space would otherwise drop out of the totals without any warning.
With the period start and end in cells named PeriodStart and PeriodEnd: Allow: Date, between =PeriodStart and =PeriodEnd. This catches the commonest journal error, the wrong month or year.
Allow: Decimal, greater than or equal to 0, for columns that must not be negative, such as invoice amounts entered separately from credit notes. For a maximum, link to a cell rather than typing the limit into the rule, so it can be changed in one place.
Unique reference (for example a journal or asset number in column A):
=COUNTIF($A:$A,A2)=1
Reviewer must differ from preparer (preparer in column F, reviewer in G):
=G2<>F2
Reason required when an amount is above a threshold (applied to the reason column H, threshold in a cell named Threshold):
=OR(E2<Threshold,LEN(TRIM(H2))>0)
Note that Excel only checks a rule when the cell itself is edited. For "this cell must not be left blank" rules, add a check column as well, because validation doesn't fire on an empty cell nobody touched.
Use the Input Message tab to tell the user what to enter ("Enter the invoice date, not the date received") and the Error Alert tab to explain what went wrong. Set the alert style to Stop for rules that must hold, and Warning where an exception is sometimes valid.
Data validation does not stop someone pasting invalid values over a validated cell, and it doesn't check data that was already there. Two safeguards:
Combine validation with sheet protection on formula cells, so the checks themselves can't be overwritten. See manual journal controls.
Data validation works the same in UK and US Excel. Date limits follow the workbook's stored dates, not the display format, so they work correctly whichever date order the user's computer shows.
12 questions on how strong your close controls are. Free to use online.
The Month-End Close & Controls System tests the trial balance against the sum of reconciled balances, keeps every open item on one exception dashboard and blocks sign-off while exceptions remain open. Excel, no macros, one-time price.
Yes, by pasting over cells or by changing the rules if the sheet isn't protected. Use check columns and protect the sheet.
Put the list in an Excel Table and point the named range at the table column; new rows are included automatically.
Google Sheets has its own data validation with dropdowns, dates, numbers and custom formulas. The custom formulas above work with small changes.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.