Menu
Select both columns of data that you want to compare. On the Home tab, in the Styles grouping, under the Conditional Formatting drop down choose Highlight Cells Rules, then Duplicate Values. On the Duplicate Values dialog box select the colors you want and click OK. Notice Unique is also a choice.
We faced the exact same issue in our co. Our tests output excel workbooks. Binary diff was not an option. So we rolled out our own simple command line tool. Check out the ExcelCompare project. Infact this allows us to automate our tests quite nicely. Patches / Feature requests quite welcome!
Quick and easy with no external tools, works well as long as the two sheets you are comparing are similar:
=if(Sheet1!A1 <> Sheet2!A1, "X", "") in the top left cell (or equivalent: click on the actual cells to automatically have the references inserted into the formula)If the sheets are similar, this spreadsheet will be empty except for a few cells with X in them, highlighting the differences. Unzoom to 40% to quickly see what is different.
I've done a lot of comparing of Excel workbooks in the past. My technique works very well for workbooks with many worksheets, but it only compares cell contents, not cell formatting, macros, etc. Also, there's some coding involved but it's well worth it if you have to compare a lot of large files repeatedly. Here's how it works:
A) Write a simple dump program that steps through all worksheets and saves all data to tab-separated files. Create one file per worksheet (use the worksheet name as the filename, e.g. "MyWorksheet.tsv"), and create a new folder for these files each time you run the program. Name the folder after the excel filename and add a timestamp, e.g. "20080922-065412-MyExcelFile". I did this in Java using a library called JExcelAPI. It's really quite easy.
B) Add a Windows shell extension to run your new Java program from step A when right-clicking on an Excel file. This makes it very easy to run this program. You need to Google how to do this, but it's as easy as writing a *.reg file.
C) Get BeyondCompare. It has a very cool feature to compare delimited data by showing it in a nice table, see screenshot.
D) You're now ready to compare Excel files with ease. Right-click on Excel file 1 and run your dump program. It will create a folder with one file per worksheet. Right-click on Excel file 2 and run your dump program. It will create a second folder with one file per worksheet. Now use BeyondCompare (BC) to compare the folders. Each file represents a worksheet, so if there are differences in a worksheet BC will show this and you can drill down and do a file comparison. BC will show the comparison in a nice table layout, and you can hide rows and columns you're not interested in.
You can try this free online tool - www.cloudyexcel.com/compare-excel/
It gives a good visual output online, in terms of rows added,deleted, changed etc.
Plus you donot have to install anything.
I have found xdocdiff WinMerge Plugin. It is a plugin for WinMerge (both OpenSource and Freeware, you doesn't need to write a VBA nor save an excel to csv or xml). It works just for the celd's contains.
This plugin supports also:
Regard, Andres
Hmmm. From the Excel menu choose Window -> Compare side by side?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With