Analyze existing solutions for excel file comparison and choose the best one
Compare Excel Files
This is very common case – you have two excel files, one of them was changed. You need to determine what was changed in this workbook. In the multiuser environment this happens regularly. For example, your colleague edited the model you are working on and changed some formulas and input cell values. What was changed by him? Of your partner sends you his price list every week and you need to identify which position has changed and which ones are in-actual anymore.
Merge (consolidate) Excel Worksheets into one
Another spreadsheet comparison task, you may have on the daily basis. From the first look merge tool is not an excel diff tool, they are doing different work. But worksheet consolidation is possible only after we determine differences between two excel sheets. So, this task worst to be included into this review.
Version Control for Excel Files
This topic can’t be ignored. If you are working in the team environment, where several people can change the same Excel File – you need to resolve merge conflict and effectively find what was changed by your colleague. So, when talking about comparing 2 excel files we should take excel git diff topic into account.
Let’s describe the ways you can use to excel worksheets for changes.
Compare Excel files by viewing them side by side
This is the simplest way. To compare excel worksheet in this way you need to do the following:
Open excel files you are going to compare
Go to the View tab and apply command Side by Side in the Window group
After this the worksheets you are going to compare are located side by side on your screen and you can check all cells for modifications. This method requires your time and attention to compare all cells visually. This method has single benefit – it is completely free, but it doesn’t worst our attention. It can be used ONLY with very small workbooks. And even in the small file you can lose minor modification made in the formula or in the VBA code. So, there is nothing to discuss.
Compare Excel sheets by using worksheet formula functions.
As a next step of the previous method – you can compare cells on two excel worksheets by using worksheet functions. Which functions should you use? IF, LOOKUP, etc. This is working way which is completely free, but it has significant limitation – it doesn’t work with reordered rows and with inserted rows and columns.
Just insert a new row into block of the existing rows and this method will fail.
So, if you are sure that corresponding cells are on the same places in your worksheets – you can use this method. It is working solution. But in the complex cases you need to use something else for comparing excel files for differences.
Compare cells with Visual Basic Macro in Excel
Let’s go ahead and use more Excel features and capabilities. You can write yourself or download in the Internet macro for comparing cells in two Excel sheets. Macros in Excel has many powerful features. Using VBA you can create complex application so, you can create even xl comparator using Excel Object Model and VBA Language features. But this is much more than just a macro. If we are talking about VBA macro – this is a good way to compare large excel files for differences. But complex comparison algorithm for two excel sheets is more than a VBA macro. This is near excel diff tool created with VBA.
So, our conclusion for this method – this is robust and working approach, but it is limited in comparison speed and list of features. And in many cases, it doesn’t give accurate results. Because excel comparison requires detection of the inserted and removed rows and columns. Complexity of these algorithms it much higher than many VBA macros for worksheet comparison, you can find on the Internet.
As an intermediate summary for the methods above we would like to say the following:
They are limited in speed and comparison accuracy
No Summary Report for the compared excel worksheets
Can’t be used as excel diff tool for the SVN and GIT environment
No ability to merge changes found between two compared excel files
We can make this list longer, but this is not a purpose of this article. The solutions above are working in some cases and they have one important advantage – they are completely FREE.
Compare sheets with excel file compare tool
The last method we want to discuss – using excel diff tool to compare excel sheets for differences. This method has one important point – there are no free excel comparators on the market. All tools you can find require license, which will cost you some money.
But let’s describe what you are getting if you are using utility to compare, and merge excel files.
Fast Processing
Accurate Results
Highlighted Difference Report
Comparison Report you can save or send by e-mail
Rich set of comparison options (accuracy, ignored characters, ignore calculated values, formulas, hidden data, etc.)
Commands to Merge cells and different rows and columns
Compare VBA Modules and Forms
Command line mode to integrate diff tool into SVN or GIT application
Complete list of features present in the excel diff tools you can find on this page:
Why the Excel file compare tool is more effective than macros and other ways of comparison? The answer is simple – macro was created by enthusiast, who spend a few hours on his code. We are polishing comparison algorithms very day and adding new features to the product. The amount of the work done is reflected in the results.
You can compare this with difference in the job done by man and job done by machine. Machine is more reliable, stable and has more power. Please, note that we are talking about routine work. In some fields machine will never replace the human.
Conclusion
Both methods give you results. And we don’t force you to download, and use excel file compare tools in all cases. If possible – download VBA macro from the Internet and solve your task for spreadsheet comparison.
But, if you need to powerful, fast, and accurate solution for spreadsheet comparison and merging tasks – excel diff tool is an ultimate solution.
The money you pay for the license backs to you in the time you are spending for spreadsheet comparison tasks and for errors you are getting by processing your spreadsheets manually.
Relatively our product xlCompare (excel file compare tool) we would like to say, that money you pay for the license is more than just a payment. This is good investment into your productivity, that allows you to concentrate on the primary tasks, while we are doing routing job for you!