Duplicate invoice numbers can make an otherwise clean invoice tracker difficult to trust. The same invoice ID may appear twice because a row was copied, imported more than once, or entered manually by mistake.
Google Sheets can help you spot repeated invoice numbers automatically with conditional formatting or COUNTIF. The important part is reviewing the matching rows before deleting anything, because the same invoice number does not always mean the entire row is a true duplicate.
Select your invoice-number column, open Format > Conditional formatting, choose Custom formula is, and use =COUNTIF($A$2:$A$1000,A2)>1. This highlights invoice numbers that appear more than once so you can review them before deciding what to remove.
Why Duplicate Invoice Numbers Are a Problem
Duplicate invoice IDs can create confusion when you track payments, overdue invoices, or customer balances.
For example, you may accidentally count the same invoice twice, mark the wrong row as paid, or think a customer owes more than they actually do.
Highlight Duplicate Invoice Numbers
Conditional formatting is a useful first step because it shows potential duplicates without deleting or changing any rows.
2. Open Format > Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Enter the formula below.
5. Choose a noticeable formatting style.
6. Click Done.
=COUNTIF($A$2:$A$1000,A2)>1
The rule checks how many times the current invoice number appears in the full invoice-number range. If it appears more than once, the cell is highlighted.
Highlight duplicates first instead of deleting them immediately. This gives you a chance to compare the customer, date, amount, and payment status before changing the tracker.
Highlight the Entire Duplicate Row
If you want the entire invoice row to stand out instead of only the invoice-number cell, apply the rule to the full table range.
For example, select A2:F1000 and use:
=COUNTIF($A$2:$A$1000,$A2)>1
The dollar sign keeps the rule focused on column A while allowing the formatting to apply across the rest of each matching row.
Count How Many Times Each Invoice Appears
If you want more detail than a colored highlight, add a temporary helper column called Duplicate Count.
If the invoice number is in A2, enter this formula in the helper column:
=COUNTIF($A$2:$A$1000,A2)
Filter the Duplicate Rows
Once the helper column shows the count, filter it to display only values greater than 1.
Now compare the rows that share an invoice number.
Customer: Is it the same customer?
Date: Are the invoice dates identical?
Amount: Are the totals the same?
Status: Is one row more up to date than the other?
The Same Invoice Number Does Not Always Mean a Duplicate
Imagine these two rows:
| Invoice # | Customer | Amount | Status |
|---|---|---|---|
| INV-1001 | Acme Co. | $250 | Paid |
| INV-1001 | Acme Co. | $150 | Overdue |
These rows share an invoice number, but the different amount and status suggest there may be a data-entry problem rather than a simple duplicated row.
Do not automatically delete every repeated invoice number. First determine whether the entire row is duplicated or whether the invoice ID itself needs correction.
Remove Confirmed Duplicate Rows
Once you confirm that rows are genuine duplicates, Google Sheets includes a built-in tool for removing repeated data.
2. Select the data range.
3. Open Data > Data cleanup > Remove duplicates.
4. Confirm whether the range has a header row.
5. Choose which columns should be used to identify duplicate rows.
6. Click Remove duplicates.
The columns you select are important. If you check only Invoice #, rows that share an invoice number may be treated as duplicates even when other details differ. For invoice records, it is often safer to review the rows manually before using the removal tool.
A Safer Workflow for Invoice Trackers
Use conditional formatting to identify potential problems without changing the data.
Use COUNTIF if you want to see whether an invoice appears two, three, or more times.
Check customer, date, amount, and status before deciding what is wrong.
Delete exact duplicate records or fix an incorrect invoice number when the rows represent different invoices.
Prevent Duplicate Invoice Numbers
Finding duplicates after they happen is useful, but keeping a warning rule in the spreadsheet can help you notice the problem sooner.
You can leave the conditional-formatting rule active so a repeated invoice number is highlighted as soon as it appears in the tracked range.
Keep the duplicate-highlighting rule active even after cleaning the tracker. It can act as a visual warning when a repeated invoice ID is entered later.
Related Google Sheets Guides
Frequently Asked Questions
Conditional formatting and COUNTIF make repeated invoice numbers easy to find, but the safest workflow is to compare the matching rows before deleting anything. Remove only confirmed duplicate records and correct the invoice number when the rows actually represent different invoices.
This guide is based on current Google Sheets Editors Help documentation for COUNTIF, conditional formatting with custom formulas, and Data cleanup > Remove duplicates. Last checked: October 2026.