When working with excel, many tasks require to merge two excel files or even to merge multiple excel files into a single sheet. For example, updating price lists, product inventories or customer databases. But for many Excel users, this task can be quite challenging and very time-consuming. Good news is, that there are several excel comparison tools available which make it very easy to merge excel files.
Here are my favorite tools for merging Excel Files:
If a seminar you get some new business cards, you capture them in excel and you now want to add them to your main customer list. The probability is that some of these newly captured addresses might be already in your customer list. So, how to find the saved ones?
At another instance, you want to update your current price list and have received several excel files from your sales departments to update all the changes in your price list.
These are two classic examples where an excel comparison tool would be of great help. Unfortunately, many excel users still use a rather manual method by sorting, comparing and searching for fields to be merged or updated.
Merging excel files into one usually requires two steps. Initially, two workbooks or worksheets need to be selected and compared. As a result, the differences or redundant fields are shown. Once the differences are identified you will need to make decisions, whether to delete, update or merge a difference. I call this ‘the updating process’ which means basically to transfer differences from one worksheet to the other worksheet until the two sheets are completely merged.
To make this task easier, faster and more accurate, there are several tools available. While all excel compare tools will support you in the first step and show you the identified differences, only a few tools are capable to support the updating process. This step by step example shows, how easy and fast you can merge two excel files with the right tool at hand.
Select the workbooks and worksheets to be merged
As the first step, you will need to select two workbooks you would like to compare. Many of the available tools can compare multiple worksheets within a workbook. Some tools even allow to save repeating projects or to customize filter and setting options as a new default.
Set filters and options to improve results
To achieve the best results and a higher accuracy some of the tools provide you with a range of filter and setting options. While some tools have a very comprehensive range, others have a rather basic range or even no options at all. But the more customizable a task is, the more accurate the results are and the more meaningful the display of differences becomes. Another thing to look out for when choosing an excel compare tool is whether it is capable to deal with inserted columns and inserted rows which are the most challenging type of differences.
Validate the comparison summary
As soon as the worksheets are selected and the filter and setting options are selected, the comparison process can be started. As for most tools, the actual comparison should take only a few seconds until the differences are displayed. In this step, it is critical that a tool not only identifies all types of differences, such as cell differences, formula differences, inserted columns, format differences, etc. but also that the identification has the highest possible accuracy.
This can be tricky since there is an infinite number of special cases and exceptions. Also critical is the way the differences are displayed since you will need to make decisions, which individual difference or difference group should be merged. The better the display of differences is, the easier and faster the updating process can be done.
Update and merge excel files into one
With all the differences identified, the first step of the task to merge two excel files is completed. And with this step, most of the excel compare tools end. Currently, there are only two tools available which support the user through the actual updating process. However, since updating or merging is the final goal, this step is critical and, naturally, having a tool at hand that supports this step is of great help.
A good tool will provide you with flexible options for each difference type regarding the updating options; to delete a difference or to transfer a difference from the target sheet to the source sheet or from the source sheet to the target sheet. But in the case when there are many differences to be merged, the function to merge entire difference types at once will save a lot of time. Once all the differences are merged, the final workbook can be saved, and you are good to go.
How to find out which tool is right for you
As you can see, having a good excel comparison tool at hand will save you a lot of time. To choose the right excel compare tool that fits your needs is in fact quite easy since most of the tools offer a free trial period of up to one month. The prices range from free to USD 100 as a onetime fee. The main question really is where and for what you will need the comparison. Is it for small and simple excel files at home or do you require a more professional and sophisticated solution at your workplace?
If the excel files are small enough to be merged manually, you will be good with basically all the available tools. However, as soon as the files are getting larger, which is mostly the case in business, a more sophisticated solution, that supports the updating process is strongly recommended.
So, was the article helpful? Do let us know in the comments section below!
Leave a Reply