How to compare two excel files

Overview

Comparing two Excel files is a common task for data analysts, accountants, and anyone working with spreadsheets. Whether you need to track changes, reconcile data, or identify discrepancies, Excel offers several methods to help. These methods range from simple visual checks and formula-based comparisons to using advanced built-in features or external tools.

Using Built-in Excel Features

Compare and Merge (Microsoft 365, Excel 2013/2016)

For users with newer versions of Excel, the 'Compare and Merge' functionality (often found under the 'Review' tab or accessible via add-ins) is designed specifically for this purpose. It allows you to compare a workbook with a previous version or another workbook and highlights any differences directly within the cells. This is particularly useful for tracking revisions in shared documents.

Worksheet Comparison Add-in

Older versions of Excel might not have the 'Compare and Merge' feature readily available. However, Microsoft offered an official 'Worksheet Compare' add-in that can be downloaded and installed. This add-in provides a dedicated interface to select two workbooks and generate a report detailing the differences.

Using Formulas for Comparison

If you don't have access to the latest Excel versions or prefer a formula-driven approach, several functions can help identify discrepancies:

VLOOKUP

VLOOKUP is excellent for checking if a value from one sheet exists in another. You can use it to search for values in a common column (e.g., an ID or name) from Sheet1 within Sheet2. If VLOOKUP returns an error (#N/A), it means the value from Sheet1 is not found in Sheet2. You can then extend this to compare other related data points.

Example: In Sheet1, assuming your lookup value is in A2 and you're searching in Sheet2's range A:B, the formula would be `=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)`. If this returns #N/A, the corresponding value in Sheet2 is missing or doesn't match.

MATCH

Similar to VLOOKUP, MATCH finds the position of a lookup value within a range. If MATCH returns an error, the value isn't present. It's often used in conjunction with INDEX for more flexible lookups.

Example: `=MATCH(A2, Sheet2!A:A, 0)` will return the row number where A2 is found in Sheet2's column A. An #N/A error indicates it's not found.

COUNTIF

COUNTIF can be used to count how many times a specific value appears in a range. If you want to see if a value from Sheet1 exists in Sheet2, you can use COUNTIF. A result of 0 means the value is not present.

Example: `=COUNTIF(Sheet2!A:A, A2)` will count occurrences of the value in A2 within Sheet2's column A. A result of 0 signifies absence.

Using Conditional Formatting

Conditional Formatting is a powerful visual tool. You can apply rules to highlight cells that are different between two ranges. This is particularly effective when comparing data side-by-side on the same sheet or across two adjacent sheets.

Steps:

1. Copy one of the datasets next to the other, ensuring the columns align correctly.
2. Select the range of cells in the first dataset you want to compare.
3. Go to the 'Home' tab, click 'Conditional Formatting', then 'New Rule'.
4. Choose 'Use a formula to determine which cells to format'.
5. Enter a formula that compares the selected cell with its corresponding cell in the other dataset. For example, if comparing A2 in Sheet1 with B2 in Sheet2, and both datasets start at row 2, you might select A2:A10 and use the formula `=A2<>Sheet2!B2`.
6. Click 'Format' and choose a fill color or font style to highlight differences.
7. Apply the same rule, adjusting the formula for the second dataset (e.g., `=B2<>Sheet1!A2`) to highlight differences from the other perspective.

Using Third-Party Tools

For complex comparisons or frequent use, dedicated third-party software offers advanced features:

• XLCompare: Provides detailed comparison reports, highlighting added, deleted, and modified rows/cells. It supports comparing multiple files and offers different comparison modes.
• DiffEngineX: A popular add-in for Excel that offers robust comparison capabilities, including detailed reports and the ability to compare formulas, formatting, and values.
• Beyond Compare: While a general file comparison tool, it can be configured to compare Excel files effectively, showing differences in a clear, side-by-side format.

These tools often provide more granular control and clearer reporting than built-in Excel functions, especially when dealing with large datasets or complex structures.

Best Practices for Comparison

• Ensure Data Consistency: Before comparing, make sure the data is clean and consistent. Remove duplicates, standardize formats, and ensure common keys (like IDs) are accurate.
• Understand Your Goal: Are you looking for exact matches, differences in specific columns, or overall structural changes? Tailor your comparison method accordingly.
• Start Simple: For minor differences, conditional formatting or simple formulas might suffice. For significant discrepancies or regular comparisons, invest time in learning more advanced features or tools.
• Backup Your Data: Always back up your Excel files before performing any comparison or reconciliation tasks to avoid accidental data loss.

By understanding these various methods, you can effectively compare two Excel files to ensure data accuracy and integrity.