Pre-numbered documents are one of the oldest controls in accounting. Invoices, receipts, cheques, goods received notes and fuel coupons are numbered in sequence so that every document can be accounted for. When a number goes missing, something has happened: a cancelled document, a system glitch, or a transaction that someone did not want recorded. Gap testing is the audit data technique that finds those missing numbers quickly and completely, across the whole population rather than a sample.
This article explains what gap testing finds, walks through a worked example, and gives you step-by-step Excel instructions you can try on your own data today.
What gap testing finds and why it matters
A gap test sorts a set of document numbers and checks whether each number follows the previous one without a break. Any break is a gap; any repeat is a duplicate. Both are exceptions that need an explanation.
Gaps matter because they point to completeness risk. If a sales invoice was issued but never recorded, revenue and VAT are understated. If a receipt book has missing numbers, cash may have been collected and not banked. If a cheque number is missing from the cheque register, a payment may have gone out without approval.
Typical places to run the test in a Zimbabwean organisation include:
- Sales invoices and fiscal tax invoices exported from the billing system, where unrecorded sales affect both revenue and VAT returns.
- Cash receipts at revenue points in local authorities, hospitals, schools and parastatals, where manual receipt books are still common.
- Cheque and payment voucher numbers in NGOs and public sector entities.
- Bank and microfinance transaction references, including teller receipts and loan disbursement vouchers.
- Multi-currency ledgers, where ZiG and USD transactions may run on separate number series. Test each series on its own, otherwise the gaps in one series are hidden by numbers from the other.
Not every gap is fraud. Voided documents, system resets at year end and numbers reserved for another branch are all common causes. The value of the test is that it gives you a complete list of exceptions, so you can ask management to explain each one with evidence.
A worked example
Below is an extract of sales invoices for September 2026, already sorted by invoice number. Column D strips the prefix to leave a number, and column E calculates the gap from the previous row.
| Row | A: Invoice No | B: Date | C: Amount (USD) | D: Number | E: Gap |
|---|---|---|---|---|---|
| 2 | INV-1001 | 01/09/2026 | 1,250.00 | 1001 | – |
| 3 | INV-1002 | 01/09/2026 | 480.00 | 1002 | 0 |
| 4 | INV-1003 | 02/09/2026 | 2,100.00 | 1003 | 0 |
| 5 | INV-1005 | 03/09/2026 | 760.00 | 1005 | 1 |
| 6 | INV-1006 | 03/09/2026 | 3,400.00 | 1006 | 0 |
| 7 | INV-1007 | 04/09/2026 | 125.00 | 1007 | 0 |
| 8 | INV-1008 | 05/09/2026 | 990.00 | 1008 | 0 |
| 9 | INV-1011 | 08/09/2026 | 4,750.00 | 1011 | 2 |
| 10 | INV-1012 | 08/09/2026 | 610.00 | 1012 | 0 |
| 11 | INV-1012 | 09/09/2026 | 610.00 | 1012 | -1 |
| 12 | INV-1013 | 10/09/2026 | 1,880.00 | 1013 | 0 |
| 13 | INV-1014 | 11/09/2026 | 300.00 | 1014 | 0 |
Reading column E:
- 0 means the number follows the previous one correctly.
- A positive number is the count of missing numbers. Row 5 shows INV-1004 is missing; row 9 shows INV-1009 and INV-1010 are missing.
- -1 means a duplicate. INV-1012 appears twice, on different dates, for the same amount. That deserves a separate look as a possible duplicate billing or a re-used number.
The range runs from 1001 to 1014, which is 14 possible numbers. There are 11 unique numbers in the data, so 3 are missing, which matches the gaps found.
Step-by-step: gap testing in Excel
Step 1: Prepare the data
- Export the full document register for the period, not a filtered report. Include voided and cancelled documents if the system records them.
- Check the record count and total against the general ledger or system report so you know the population is complete.
- If there are several series (by branch, currency or document type), add a column for the series and test each one separately.
Step 2: Convert document numbers to values
Document numbers often carry a prefix. In D2, strip it and convert to a number:
- =VALUE(SUBSTITUTE(A2,”INV-“,””))
If the numeric part is always the last four digits, =VALUE(RIGHT(A2,4)) also works. Copy the formula down, then sort the table by column D, smallest to largest.
Step 3: Calculate the gap
In E3 (the second data row), enter:
- =D3-D2-1
Copy it down. Leave E2 blank because the first record has nothing before it. If you are testing several series in one sheet, sort by series then number, and use =IF(F3=F2,D3-D2-1,””) where column F holds the series, so the test restarts for each series.
Step 4: Highlight the exceptions
- Select column E, then go to Home > Conditional Formatting > Highlight Cells Rules > Not Equal To and enter 0.
- Filter column E to show non-zero values. These are your gaps and duplicates.
- To list duplicates directly, add =COUNTIFS($D$2:$D$13,D2)>1 in a helper column. Any TRUE is a repeated number.
Step 5: List every missing number
In Excel 365, one formula produces the full list of missing numbers:
- =LET(n,D2:D13,s,SEQUENCE(MAX(n)-MIN(n)+1,,MIN(n)),FILTER(s,COUNTIF(n,s)=0,”No gaps”))
For the example above, it returns 1004, 1009 and 1010. In older versions of Excel, type the full expected range in a separate column (1001 to 1014) and use =COUNTIF($D$2:$D$13,H2)=0 beside it to flag the missing ones.
Step 6: Summarise
Insert a pivot table with Series in rows and a count of Gap values greater than zero, so you can see at a glance which branch, cashier or currency series has the most exceptions.
Following up on the exceptions
The test only tells you where to look. For each gap or duplicate:
- Ask for the physical or system record of the missing document, such as a voided original with all copies attached.
- Check the system audit log for deletions or number resets around the dates involved.
- Look at who issued the documents on either side of the gap. Repeated gaps around one user or one revenue point are a red flag.
- For duplicates, compare the customer, amount and date. The same amount to the same customer on consecutive days may be a double charge.
- Document the explanation and evidence for each item in your working papers. A tool such as Auditors Track GRC lets you record the finding, attach the evidence and track management’s response in one place.
Common mistakes to avoid
- Testing a filtered extract. If the export excludes cancelled documents, every void looks like a gap and real issues get lost in the noise.
- Mixing number series. Combining branches or ZiG and USD series in one list hides gaps and creates false ones.
- Sorting as text. If numbers stay as text, INV-1010 sorts before INV-102. Always convert to values first.
- Ignoring the ends of the range. A gap test only checks between the lowest and highest numbers in your data. Compare the first and last numbers with the closing numbers of the previous and next periods to catch missing documents at the edges.
- Stopping at the list. An unexplained gap is a finding; a list of gaps with no follow-up is not audit evidence.
Conclusion
Gap testing is simple, fast and covers 100% of the population. With a few formulas you can account for every invoice, receipt or cheque number in a period, find duplicates at the same time, and give management a precise list of items to explain. Run it on one document series this week and you will have a repeatable test for every audit that follows.
Take gap testing further with ProDataTool
Excel works well for a few thousand documents. When you are testing a full year of receipts across dozens of revenue points, or millions of bank transactions, spreadsheets slow down and become hard to control. ProDataTool is a desktop data analysis application built for auditors, risk analysts, accountants and business analysts. It handles datasets from thousands to millions of rows, organises your work into projects, and gives you descriptive statistics, cross-tabulation, pivot analysis, summarisation and custom filtering to isolate exceptions by branch, user or currency.
See it on your own data. Book a free ProDataTool demo today.