Data Deduplication in Excel: Keep Financial Records Clean

Q: How do I remove duplicates in Excel without deleting data I need?

Use Excel's Remove Duplicates feature under Data tab but select only the columns that define uniqueness. Always copy your worksheet first and validate totals before and after to catch accidental removals.

It Only Takes One Duplicate Invoice to Trigger an ATO Review

Your accounts payable clerk processes 50,000 supplier invoices a year. A glitch in the MYOB-to-Excel import doubles 4,200 of them. Your BAS now claims $52,500 in inflated input tax credits.

That is not a hypothetical. For Australian businesses running manual data workflows, duplicate entries are the most common source of BAS errors that get picked up in ATO data-matching reviews. And the fix is almost always in Excel.

This guide covers the three Excel methods that handle most deduplication scenarios, when to use each, and how to keep your financial data accurate for BAS, GST, and reporting.

The Problem: Why Duplicate Data is Costing You

Duplicate data does not just clutter your spreadsheets. It causes real financial damage:

BAS and GST errors: Duplicate invoices inflate input tax credits. The ATO's data-matching program compares supplier-reported transactions against your BAS claims. Discrepancies trigger compliance letters and potential penalties.
Double payments: A supplier submits an invoice, you pay it. The duplicate entry in your system triggers a second payment. Recovering that money costs time and damages supplier relationships.
Faulty reports: Duplicate revenue entries inflate your P&L. Duplicate cost entries hide true margins. Management decisions based on faulty data are worse than no decisions at all.
Wasted time: Your finance team spends hours each month reconciling, investigating, and fixing duplicates. Time that should go into analysis and strategy.

A typical Australian SME with manual data entry loses significant finance team capacity to duplicate-related cleanup. The fix is not expensive software. It is knowing the right Excel method.

Worked Example: Cleaning a Construction Business AP Database

Let us make this concrete. A Melbourne-based construction business processes 50,000 supplier invoices per year. An audit of their accounts payable data reveals:

4,200 duplicate invoice numbers: Same invoices entered twice due to an import bug between Xero and Excel. Each invoice averages $1,250. At 10% GST, the BAS is overstated by approximately $52,500.
800 near-duplicate supplier names: "ABC Electrical Pty Ltd", "ABC Electrical", "A.B.C. Electrical" - each treated as a separate supplier record, causing fragmented payment runs and missed settlement discounts.
300 transactions with duplicate line items but different batch IDs: Materials cost recorded against both the project cost code and the general expense code.

Here is how to fix each problem using Excel.

Method 1: Remove Duplicates (Best for Exact Matches Under 10,000 Rows)

Use this when: You have exact duplicates in a single column (like invoice numbers) and fewer than 10,000 rows.

The built-in Remove Duplicates command is the fastest tool for straightforward cases:

Select your data range (Ctrl+A).
Go to the Data tab and click Remove Duplicates.
In the dialog box, check only the columns that define a duplicate. For invoices, that is usually just the Invoice Number column.
Click OK. Excel tells you how many duplicates were removed and how many unique values remain.

For the construction AP example: Remove Duplicates on the Invoice Number column eliminates the 4,200 exact duplicates in seconds. The BAS position is corrected immediately.

Critical precautions:

Always copy your worksheet first. Run Remove Duplicates on the copy, not the original.
Validate totals before and after. The sum of invoice amounts should drop by exactly the sum of the removed duplicates.
Only check columns that truly define uniqueness. Selecting all columns will remove rows that are identical in every field - which is rare in real data and likely to delete legitimate records.

Method 2: Power Query (Best for 10,000 to 100,000 Rows and Fuzzy Matches)

Use this when: Your dataset is too large for Remove Duplicates, or you have near-duplicate names like supplier names with inconsistent formatting.

Power Query (Get & Transform) is included in Excel 2016 and later. It handles larger datasets without freezing and can detect fuzzy matches:

Select your data and go to Data > From Table/Range to open Power Query.
Select the column(s) to deduplicate on.
Go to Home > Remove Rows > Remove Duplicates.
For near-duplicates: use Transform > Group By, enable Use fuzzy grouping, and set the similarity threshold (80-90% works well for company names).

For the construction AP example: Export the supplier names column to Power Query and run fuzzy grouping with a 85% similarity threshold. This collapses the 800 near-duplicate supplier names into approximately 620 unique suppliers, consolidating 180 duplicate records.

Power Query also supports scheduled refresh, which is ideal for monthly AP deduplication workflows. See our guide on Excel Power Query for data cleaning automation for a deeper walkthrough.

Method 3: Conditional Formatting (Best for Visual Inspection Before Deleting)

Use this when: You are unsure whether certain records are genuine duplicates and want to review before taking action.

