Blog · How-to guides · Finance and FP&A teams
Compare transaction first digits with Benford expectations in Excel. Build a checked frequency table, identify unsuitable populations, and trace unusual groups back to ledger rows.
Benford analysis in Excel compares transaction first digits with a logarithmic expected distribution. It can help a finance team choose ledger groups to inspect, provided the amounts come from a suitable population. Differences are screening signals, and neither a match nor a mismatch establishes whether transactions are legitimate.
Start with an invoice or journal export containing transaction ID, posting date, vendor, account, currency, and amount. Choose the amount field and period before viewing the digits. Use one currency, one documented transaction type, and one consistent level of detail.
Benford screening is more plausible for naturally arising amounts spread across a broad range than for assigned numbers, fixed prices, or amounts constrained to a narrow band. The AICPA's Journal of Accountancy walkthrough describes the first-digit distribution and circumstances where it is inappropriate. A contract schedule of repeated fixed fees needs a different review.
Do not pull only large invoices and compare them with the unrestricted Benford curve. A value filter changes the possible first digits. Likewise, a single vendor with a standard price list may be unsuitable even when the wider invoice population merits review.
Keep a selection log. Record excluded zeros, credits, duplicate exports, foreign-currency rows, and nonnumeric amounts, with their counts and values. Removing rows simply because they make the chart look unusual invalidates the exercise. Use ad hoc analysis in Excel to frame the business question before choosing a screen.
This miniature US accounts-payable extract illustrates the formulas. It is deliberately too small to support a judgment about conformity. Put transaction ID in column A, amount in B, and the calculated digit in C.
| Transaction | Amount (USD) | First significant digit |
|---|---|---|
| AP-A | 125 | 1 |
| AP-B | 1,480 | 1 |
| AP-C | 17,000 | 1 |
| AP-D | 216 | 2 |
| AP-E | 2,450 | 2 |
| AP-F | 320 | 3 |
| AP-G | 3,650 | 3 |
| AP-H | 41,000 | 4 |
| AP-I | 582 | 5 |
| AP-J | 6,430 | 6 |
| AP-K | 780 | 7 |
| AP-L | 96,000 | 9 |
| Total | 170,033 | 12 amounts |
First convert imported amounts to genuine numeric cells and resolve conversion errors. In C2, enter this formula and fill down through C13:
=IFERROR(INT(ABS(B2)/10^INT(LOG10(ABS(B2)))),"")
The power of ten removes the amount's scale, leaving its leading significant digit for INT. Zero produces an empty result because its logarithm is undefined. The absolute value permits digit extraction from negatives, but it does not authorize mixing credits into the selected population.
For example, AP-C becomes 17000 / 10^4 = 1.7, then INT(1.7) = 1. Microsoft documents LOG10 as the base-ten logarithm. Covirage is not affiliated with Microsoft.
Enter digits one through nine in E2:E10. Use F for observed count, G for observed share, H for expected share, and I for the percentage-point gap:
F2 =COUNTIF($C$2:$C$13,E2)
G2 =F2/COUNT($C$2:$C$13)
H2 =LOG10(1+1/E2)
I2 =100*(G2-H2)
Enter the expression after each cell label, then fill down. Format G and H as percentages; format I as a number because it already expresses percentage points.
Expected first-digit share = LOG10(1 + 1 / Digit)
| Digit | Observed count | Observed share | Expected share | Gap (percentage points) |
|---|---|---|---|---|
| 1 | 3 | 25.00% | 30.10% | -5.10 |
| 2 | 2 | 16.67% | 17.61% | -0.94 |
| 3 | 2 | 16.67% | 12.49% | 4.17 |
| 4 | 1 | 8.33% | 9.69% | -1.36 |
| 5 | 1 | 8.33% | 7.92% | 0.42 |
| 6 | 1 | 8.33% | 6.69% | 1.64 |
| 7 | 1 | 8.33% | 5.80% | 2.53 |
| 8 | 0 | 0.00% | 5.12% | -5.12 |
| 9 | 1 | 8.33% | 4.58% | 3.76 |
Shares and gaps are rounded to two decimals; compute gaps from unrounded cells. Displayed shares may not sum exactly because of rounding. A clustered column chart of G and H makes the comparison visible, but the underlying counts must stay beside it.
The digit count must equal the count of eligible, nonzero numeric amounts:
=SUM(F2:F10)-COUNT(C2:C13)
The result is zero: both counts are twelve. Separately, =SUM(B2:B13) must match the selected source total of $170,033. The expected shares sum to one before rounding because the logarithms telescope: LOG10(2/1) + ... + LOG10(10/9) = LOG10(10) = 1.
For a substantial eligible population, review unusual digits by account, vendor, and period without losing the original selection rules. Trace records to invoices and approvals. Outlier analysis in Excel addresses unusual amount sizes, while decile analysis in Excel compares ranked contribution; those questions differ from digit frequencies.
Bring the selected ledger workbook or Google Sheet with vendor, account, period, and amount columns to inspect its underlying transaction summaries. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for finance teams for more on working from company finance files.
No. An unusual pattern can come from pricing, approval limits, duplicates, population selection, or chance. Review the underlying records and supporting documents. A vendor-level digit difference is a reason to ask a question, not an accusation or an audit conclusion.
Usually they are unsuitable because they are assigned identifiers rather than naturally generated amounts. The same caution applies to account codes and ZIP codes. Analyze an eligible monetary measure, while retaining identifiers only to find the original records.
Zero has no first significant digit and must be excluded from the digit denominator. Review credits separately or document a justified absolute-value population. Never combine debits and credits silently, since their business processes and amount patterns may differ.