How to identify and process duplicate data in Excel tables

Key points of this article

Excel only retains 15 significant digits, and the excess will become 0. Long numbers must be saved as text before comparison, otherwise different numbers may be mistakenly judged as duplicates.
Use auxiliary columns to count first and then filter, which is safer than deleting directly. You can check each row before deciding which row to keep.
Before deleting, be sure to back up the original table and confirm which columns are used to determine duplicates to avoid deleting records that are not identical.
Mixed use of numbers and text, spaces before and after, and inconsistencies in full-width and half-width values will all cause the same value to fail to match, so the format needs to be unified first.

When organizing customer lists, expense reports or inventory tables, a common trouble is that the same record appears twice, or a number that looks the same is not recognized by the system. Manual line-by-line comparison can still be done when there are dozens of lines, but it is easy to miss hundreds or thousands of lines. This article talks about how to find and deal with duplicate data in tables. It is suitable for people who use Excel to organize data on a daily basis.

This article also specifically addresses a situation that is easily overlooked: long numbers that exceed 15 digits, such as ID numbers and bank card numbers, are often not found to be duplicates in Excel, or different numbers are regarded as duplicates. The reasons and solutions will be explained clearly below.

How to identify and process duplicate data in tables? One sentence conclusion

It is a safe approach to first use auxiliary columns or conditional formatting to mark rows that are suspected to be duplicates, and then delete them after manual verification. Although it is faster to directly use the "Delete Duplicates" function, once the wrong judgment basis is selected, valid records will be deleted by mistake.

your situation	Recommended practices	reason
Just want a quick look at which values are repeated	Highlight duplicate values with conditional formatting	No data changes, intuitive, suitable for short tables
Need to count how many times each value appears	Use COUNTIF to count auxiliary columns	The results can be filtered and traced, making it easy to check item by item.
There is an ID number or bank card number in the table	First change the column to text format and then compare	Prevent numbers after 15 digits from being replaced by 0 by Excel
Data comes from PDF table	First convert the PDF into a table and check whether the numbers are recognized accurately.	OCR may misread the numbers, and the error will directly lead to misjudgment.
After confirming the duplicates, clean them in batches	After backup, use the delete duplicates function and specify the judgment column.	Batch operation is fast, but the columns based on must be confirmed first

Before you start: Confirm these points first

Back up the original table first. Deleting duplicate items is an irreversible operation, especially data that requires leaving traces such as reimbursement forms and orders. Save a copy, work on the copy, and roll back if something goes wrong.
Confirm definition of "duplicate". Does it count as a duplicate if the entire line is the same, or does it only look at the name and mobile phone number? If the judgment column is selected differently, the results will be much different. It is common for two people to have the same name, and looking at the name alone is usually not enough.
Check the format of long numeric columns. Excel only retains 15 significant digits for numbers, and the excess will be displayed as 0. If the ID number has changed to something like "110101199001010000", it means that the data has been lost during entry and cannot be retrieved by Excel. It needs to be re-imported from the original source.
Check for invisible differences. The same number, one with spaces and one without, or one with full-width numbers and one with half-width, will be regarded as different values during comparison.
Confirm whether the data source is a scanned copy. If the table comes from a scanned PDF, you can use the desktop client's PDF editor to perform OCR recognition and then export it to Excel. The recognition results need to be checked item by item, especially numbers. OCR may regard 3 as 8 and 1 as 7.

Steps to use auxiliary columns to mark duplicate data and clean it

