OmniDocHow to compare different data in two tables in Excel
How to quickly find different records between two Excel sheets? This article uses COUNTIF formulas, auxiliary columns and conditional formatting to compare rows, list missing and changed data, and explain when it is not necessary to use a pivot table.
Key points of this article
- Unify the column names, formats and numbered columns before comparison, otherwise the formula will reference the wrong column, or the same data will be judged as different.
- When there is a stable number, use COUNTIF to search in two directions to list records that exist in only one table.
- When there is no number, concatenate multiple columns such as name and date into auxiliary keys, and then compare them row by row to avoid misjudgment of names with the same name.
- Conditional formatting is suitable for quick browsing, and pivot tables are suitable for viewing summary amounts. Neither can replace finding differences row by row.
If you have two Excel spreadsheets, such as last month's and this month's customer lists, or financial exported bills and records in the business system, you probably want to know: which rows have changed, and which rows only appear in one of them. Manually correcting each line can handle dozens of lines, but it is easy to miss hundreds of lines. It is not difficult to compare different data in Excel tables. The key is to first think clearly about what to use as the basis for comparison.
This article is for office staff, finance and operations who need to reconcile two sets of data. After reading, you will know how to use formulas to mark different records between two tables, how to use conditional formatting to highlight duplicate or missing content, and why most reconciliation scenarios do not require pivot tables.
Which method is suitable for comparing different data in Excel tables? One sentence conclusion
Let’s talk about the conclusion first: when the two tables have unique numbers, use a formula to search by number; when there are no numbers, first splice multiple columns into auxiliary columns and then compare; when you just want to scan them with the naked eye, use conditional formatting. Pivot tables are more suitable for summary statistics than for finding differences row by row.
| your situation | Recommended practices | reason |
|---|---|---|
| Both tables have numbers that can distinguish each row (such as order number, job number) | Find by number using COUNTIF | The results are intuitive and can be filtered and exported directly. |
| There is no number, so you have to rely on multiple columns such as name and date to judge. | Splice multiple columns into auxiliary columns and compare row by row | A single column may be repeated, and a combination of multiple columns can locate a record. |
| The order of the rows in the two tables is the same. I just want to see whether the values change. | Compare cells at the same location column by column | The formula is simple and suitable for tables with exactly the same structure. |
| Just want to quickly see duplicate or different cells | Highlight with conditional formatting | Without changing the data, the color can prompt, suitable for manual review |
| To summarize amount differences by category | PivotTable or Subtotal | Suitable for statistics, but does not list the specific rows that are different |
Before you start: Confirm these points first
- Whether the column names and formats of the two tables are consistent: The column names are different, and the formula will reference the wrong column; the date in one table is text and the other is in date format, which looks the same but does not match.
- Is there a reliable identification column: Numbers such as order number and job number are more suitable. If not, first confirm which columns can be combined to locate a row, otherwise records with the same name and the same date will be misjudged.
- Are there any hidden spaces and full-width differences in the data: The extra space is not visible to the naked eye, but the formula will judge it to be different. You can clean it first with the TRIM function.
- What is the purpose of the comparison: Is it to find missing records, to find changes in values, or to reconcile amounts? The purposes are different, and the methods are also different. Thinking it through first can save a lot of rework.
- Where to put the two data: If you use the UniCell table in the OmniDoc desktop client, you can put the two data side by side in the same file for comparison, and then process them according to the following ideas.
Use COUNTIF to find the different records in the two tables row by row
- Organize the two tables, confirm that the first row is the column name, and put the key columns (such as numbers) that serve as the basis in the same column of the respective tables. The column positions are unified, and the subsequent formulas are easier to write.
- Add a new column next to the key column in Table 1 with the title "Does it exist in Table 2" to store the judgment results.
- Enter the formula =IF(COUNTIF(Table 2!$A:$A,A2)>0,"both tables have","only table 1 has") in the second row of the new column. Where A2 is the key value of table 1, table 2!$A:$A is the key column of table 2, the $ symbol locks the search range, and will not be offset when copied downwards.
- Fill in the formula down to the last row of Table 1, and each row will get the judgment result.
- In turn, do the same operation next to table 2 to find the records that are "only in table 2". If you only do one direction, you will miss the new rows in Table 2.
- Filter the judgment result column, select the rows that are "Only in Table 1" or "Only in Table 2", and copy them to the new table. This is the list of differences between the two tables.
- If you still want to see whether the values of the joint records have changed, first sort and align the two tables by number, and then use a formula such as =B2=Table 2!B2 to compare column by column. TRUE means the same, FALSE means different.
Check each item after completion
After getting the list of differences, don't trust the results directly. Check the following items.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| number of difference rows | Subtract the number of rows in the two tables to see if the numbers of "only table 1 has" and "only table 2 have" match up. | Not right, check if there are duplicate numbers or the formula is not completely pulled down. |
| formula range | Click on a few formulas to confirm that they refer to the complete search column. | If the $ symbol is missing, the reference will be offset after pulling down, and the area will be re-locked. |
| Format differences | Randomly check the records "Only in Table 1" and manually search for the same number in Table 2 | If found, it means there is a space, half-width or date format problem. Use TRIM to clean it up and then compare it. |
| Repeat number | Highlight duplicate values using conditional formatting on key columns | Duplicate numbers will distort the COUNTIF results. You need to confirm which record is the correct one. |
| Conclusion review | Manually check important data such as amount, contract number, etc. | Formula comparison will be affected by data format, and key data still needs to be manually confirmed. |
📊 Core comparison
| method | what can be seen | limitations |
|---|---|---|
| Formula method (COUNTIF) | Which specific rows are missing or changed can be filtered and exported. | You need to understand the formulas. The more formulas there are, the greater the amount of calculations in the table will be. |
| conditional formatting | Duplicate or different cells are colored and visible at a glance | It is only a prompt, and the difference list cannot be directly exported. |
| pivot table | Summarize quantities and amounts by category, suitable for checking totals | Do not list the differences line by line. If the structures of the two tables are different, they must be sorted out first. |
❓ Frequently Asked Questions (FAQ)
1. The data order of the two tables is different. Can it still be compared?
able. When using numbers as the basis, the order of rows does not affect the results of COUNTIF. Only when you need to compare values column by column, you need to sort and align the two tables by number first.
2. Why are the formulas judged to be different even though they look the same?
Common reasons are extra spaces, different full-width and half-width values, or one date is text and the other is date format. Use TRIM to clean up the spaces, unify the format and then compare.
3. How to identify duplicates between two tables?
Duplicate items between the two tables are the records that COUNTIF determines to be "contained in both tables". For duplication within the same table, you can use COUNTIF to count the number of occurrences of each value. If the result is greater than 1, it is a duplicate.
4. The amount of data is huge. Is there any easier way?
Only keep key columns and columns required for comparison, and remove irrelevant data. The table will be lighter and less likely to be misread. Sorting by number first also facilitates quick manual review.
📝 Summary
To compare different data in Excel tables, the core is to first select the basis column, and then use COUNTIF to find the records that are "only in Table 1" and "only in Table 2". Conditional formatting is good for quick browsing, and pivot tables are good for looking at totals. After finishing, check with the number of difference lines and the spot check results, and you can use this difference list with confidence.