Conditional formatting highlights duplicates without removing anything:

Select your range.
Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
Choose a formatting colour and click OK.

Duplicates are highlighted in your chosen colour. Scroll through and inspect each group. When you are confident, remove duplicates using Method 1 or 2.

Use case: The construction AP team can use conditional formatting on the batch ID column to spot the 300 transactions with duplicate line items. Reviewing visually helps them decide whether to keep the general expense entry or the project cost code entry before running a bulk deduplication.

Conditional formatting is also excellent as a first pass on any new dataset. Highlight duplicates, skim through, and you will immediately understand the shape of your data quality problem.

Choosing the Right Method: A Decision Flow

Not sure which method to use? Follow this logic:

If your data looks like this	Use this method	Why
Under 10,000 rows, exact duplicates, single column	Remove Duplicates (Data tab)	Fastest, no setup needed
Under 10,000 rows, near-duplicate text names	Power Query fuzzy grouping	Catches variations standard dedup misses
10,000 to 100,000 rows	Power Query	Handles larger data without Excel freezing
Over 100,000 rows	Python Pandas or SQL	Excel struggles at this volume
Unsure what counts as a duplicate	Conditional Formatting first	Visual review before action
Monthly recurring cleanup	Power Query	Save and refresh the query each month

For datasets over 100,000 rows or when you need repeatable automated pipelines, Python's drop_duplicates() or SQL's ROW_NUMBER() with partitioning are stronger options. But for most Australian SME accounting scenarios, the three Excel methods above handle 90% of deduplication needs.

Validation: The Step Everyone Skips

Removing duplicates is only half the job. You must validate the result. ATO compliance requires documented data controls.

Before removing: calculate control totals per column (sum of invoice amounts, count of records). After removing: recalculate. The difference should equal the sum of removed records only. Cross-check a random sample of 20-30 removed records against source documents to confirm they were genuine duplicates. Document your deduplication logic: which columns, which method, keep first or keep most recent. Store this with your monthly close documentation.

This is not busywork. If the ATO ever queries your BAS, your documented deduplication process demonstrates reasonable care. Without it, you are guessing.

Prevention is Better Than Cleanup

The best deduplication is the one you never need to run. Set up these preventive measures:

In Xero/MYOB: enable duplicate invoice detection. Both platforms flag invoices with matching numbers or amounts before posting.
In Excel: use Data Validation on the Invoice Number column to prevent entry of existing values.
In Power Query: set up a monthly deduplication query that your AP team runs during month-end close. This catches duplicates before they reach the general ledger.
In your workflow: implement a rule that invoices are checked against the existing supplier record before creating a new one.

For more on building clean data workflows, read Excel Data Analysis for Business Decisions - it covers the broader data hygiene framework that supports accurate reporting.

Frequently Asked Questions

How do I remove duplicates in Excel without deleting data I need?

Use Excel's Remove Duplicates feature under the Data tab but select only the columns that define uniqueness. Always copy your worksheet first and validate totals before and after to catch accidental removals.

What is the best Excel method for large datasets with over 50,000 rows?

Excel Power Query handles larger datasets more reliably than the standard Remove Duplicates button. It can process hundreds of thousands of rows without freezing and supports fuzzy matching for near-duplicate company names.

How do I find near-duplicate supplier names like 'ABC Electrical Pty Ltd' vs 'ABC Electrical'?

Use Excel Power Query's Fuzzy Grouping feature. Go to Power Query > Transform > Group By > enable 'Use fuzzy grouping.' This catches variations in company names, addresses, and text fields.

Can conditional formatting find duplicates before I delete them?

Yes. Select your range, go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values. This highlights duplicates visually without removing anything, so you can inspect before acting.

What are the ATO risks of incorrect deduplication for BAS reporting?

Removing legitimate transactions can distort GST claims, PAYG withholding totals, and BAS lodgements. Always run control totals before and after deduplication and keep documented records of your deduplication logic.

Should I use Python or SQL instead of Excel for deduplication?

Stick with Excel for datasets under 100,000 rows. Python's Pandas or SQL are better for larger datasets (100K to 1M+ rows) and when you need repeatable automated pipelines.

The Bottom Line

Duplicate data in Excel is solvable. For the vast majority of Australian businesses, the three methods here - Remove Duplicates, Power Query, and Conditional Formatting - are all you need to keep your financial records accurate for BAS, GST, and management reporting.

Start with the decision flow above. Pick the method that matches your data size and complexity. Validate your results every time. And invest in prevention so the problem shrinks month by month.

Your finance team will recover hours of reconciliation time. Your BAS will be accurate. And you will sleep better knowing the ATO's data-matching algorithms have nothing to flag.

EXCELWHIZ