Most fraud in small and medium businesses is not sophisticated. It is a duplicate invoice paid twice, a payment just under the amount that triggers a second approver, an entry posted on a weekend. It is findable with tools you already have, in the file where the fraud happened: Excel.

This is the introduction to forensic accounting in Excel. The advanced guide covers Benford's Law, outlier detection and pattern recognition. This post covers what those techniques depend on: complete data, clean duplicate checks, anomaly spotting and preserved evidence. Skip the foundations and the advanced tests are analysing noise.

Why Excel Is Still the Workhorse of Forensic Accounting

Three practical reasons keep Excel at the centre of SME fraud reviews.

The data is born in Excel. Accounting packages export to Excel by default. Bookkeepers reconcile in it, payment runs are built in it, and financial modelling happens there too. When something is wrong, the trail starts and ends in a workbook someone forgot to check.

It is affordable. Dedicated forensic tools are priced for firms and large corporates, not SMEs. Excel is already on every desk, and these techniques use only built-in functions.

It is auditable, which matters most. Every number has a formula behind it, and every formula can be traced: press Ctrl + [ to jump to precedents, or use Evaluate Formula. A conclusion in Excel can be shown step by step to a director or auditor; a black-box system cannot. The formula is the audit trail.

Excel also has honest limits: it is not the tool for real-time monitoring, millions of rows without a Power Query pipeline, or a matter already heading to court. For the routine periodic review that catches most SME fraud, it is the right tool.

Data Integrity First: Prove the Data Before You Analyse It

Start by checking the data, not analysing it. Every test that follows assumes the extract is complete and accurate - prove that first.

Control totals

A control total is a figure from a source you trust: the month-end accounts payable balance, the trial balance, the bank statement closing balance. Sum the extracted amounts and compare:

=SUM(F2:F10001)

It should tie to the cent. If it does not, the extract, the source, or something between them is wrong. Resolve the difference first.

Record counts

The number of rows in the extract should match the number of lines on the source report:

=COUNTA(A2:A10001)

A missing row changes totals; a duplicated row inflates them. Either corrupts every downstream test, and a count is the cheapest way to catch both.

Reconciling back to source

Counting is not enough. Check the first and last rows against the source, sample rows in between, and sort by amount to confirm the largest items match. For a period extract, compare totals by month with SUMIFS against the source reports. A month with no postings is itself an anomaly.

Missing and duplicated rows

Sort by invoice number and flag gaps:

=IF(C3-C2>1,"GAP","")

Then count occurrences of each key field and filter for anything over one. Clean the data before counting: invoice numbers stored partly as text, stray spaces and leading zeros all break matching. If this is a monthly ritual, build the extraction once in Power Query so the cleaning repeats.

The Duplicate Detection Playbook

Duplicate payments are the most common finding in SME fraud reviews, and they are usually simple: the same invoice entered twice, or a line included twice in a payment run. Three layers:

Conditional formatting first

Select the invoice number column, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel shades every repeat instantly, no formulas required.

The limitation matches the strength: it highlights every repeat, including legitimate ones. Treat it as a pointer, not a verdict.

COUNTIF and COUNTIFS

For something you can filter, sort and audit, use a flag column with COUNTIF on a single field:

=IF(COUNTIF($C$2:$C$10001,C2)>1,"DUPLICATE","")

A duplicate invoice number is usually enough to start, but the strongest test uses the full record - invoice number, supplier, amount and date together:

=IF(COUNTIFS($C$2:$C$10001,C2,$D$2:$D$10001,D2,$F$2:$F$10001,F2)>1,"CHECK","")

Filter on the flag and you see both sides of the pair: the shape of a duplicate payment.

True duplicates vs legitimate repeats

COUNTIF cannot tell the difference, so you have to. A true duplicate is the same invoice entered twice or paid twice - two rows sharing the invoice number and amount where only one should exist. A legitimate repeat is a recurring charge with its own invoice number each month - a software subscription, rent - or a credit note paired with the invoice it reverses.

The distinction is in the reference: repeats carry different invoice numbers or dates; true duplicates share them. Never delete a flagged row on sight - mark it, check the documents, and recommend the correction through the proper channel. Deleting a row also deletes evidence.

What Anomalies Look Like in Transaction Data

With the data verified and duplicates flagged, the next pass looks for patterns that do not fit. Four cover most of what shows up in SME data.

Posting dates that do not fit

Weekend or public holiday postings, entries dated before the system was installed, and clusters just after period-end all warrant a look; period-end clusters can signal cut-off manipulation, revenue or expenses moved across a period boundary. A weekend flag is one formula:

=IF(WEEKDAY(A2,2)>5,"WEEKEND","")

Round-number amounts

People create round numbers; systems create odd cents. $500.00, $1,000.00 and $7,500.00 are the shape of a manually created entry, worth a second glance in expense claims, refunds and credit notes.

=IF(MOD(F2,100)=0,"ROUND","")

A single round amount is rarely suspicious; a run of them in one account, or under one approver, is another story. For more on unusual patterns, see our anomaly detection guide.

Amounts just below approval thresholds

The classic pattern: the policy says payments over $5,000 need a second approver, and the invoice is $4,980.

=IF(AND(F2>=4900,F2<5000),"REVIEW","")

Look at the distribution, not just individual rows. Many amounts clustered just under the limit and very few just over it is the signature of threshold shopping:

=COUNTIFS($F$2:$F$10001,">=4900",$F$2:$F$10001,"<5000")

Compare it with the range from $5,000 to $5,100; a large imbalance is worth raising even when no single row is conclusive.

Backdated entries

An entry posted before its invoice date, or dated into a prior period after the books closed, is a backdating red flag:

=IF(A2<B2,"BACKDATED","")

where column A is the posting date and column B is the invoice date. Legitimate reasons for odd dates exist - late invoices get entered after the fact - so treat this as "explain", not "guilty". A backdated journal that moves profit between periods is exactly what a forensic review exists to find.

Worked Example: One Supplier Payments Extract

The table below is a typical SME accounts payable extract: twelve supplier payments posted in March 2026. Five rows need explanation.

Assume the data sits in A2:F13 with dates stored as real Excel dates. Five formulas in helper columns find everything worth looking at:

=IF(COUNTIF($C$2:$C$13,C2)>1,"DUPLICATE","")
=IF(AND(F2>=4900,F2<5000),"UNDER LIMIT","")
=IF(MOD(F2,100)=0,"ROUND","")
=IF(WEEKDAY(A2,2)>5,"WEEKEND","")
=IF(A2<B2,"BACKDATED","")

What the flags find:

• Rows 3 and 4 are the same invoice entered and paid twice. Same invoice number, amount and supplier: a duplicate payment.
• Rows 3, 4 and 10 sit under the $5,000 approval threshold. INV-2216 at $4,980 appears twice and INV-2222 at $4,990 sits just under the limit. Not proof of fraud - $4,990 may be a legitimate quote - but each needs its approval trail checked.
• Row 9 is exactly $5,000. At the threshold, it required the second approver - confirm the approval exists.
• Row 11 was posted on a Saturday. The invoice is dated three days earlier, so it may be innocent, but it needs an explanation.
• Row 12 was posted on 25 March against an invoice dated 30 March. The entry exists before the invoice it records - the shape of a backdated entry, and the most serious flag in the table.

Five rows out of twelve need a closer look, found in ten minutes with built-in functions - the short list is what you take to the next stage.

Building a Simple Exception Report

The worked example was a one-off. The professional version is an exception report: a workbook that applies the same rules every period and lists only the failing rows.

The data sheet. The transaction extract, refreshed each period, structured identically so the formulas never change.

The rules sheet. A small table with the rules and thresholds in cells, not buried in formulas:

The exception columns. One formula per rule, with named ranges pointing at the thresholds on the rules sheet:

=IF(AND(F2>=low_lim,F2<high_lim),"UNDER LIMIT","")

where low_lim and high_lim are named cells on the rules sheet. Change the threshold once and the whole report follows - parameters live in cells, nothing is hard-coded twice.

Bring the rules together with an overall flag:

=IF(COUNTIF(G2:J2,"*")>0,"EXCEPTION","OK")

assuming columns G to J hold the rule flags. Filter on "EXCEPTION", work through the list, and record the disposition of every flagged row: explained, investigated or corrected. File the report with the period's records; the second month takes ten minutes.

Preserving the Evidence

Everything so far assumes the review can survive scrutiny. The discipline is simple and non-negotiable.

Never edit the original. Copy the file first. Save the extract as AP_Extract_2026_03_SOURCE.xlsx, mark it read-only, and work in a separately named copy such as AP_Review_2026_03_WORKING.xlsx. If the working copy is corrupted, the source is untouched; if the matter goes anywhere formal, the untouched source is the exhibit.

Document every step. Keep a control log - a sheet in the working workbook or a short note alongside - recording what was done, when and by whom. Every filter and formula should be traceable. Avoid deleting rows; use a flag column instead, so nothing disappears from the record.

Keep the chain of custody note. A short table recording who provided the file, when, from where, what was done to it, and who holds copies:

If the matter reaches a lawyer, an insurer or the ATO, that note is the difference between a usable exhibit and a file whose history can be challenged. Two practical points: do not run the review on the suspect's own computer, and keep working copies off shared drives.

When to Escalate: Advanced Techniques and the Professionals

The foundations produce a short list of rows that need explanation, not proof. The next rung is the advanced guide, which runs statistical tests on the verified dataset: Benford's Law on leading digits, outlier detection on amounts, pattern recognition across accounts. Those tests answer a different question - not what is obviously wrong, but what is suspiciously distributed - and only mean anything on data that passed the integrity checks in this post.

Escalate when the stakes pass the spreadsheet's ceiling: amounts material to the business, signs of deliberate concealment such as backdated entries or overridden controls, or a matter that could reach court, an insurer or a regulator. A forensic specialist - a CA or CPA with a forensic specialisation, or a Certified Fraud Examiner - follows evidence-handling procedures and reporting standards a spreadsheet review cannot replicate. At that point, stop analysing, preserve the file and hand over your working copy and control log - do not confront the person involved.

The pattern across these guides is deliberate. The advanced techniques are only as good as the data they run on, and the data is only as good as the integrity checks in this post. Control totals, duplicate flags and exception reports are not the glamorous part of forensic accounting; they are what makes every other part trustworthy - and for most SMEs, what catches the fraud. The fraud was sitting in a spreadsheet all along, waiting for someone to check it properly.

Related reading

• Excel Forensic Accounting: Advanced Fraud Detection
• Anomaly Detection in Excel: Unusual Data Patterns
• Excel Due Diligence: Deal Review Workbook