Using the Microsoft Spreadsheet Compare Tool to Find Differences in Spreadsheets

You could run into a situation where you have two copies of the same file if you work with a lot of Microsoft Excel spreadsheets. If it’s a big file with a lot of details, this can be a problem because it would be hard to see the discrepancies between the two spreadsheets so that you can find out which one you want to use or keep.

Fortunately, with its Office suite of tools that you can use to compare your Excel spreadsheets, Microsoft has considered this issue and included a solution, so you don’t have to try to work out the differences between them yourself.

These two spreadsheets, as you can see below, look very similar and may even look identical at first glance. In several of the cells, they also contain a decent number of formulas that make them much harder to compare with each other.

You will first need to open it in your Microsoft Office Tools program category to use the Compare Spreadsheet feature. You’ll then have to press the Compare Files button and navigate to the position of each of your spreadsheets. To adjust the order of the files as to which is the new file and which is the older file, you can press the Swap button.

After you press the OK button, the files in the tool will be opened and ready for comparison.

The top two panes show the cells with the colored cell values from each of your spreadsheets to match the categories\options displayed in the tool’s lower-left pane. You may use the tool to compare your files using the categories below.

  • Entered values
  • Calculated values
  • Formulas
  • SysGen formulas
  • SysGen formulas errors
  • Structural
  • Names
  • SysGen names
  • SysGen error
  • Macros
  • Data connection
  • Cell format
  • Cell protection
  • Sheet/workbook protection

To adjust which outcomes are displayed, you can check or uncheck any categories you want to use or don’t want to use. If you have a large amount of data, you may want to do one class at a time so that it is easier to see the differences.

The cells that the result refers to will be highlighted in the sheet when you click on a result, so you can see where the difference is inside your file.

The bottom-right pane displays the styles and numbers of discrepancies between your two spreadsheets in a bar map. To give you an idea of what’s going on between your two files, this is more of an information field.

In order to give you a clearer understanding of how many and what kinds of formulas you are using in your workbooks, the Display Formulas button will show the formulas used in your spreadsheets inside the cells.

The Display Workbook Formatting button will adjust the view of the sheets to be closer to the actual look of your Excel file.

If you want to save the results of your comparison report, you may press the Export Results button to save the changes to an Excel spreadsheet that you can use later to analyze the changes or send them to anyone who might need them. This works better than the Copy Results to Clipboard option, since it will be very difficult to read if you use it and paste the results into something like Notepad or Phrase.

You will find it easier to use once you start using the Spreadsheet Comparison feature, and you will also find yourself able to notice the variations between your files more quickly.

Leave a Reply

Your email address will not be published. Required fields are marked *