If you have two similar Excel spreadsheets, or different versions of the same original spreadsheet, it can be useful to compare the Excel files and see how they differ. If the spreadsheets are short, you might be able to do this simply by looking at the two and taking notes on screen or on paper. But this can quickly become tedious and error-prone as the spreadsheets get bigger and more complex. Luckily, there's an Excel compare tool from Microsoft to make this task easier.
You can use Microsoft's Spreadsheet Compare tool to compare Excel spreadsheets and see how they're similar or different. You can also export the spreadsheets to the text-based comma separated value format and use text comparison tools to compare them.
Compare Excel Spreadsheets with Spreadsheet Compare
You can use Microsoft's Spreadsheet Compare tool to get a color-coded chart showing how two spreadsheets compare and what's missing or changed from one to the other.
Find this tool by typing Spreadsheet Compare in the Search box in the Windows Start Menu, then click its icon to launch the program. Once the program launches, check the boxes for the features that you want to compare between the two spreadsheets, including formulas, macros and cell formatting.
Then, click the Home tab and Compare Files. Click the Blue folder icon icon to choose one file for comparison and click the green icon to choose a second file. Click OK to compare the two files. You may get an error message and a prompt to enter a password if either or both of the files is password-protected. If so, enter the necessary passwords to proceed.
The program will generate a color-coded grid showing the changes and differences between the two files. Scroll through the grid to see the differences. If you want to export the set of differences to an Excel file, click the Home tab then Export Results.
To copy it to another program such as Microsoft Word or an email program via the Windows clipboard, click the Home tab then Copy Results to Clipboard. Paste the data into the other program using its paste feature.
Compare Text Versions of the Spreadsheets
If you don't have Spreadsheet Compare or aren't happy with the way it presents results, you may be able to compare text versions of the spreadsheets using a variety of other tools.
In Excel, click the File tab, then Save As to export a text version of a spreadsheet. You can use the file type drop-down menu to choose either a ".txt" file where spreadsheet columns are separated by tab characters or a ".csv" file where they're separated by commas. Note that you'll lose some formatting and formula information by using these formats, but you'll be able to compare the text values in the spreadsheets. Export both files you want to compare using the same settings.
Then, you can use a variety of tools to compare the data in the two files. Many text editing tools, such as Sublime Text and Vim, offer file comparison tools with a number of options. Commercial and open source tools like Beyond Compare and the command line tool diff can also be useful for this purpose.