Data validation for account codes in Excel: stop bad codes at the door

One wrong digit in an account code sends an amount to the wrong account, or to an account that does not exist at all. Data validation lets you put a rule on a column so the spreadsheet refuses a bad entry the moment someone types it. This page covers the rules that suit account codes and amounts, how to take the allowed codes from your chart of accounts, how to find bad entries already in the sheet, and the mistakes that leave a hole in a rule.

What data validation is

Data validation is a rule attached to a range of cells. Whenever someone types into one of those cells, the entry is tested. If it passes, it goes in; if it fails, an error message appears and the entry is refused.

Most accounting checks happen afterwards: a formula or a red highlight tells you about a problem once it is in the sheet. Validation works earlier and stops the problem getting in. The two complement each other; the page on conditional formatting for accountants covers the highlighting side.

Why account codes need a rule

An account code is the key that decides where an amount ends up. If the code for Prepaid Rent is 1050 and someone types 1005, one of two things happens. Either 1005 belongs to a different account and the amount quietly lands there, or 1005 does not exist and the line cannot be posted until someone works out what was meant. Both are slow to find later, because the amount and description look normal. Typical slips are a digit dropped (1020 becomes 20), two digits swapped, or a plausible code that is not in this company's chart.

The rule types you will use

The Data validation dialog asks what to allow and then for the details of the rule. For account data, four kinds of rule cover almost everything.

Whole number between a minimum and a maximum

Many charts of accounts number their accounts by type: 1000s for assets, 2000s for liabilities, 3000s for equity, 4000s for revenue and 5000s for expenses. A rule that allows whole numbers between 1000 and 5999 enforces that shape. It refuses 20, 999, 6000, 1500.5, negative numbers and text such as "Cash". Choose Whole number rather than Decimal, because an account code never has a fraction.

Decimal greater than 0

If the debit or credit side of each line is recorded in a separate column, every amount should be positive. Allow Decimal (so cents are accepted), choose the comparison "greater than", and enter 0 as the minimum. "Greater than or equal to" would let a zero through, and a zero-dollar line has nothing to post.

List with a source range

A list rule accepts only the values in a list. You can type the list into the dialog, separated by commas, but for account codes it is far better to point the rule at the code column of the chart of accounts, for example =$G$5:$G$16. The dollar signs make the reference absolute, so every cell in the validated range checks against the same twelve codes instead of a range that shifts down one row per cell. The page on absolute cell references explains why that matters.

Taking the list from the sheet has a second benefit: when the chart changes, the rule changes with it. Edit a code in the chart, and the rule immediately accepts the new code and refuses the old one. A typed list would keep accepting the old code until someone remembers to edit the rule. A list rule also adds a drop-down arrow, so people can pick a code instead of typing it.

Text length

Codes stored as text with leading zeros, such as 0150, fail a whole-number rule. A text length rule set to "equal to 4" checks the number of characters instead. It accepts any four characters, so it is a weaker test.

A four-digit code is not the same as a code that exists

This is the key idea of the lesson. A range rule such as 1000 to 5999 tests the shape of a code: is it a whole number with four digits in the right band? It cannot know which codes the business actually uses. A chart of twelve accounts uses twelve of the five thousand numbers in that band, so a range rule happily accepts thousands of codes that point nowhere.

A list rule tests existence: is this exact code in the chart? It catches everything the range rule catches, because codes such as 20 or 6000 are not in the chart either, and it also catches the near misses that look valid. When the chart of accounts is in the workbook, a list rule is the stronger choice. The range rule is useful when the chart lives somewhere else and you only know the numbering scheme.

How to add the rules

1. Select the cells the rule should cover, for example the code column B5:B16. Select every row that will hold an entry, down to the last line.
2. Open Data validation from the Data tools.
3. For a range rule, set Allow to Whole number, the comparison to between, the minimum to 1000 and the maximum to 5999.
4. For a list rule instead, set Allow to List and the source to =$G$5:$G$16, covering the chart's codes from the first to the last row.
5. For the amount column, select E5:E16, open Data validation again, and set Allow to Decimal, the comparison to greater than, and the value to 0. Each column needs its own rule.
6. Use Circle invalid data to mark existing entries that break the rules.

Blank cells are allowed by default: the "ignore blank" option lets someone leave a cell empty without an error. That suits lines not yet filled in.

Finding bad entries that are already there: Circle invalid data

Because validation only tests new entries, a sheet can already hold values that break a rule you have just added. Circle invalid data draws a red circle around every cell in a validated range whose current value fails its rule. Nothing is changed; the circles are a to-do list. Correct each circled entry and its circle disappears.

Worked example: twelve lines for Bluebird Veterinary

Bluebird Veterinary has twelve journal lines waiting to be posted in A5:E16: date, code, account, description and amount. The chart of accounts sits beside them in G5:H16.

Here are the last four journal lines:

Two of these codes are wrong. In row 15, the account is Notes Receivable, whose code is 1020, but the leading digit was dropped and the cell holds 20. In row 16, the account is Repairs Expense, code 5050, but the cell holds 5055.

Now compare the two code rules. With a whole-number rule between 1000 and 5999, Circle invalid data circles row 15, because 20 is below 1000. It does not circle row 16: 5055 is a whole number between 1000 and 5999, so it passes, even though no account has that code. With a list rule whose source is =$G$5:$G$16, both rows are circled, because neither 20 nor 5055 appears in the chart. The other ten codes, from 5040 in row 5 down to 2020 in row 14, are all in the chart and pass either rule.

The list rule also protects future typing: nobody can enter 1005 or 4050, though both look normal. The amount rule on E5:E16 circles nothing here, since all twelve amounts are positive, but it will refuse a later 0 or -25.

Common mistakes and how to fix them

These are the mistakes our exercises check for. Each one leaves a rule that looks right but lets bad entries through or refuses good ones.

Decimals allowed

Choosing Decimal instead of Whole number for codes lets through 1500.5, which is not a code. Account codes are whole numbers, so allow Whole number.

Upper limit too high

Setting the maximum to 9999 instead of 5999 accepts 6000 and everything above it, codes outside this chart's numbering. Match the limits to the chart: here, 1000 to 5999.

Number range instead of the chart list

When the task asks for codes that exist in the chart, a range rule is not enough: it accepts 5055, as the worked example shows. Use a list rule with the chart's codes as its source.

List source one row short

A source of =$G$5:$G$15 misses the last code in the chart, so a correct entry of 5080 Supplies Expense is refused. Check that the source runs from the first code to the last, here G5:G16.

Zero allowed as an amount

"Greater than or equal to 0" accepts a zero-dollar line. Use "greater than 0" so every amount must be positive.

Second column left without a rule

Protecting the codes but forgetting the amounts still lets a zero or negative amount in. Each column needs its own rule; one rule on B5:B16 does nothing for E5:E16.

Where this fits in MO-220 practice

Restricting entries with data validation belongs to preparing data and keeping it reliable, alongside tasks such as splitting an account number from its name and flagging values outside an expected range. Our MO-220 practice tasks describe what the column must accept, such as "only codes from the chart of accounts", and grade what your rule actually accepts and refuses, so any rule that behaves correctly passes. Accounting Exercises is not affiliated with Microsoft or Certiport.