Add a new column next to the original table, such as "Verification", which is specifically used to calculate the number of repetitions. Do not write formulas directly on the original data column.
If you want to compare long numbers, first format the cells in this column as text, and then re-enter or paste. Data that has lost bits needs to be returned to the original source and reacquired.
Enter a formula similar to "=COUNTIF(B:B,B2)" in the first row of the auxiliary column, replace column B with the column you want to compare, and then fill down. A result of 2 or above indicates that the value appears multiple times in the entire column.
Enable filtering on the auxiliary column and only display rows greater than 1. These are suspected duplicate records.
Check other fields of these lines one by one, such as date, amount, and remarks, to determine whether they are true duplicates or different people with the same name.
For confirmed duplicate lines, decide which one to keep. Usually, keep the one entered earlier or with complete information, and then delete the remaining rows.
After the cleaning is completed, delete the auxiliary column, or keep it and indicate its purpose to avoid misuse next time.

If you confirm that all duplicates can be deleted in the same column, you can also use the software's built-in deduplication function, but you must check the correct judgment column in the dialog box and keep a backup.

📊 Core comparison

method	what can be done	Main limitations
Highlight conditional formatting	You can instantly spot the position of duplicate values	Cannot count the number of times; when the data volume is large, too many colors make it hard to distinguish
COUNTIF auxiliary column	Count counts, which can be filtered and verified	You need to manually fill in the formula, and long numbers must be converted to text first
Duplicate item removal feature	One-click deletion, fast speed	Cannot be revoked; if you judge that the listing is wrong, it will be mistakenly deleted

After completion, check each item one by one

Inspection items	How to see it	What should you do if there's a problem?
Whether the number of lines is reasonable	Compare the number of rows before and after deletion; the difference should equal the number of duplicates you have confirmed	If the row count difference is too large, restore with the backup table and reconfirm the judgment column
Whether the long number is complete	Check the length of the auxiliary column; the ID number should be 18 digits, and the last few digits are not consecutive zeros	If the last digit becomes 0, it means it is lost; return to the original source and re-import
Whether records of different people were accidentally deleted	Randomly check fields such as the name and date of the reserved and deleted rows	If accidentally deleted, retrieve it from the backup and add a checklist column
Have hidden differences been removed?	Use the TRIM function to handle spaces and check if full-width and half-width are consistent	After standardizing the format, recalculate the auxiliary column once
Whether the numbers are accurately recognized	If the data comes from a PDF, check the key figures item by item	If OCR recognition is incorrect, manual correction is performed before handling the duplicate

A reminder: if the table has been recognized by machine translation or OCR, key fields should be checked manually; don't rely solely on automatic results.

❓ Frequently Asked Questions (FAQ)

1. Why is it that even though the ID number is duplicated, Excel can't find it?

A common reason is that the format of two records is inconsistent—one is a number, the other is text—or one has a space. Set the entire column to text format, use TRIM to remove spaces, and then recalculate; usually, the match will match.

2. Why are two different ID numbers considered duplicate?

When an 18-digit number is stored as a number in Excel, the portion exceeding 15 digits is replaced with 0, and the first 15 digits of the same different numbers become identical. The solution is to convert to text before entering; data that has already been lost needs to be retrieved from the original source.

3. Which is more suitable, conditional formatting or COUNTIF?

If you just want to quickly see where there are duplicates, using conditional formatting is more intuitive. If you need to count counts, check each item, and keep processing records, use COUNTIF as an auxiliary column.

4. Can duplicate data be restored after deletion?

Excel deletion of duplicates generally cannot be undone, and closing files makes them harder to retrieve. Therefore, always save a backup copy before proceeding, and only overwrite the original file after confirming the result is correct.

5. The table is a scanned PDF format. How do you get the numbers accurate first?

You can first use the desktop client's PDF editor for OCR recognition, then export to Excel. After exporting, focus on checking the number columns, because OCR tends to make errors with similar fonts. After manual confirmation, repeat the judgment.

📝 Summary

Handling duplicate data in tables can be divided into three steps: back up first, then mark with auxiliary columns, and finally manually check and clean up. Long numbers must first be set to text format; otherwise, digits after 15 digits will be lost, and repeated checks will cause errors. Following this process not only allows you to find all duplicate records but also avoids accidental deletion.

Open OmniDoc and process your table file