Comparing two Excel sheets can mean several different things. You may want to know whether the same cells changed, whether one list contains records missing from the other, or whether two versions of a report contain different values. The best method depends on the question you are trying to answer.
For a small worksheet, a simple side-by-side check may be enough. For larger datasets, formulas, conditional formatting or Power Query can make the comparison faster and more reliable.
Choose the comparison method by the question
- Same position, different value: use a cell-by-cell formula or conditional formatting.
- Same records, different order: use a lookup-based method.
- Added or missing rows: use XLOOKUP, COUNTIF/COUNTIFS or Power Query.
- Repeat monthly comparison: build a Power Query workflow.
Compare two sheets side by side
For small sheets, opening both worksheets and placing them side by side can reveal obvious changes. This works when row and column positions line up and the number of records is manageable.
It becomes unreliable when rows have been inserted, sorted or filtered differently. That is when a rule-based comparison is better.
Method 1: Compare matching cells with a formula
When the two sheets have the same layout, a simple comparison formula can return whether two cells match. For example, =A2=Sheet2!A2 returns TRUE when the two cells are equal.
You can extend the formula down and across the range. A more useful version can return a label such as “Match” or “Different” so the result is easier to filter.
Highlight differences with Conditional Formatting
Conditional Formatting can make changed values visible without creating a separate report. This works well when the sheets have matching row and column structure.
Use it as a visual aid, not as the only validation method for important data.
When row order is different
If one sheet is sorted by customer name and the other by customer ID, comparing A2 with A2 is meaningless. You first need to match the records using a stable key such as an ID.
Use XLOOKUP to match records
In supported Excel versions, XLOOKUP can find a corresponding record from the other sheet. You can then compare the returned value with the current sheet's value.
This approach is especially useful for customer lists, inventory, employee records and recurring reports where a unique identifier exists.
Use COUNTIF or COUNTIFS for presence checks
If the main question is whether an ID exists in both sheets, COUNTIF or COUNTIFS can be simpler than retrieving the entire record. A result of zero means the value was not found in the target range.
Find added and missing rows
Create a presence check on each sheet. Records found in Sheet A but not Sheet B are additions or missing records, depending on which version you treat as the baseline.
Do the reverse check as well. Running only one direction can miss records that exist in B but not A.
Compare more than one column
A record may match on ID but differ in amount, status or date. Once the key is matched, compare the fields that matter to the business question.
This creates a better result than simply reporting that a customer exists in both sheets.
Method 2: Create a difference flag
For a reconciliation sheet, create columns such as Record ID, Status, Old Value, New Value and Result. This makes the differences easier to review and communicate.
Method 3: Power Query for repeated comparisons
If you compare the same type of export every month, Power Query can merge datasets on a key and expose records that exist on one side or contain changed values.
The advantage is repeatability. Once the transformation is configured, a new pair of source files can be loaded into the same comparison process.
Compare values versus formulas
Two cells can display the same number while using different formulas. Decide whether you are comparing the displayed result or the underlying formula. This matters when auditing workbook logic rather than business values.
Handle blanks and errors carefully
Blank cells, zeros and error values can have different meanings. A blank in one sheet may indicate missing data, while a zero may be a real value. Define how your comparison should treat each case.
How to create a difference report
- Identify the stable matching key.
- Match records across the sheets.
- Compare the fields that matter.
- Flag missing, added and changed records.
- Filter to differences.
- Review the results before making changes.
Common mistakes
- Comparing rows by position when order changed.
- Using a non-unique field as the matching key.
- Checking only one direction for missing records.
- Ignoring blanks, errors or number formats.
- Assuming equal displayed values mean equal formulas.
Validate the result
Check the record count in both sheets and compare a few known differences manually. For important financial or operational reconciliations, verify totals independently before treating the result as final.
When manual comparison is enough
For a short two-column list with identical ordering, a visual check or simple formula may be faster than building a query. Use automation when data volume or repeat frequency makes manual checking unreliable.
Keep a comparison baseline
Save the two original sheets before changing either one. A comparison report is easier to trust when someone can trace it back to the source files.
Use Tervilo for related spreadsheet work
A comparison workflow often sits beside other Excel cleanup and document tasks. Tervilo can handle focused calculations or document preparation without replacing the spreadsheet process itself.
Compare the right fields
Do not compare every column automatically. Start with the business question. A reconciliation may need only ID and amount; a record audit may also require status, date and owner. Fewer, meaningful fields make the difference report easier to review.
Define which sheet is the baseline
Before reporting additions and removals, decide which workbook represents the earlier or authoritative state. Without that baseline, a record can be described as “new” or “missing” even though the labels simply reflect the direction of the comparison.
Check both directions
A comparison that only asks “what changed in Sheet B?” can miss records that exist in the new sheet but not the old one. Run the comparison in both directions when the business question includes additions and removals.
Do not overwrite the source sheets
Create a separate comparison or reconciliation sheet so the two source workbooks remain unchanged. This makes the result easier to audit and rerun.
Use stable identifiers whenever possible
Names and descriptions can change, but a stable customer, product or invoice ID is usually a better matching key. If no reliable identifier exists, use a combination of fields and review ambiguous matches manually.
Record the reason for a difference
A useful reconciliation report should distinguish changed values, missing records and genuinely new records. This makes the result actionable instead of leaving someone to inspect every row again.
Avoid changing data during the comparison
Run the comparison against copies of the two source sheets. Once the differences are understood, make the changes in the appropriate source or master workbook. Mixing comparison and correction in the same pass makes it harder to tell what changed and why.
Final recommendation
Start by defining what “difference” means for your data. Use direct cell comparison when the layout matches, lookup-based comparison when row order differs, and Power Query for recurring reconciliation. Always validate key counts and totals before acting on the results.
FAQ
Can Excel compare two sheets automatically?
Yes. Depending on the situation, formulas, Conditional Formatting, workbook comparison features or Power Query can help.
What if the rows are in a different order?
Match records using a stable key such as an ID instead of comparing the same row numbers.
How do I find rows that exist in one sheet but not another?
Use XLOOKUP, COUNTIF/COUNTIFS or a Power Query merge to check whether the key exists in the other dataset.
What is the best method for monthly reports?
A repeatable Power Query comparison can reduce manual work and make the logic consistent.
Sources checked
Formula availability varies by Excel version. Verify current Microsoft documentation for your environment.