How to compare two excel files
Last updated: April 1, 2026
Key Facts
- Excel's built-in comparison tool is available in the Data tab under "Queries & Connections" or "Compare Files" menu
- You can manually compare files by using filters, sorting, and conditional formatting to highlight differences
- Formulas like VLOOKUP, INDEX/MATCH, and COUNTIF can identify differences programmatically between sheets
- Third-party tools like WinMerge, DiffEngineX, and online comparison tools provide detailed visual reports
- Conditional formatting with formulas can automatically highlight cells that differ between two identical layouts
Methods to Compare Two Excel Files
Comparing two Excel files is a common task in business, accounting, and data analysis. Whether you need to find changes made to a spreadsheet, verify data accuracy, or identify discrepancies between versions, there are several effective methods available. The best method depends on your file size, complexity, and the specific differences you're looking for.
Excel's Built-in Comparison Features
Microsoft Excel provides native tools for comparing and merging files. In Excel, click the "File" menu and look for "Compare and Merge Workbooks" or similar option. This feature highlights changes made between versions and shows who made each change and when. This method works best when comparing different versions of the same file. Note that this feature may not be available in all Excel versions or may be located under different menu paths.
Manual Side-by-Side Comparison
For simpler comparisons, open both files and position them side-by-side on your screen using Windows (right-click and select "Snap Window"). Then manually review cells to identify differences. You can use Excel's "Find & Replace" feature to search for specific values and navigate between matching entries. While this method is time-consuming for large datasets, it's practical for smaller files or when you need to understand each change in detail.
Using Conditional Formatting and Formulas
Create a more efficient comparison by copying both datasets into separate sheets in the same workbook. Use VLOOKUP or INDEX/MATCH functions to find matching data and identify missing or different values. Apply conditional formatting with formulas like =A1<>Sheet2!A1 to automatically highlight cells that differ. This method allows you to see at a glance which cells have changed and is more scalable for larger datasets.
VLOOKUP Comparison Method
In a new column, use =VLOOKUP(A1,Sheet2!$A:$B,2,FALSE) to find corresponding values from the second file. Create another column with =IF(ISNA(VLOOKUP(...)),"Not Found",IF(B1=VLOOKUP(...),"Match","Different")) to identify whether values match, are missing, or differ. Filter the results to show only "Different" or "Not Found" entries, making discrepancies immediately visible.
Third-Party Comparison Tools
WinMerge is a free, open-source tool that provides detailed visual comparison of files, including Excel files. DiffEngineX specializes in Excel file comparison and highlights differences color-coded by type (insertions, deletions, modifications). Online tools like Diff Checker allow you to paste or upload files for quick comparison without installation. These tools often provide better visual representation and more detailed reports than manual methods.
Best Practices for Comparison
Before comparing, ensure both files have the same structure and column order, or sorting may produce incorrect results. Remove any temporary rows or columns that don't belong in the final comparison. Create a backup of your original files before making changes. Document all differences you find, especially if they are unexpected, as they may indicate data entry errors or system issues that need investigation.
Related Questions
How do I merge two Excel files together?
Use the "Consolidate" feature in Excel's Data tab to combine data from multiple sheets, or manually copy and paste data into a master file. For more complex merges, use VLOOKUP or INDEX/MATCH functions to combine data based on matching criteria.
How do I find duplicate values in Excel?
Use conditional formatting with "Highlight Cells Rules" > "Duplicate Values" to automatically highlight matching entries. Alternatively, use the Remove Duplicates feature in the Data tab, or create a COUNTIF formula to identify duplicates.
How do I use VLOOKUP in Excel?
Use the formula =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) to search for a value in the first column of a range and return a value from another column. This is commonly used to match data between different sheets or files.