Most auditors expect the leading digits in a ledger to be spread evenly: about as many amounts starting with 9 as with 1. In real financial data that is not what happens. Far more amounts start with 1 than with 9, and the pattern is predictable enough to test. That pattern is called Benford’s Law, and it is one of the quickest ways to find out where to look in a large set of payments, expense claims or journal entries.
This guide covers what Benford’s Law is, what it can and cannot tell you, and how to run a first-digit test in Excel on your own data today.
What Benford’s Law says
The astronomer Simon Newcomb noticed the pattern in 1881. The physicist Frank Benford tested it on thousands of data sets and published his results in 1938. In data that arises naturally, the chance that the first digit is d equals LOG10(1 + 1/d). That gives these expected proportions:
| First digit | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| Expected % | 30.1 | 17.6 | 12.5 | 9.7 | 7.9 | 6.7 | 5.8 | 5.1 | 4.6 |
Mark Nigrini brought the law into auditing in the 1990s. His point was simple: people who make up numbers, or keep amounts just under a limit, usually leave digit patterns that differ from Benford. A first-digit test shows you those differences.
When the test works, and when it doesn’t
Benford’s Law is a way to choose where to look. It does not prove fraud. Use it on data that:
- Has enough records. A few hundred is the minimum, and thousands is better. On small samples, ordinary chance can look like a red flag.
- Covers several orders of magnitude. For example, amounts from tens to millions.
- Is not assigned or capped. Invoice numbers, phone numbers and fixed prices will not follow Benford. Neither will a population of identical allowances.
Data that usually suits the test includes supplier payments, expense claims, sales invoices, journal entry amounts and insurance claims. Look closely at the data before you test it. If your ledger mixes ZiG and USD transactions, run each currency separately. Combining them blends two different scales and can distort the digit pattern.
A good place to start is any approval limit. If payments above USD 5,000 need a second signature, an unusual number of payments starting with 4 (USD 4,000 to 4,999) is worth looking at. The same goes for tender thresholds in public sector procurement, petty cash limits in NGOs and override limits in banks.
Worked example: 2,400 supplier payments
An internal auditor at a mid-sized parastatal extracts 2,400 USD supplier payments for the year. Each payment is at least USD 10. Payments above USD 5,000 need the finance director’s approval. The table below is an illustrative result of a first-digit test.
| First digit | Actual count | Actual % | Expected % | Expected count | Difference (pts) |
|---|---|---|---|---|---|
| 1 | 690 | 28.7 | 30.1 | 722 | -1.4 |
| 2 | 410 | 17.1 | 17.6 | 423 | -0.5 |
| 3 | 295 | 12.3 | 12.5 | 300 | -0.2 |
| 4 | 300 | 12.5 | 9.7 | 233 | +2.8 |
| 5 | 170 | 7.1 | 7.9 | 190 | -0.8 |
| 6 | 150 | 6.2 | 6.7 | 161 | -0.4 |
| 7 | 135 | 5.6 | 5.8 | 139 | -0.2 |
| 8 | 120 | 5.0 | 5.1 | 123 | -0.1 |
| 9 | 130 | 5.4 | 4.6 | 110 | +0.8 |
The mean absolute deviation (MAD) for this data set is about 0.0081. Nigrini’s first-digit benchmarks put that in the “acceptable conformity” range. A quick reviewer might stop there. But digit 4 has about 67 more payments than expected, and they sit just below the approval threshold. The next step is to filter the payments between USD 4,000 and 4,999. Then group them by supplier, by the officer who approved them and by date. Look for split invoices, the same supplier paid several times on one day, and round amounts.
The lesson is this: always read the individual digits, not just the summary statistic.
Step-by-step: running the test in Excel
1. Prepare the data
- Paste the amounts into column A of a new sheet, with the heading Amount in A1.
- Remove zero, negative and blank values, or test credits as a separate population. Also exclude amounts below 10, which are usually immaterial and add noise.
- Convert the range to a table (Ctrl+T) and name it Pay.
2. Extract the first digit
In B1 type FirstDigit. In B2 enter:
=INT(A2/10^INT(LOG10(A2)))
This works for any positive amount, including decimals such as 0.75 or 1,250.40. Text-based formulas such as LEFT() can be thrown off by leading zeros and decimal points, so this formula is safer.
3. Build the summary table
- On a new sheet, enter the digits 1 to 9 in cells D2:D10.
- Actual count in E2: =COUNTIFS(Pay[FirstDigit],D2)
- Actual % in F2: =E2/SUM($E$2:$E$10)
- Expected % in G2: =LOG10(1+1/D2)
- Expected count in H2: =G2*SUM($E$2:$E$10)
- Absolute difference in I2: =ABS(F2-G2)
- Fill all formulas down to row 10. Calculate MAD in I11: =AVERAGE(I2:I10)
4. Visualise and flag
- Select columns D, F and G and insert a clustered column chart, or a column chart with the expected % shown as a line. Spikes stand out right away.
- Apply conditional formatting to column I using Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items, set to 2. This highlights the two digits that deviate most.
5. Drill down
Filter the Pay table on the flagged digit. Then insert a pivot table with Supplier in Rows and Amount in Values, set to both Count and Sum. Add the approver or user ID if your extract has it. Suppliers or users who account for most of the excess are your sample for vouching.
Reading the results without overreacting
- Look for a business explanation first. A spike can come from a recurring contract amount, a standard fee or a regulated price. Write down the explanation and move on.
- Use MAD as a guide. Nigrini’s first-digit ranges are roughly: 0.006 or below is close conformity, 0.006 to 0.012 is acceptable, 0.012 to 0.015 is marginal, and above 0.015 is nonconformity. On very large data sets, chi-square tests flag almost everything, which is why many practitioners prefer MAD.
- Go further when you need to. First-two-digit tests (10 to 99) find threshold avoidance much more precisely. Last-digit and round-number tests are useful for finding invented amounts.
- Document it. Keep the population, the exclusions, the summary table, the chart and your follow-up in your working papers. If you manage engagements in Auditors Track GRC, you can attach the workbook as evidence and log any exceptions as findings.
Common mistakes
- Running the test on a few dozen transactions and treating every deviation as meaningful.
- Testing assigned numbers, such as invoice or cheque numbers. Use gap testing for those.
- Mixing currencies, entities or transaction types in one population.
- Reporting a “Benford failure” as a finding before any vouching has been done.
Conclusion
Benford’s Law gives you a quick, low-cost way to decide where to focus in large financial data sets. With five formulas and a pivot table you can test a year of payments in under an hour. Most importantly, you will have a clear reason for choosing which transactions to examine closely. Start with a population you know well, compare the spikes against your approval limits, and follow up on what the data shows you.
Take Benford testing beyond Excel
Excel handles a few thousand rows easily. But when you need a full year of general ledger lines, a bank’s transaction history or several years of payroll, workbooks become slow and harder to control. ProDataTool is a desktop data analysis application for auditors, risk analysts, accountants and business analysts. It handles data sets from thousands to millions of rows. It keeps each review in its own project, and gives you descriptive statistics, cross-tabulation, pivot analysis, summarisation and custom filtering. That lets you stratify, filter and drill into flagged digits without Excel slowing down.
Book a free demo of ProDataTool and see how it handles your largest data sets.