Ways To Compare Files In Microsoft Excel
Comparing and Analyzing Data
Using Microsoft Excel often leads to challenges managing your data. This is hardly surprising, given the scale and depth of the application. Excel is one of the most versatile products that Microsoft has ever produced. Whether you use Excel to track sales, manage attendance or simply oversee your personal outgoings, there is likely to be a time when you will need to compare the data of two files.
If you are looking for a way to compare files in Microsoft Excel, you might choose from third-party software, free Excel tricks or the basic onscreen side-by-side comparison.
Whether you have a couple of similar files full of almost identical data, or two similar worksheets within a single Microsoft Excel file, the following options should suit all requirements for anyone looking to compare data in this way.
Three Options
There are four options for comparing files in Microsoft Excel – basic, premium and free, explained as follows:
First, the basic option – this involves opening up two spreadsheets and viewing the together using View > Arrange All (Excel 2007) option to view your spreadsheets tiled horizontally or vertically on your monitor. This option is of course only suitable for visual comparison.
Second, you can download a third party application. Available from www.office-excel.com/excel-addins, Compare Spreadsheets for Excel is designed to allow convenient comparison of Excel files and spreadsheets. Using this tool, you can choose tables or cell ranges, rows or columns and cell values or formulas; it is a powerful option, available for £39 (around $56) for single use with other options for organizations.
An alternative paid option is Excel Compare from www.formulasoft.com. This is available for $34.95 for personal use.
The third option is to use tools provided by Microsoft, such as a VBA script as a macro.
How to Compare Files in Microsoft Excel
Using a macro with VBA script, you can automate the comparison of files in Microsoft Excel. A range of scripts can be found online, such as the one at www.exceltip.com
However, my favourite option is to use tools already present in Excel, especially if using Excel 2007.
For instance if you comparing two sets of similar data in order to find duplicate elements, you can line up the two spreadsheet files, and use Data > Sort to sort by a specific column in each. Then browse through both files to find data that cannot be matched with another column.
An extension of this is to copy the contents of one of your Excel spreadsheets into the other, placing the new data next to the original data. After selecting the columns, use the Data > Remove Duplicates options. Excel will then scan through all data and get rid of anything that should not be there.
