What You Can Do with Excel Spreadsheet Compare

What You Can Do with Excel Spreadsheet Compare
Page content

If you are a frequent user of Excel, you may have wanted the ability to compare two spreadsheets. Unfortunately, doing so was a manual task often requiring you to copy and paste parts of one sheet into another to compare them side by side. Enough of that!

Getting Started

Not only will the Spreadsheet Compare tool show you the differences of values in your spreadsheets, it can also show you structural differences such as which fields are manually entered versus created by a calculation.

To get started, I’ve whipped up two simple spreadsheets with some basic inventory data (Figure 1). You’ll notice a few differences in the data. For the Extended Price column I manually entered two values in the second spreadsheet. Let’s see how the Spreadsheet Compare tool can help.

Using the Spreadsheet Compare Tool

Figure 2 Spreadsheet Compare

You can access the Spreadsheet Compare tool by selecting All Programs from the Start menu, choosing Microsoft Office 2013, clicking Office 2013 Tools and selecting Spreadsheet Compare. Keep in mind this tool is only available in the Office 2013 Professional Plus edition.

The tool window contains five main sections (Figure 2).

  1. This section is the main menu tab. Open and manipulate your open files here.
  2. This section displays your two spreadsheets. File 1 will be on the left and File 2 will appear on the right.
  3. This section allows you to choose which types of differences you wish to see highlighted.
  4. This section shows the list of differences between the two spreadsheets.
  5. This small chart presents a graphical count of the number of differences.

Figure 3 Results

Let’s open up our two spreadsheets to compare them.

  1. Click the Compare Files button.
  2. Browse to the files you wish to compare. Click the Swap button if you want to flip flop the two files in the display. Click OK.
  3. The comparison tool will compare the two spreadsheets and you will be presented with the results (Figure 3).

Interpreting the Results

Figure 4 Filters

If we leave the filter options as default we’re going to see a number of differences between the two sample sheets. As you can see by the color coding, each type of difference will be colored differently. Green values were manually entered, calculated values are blue and formulas are purple.

If we only want to see differences between entered values, we can deselect the filters for the other options (Figure 4).

The results list section will specify the exact differences between the two sheets. As we can see, the details are listed in easy to read form (Figure 5). For example, the compare tool noticed that one of my formulas was different between the two sheets while in two rows I had actually deleted the formula.

Figure 5 Results List

You can export the results to a new spreadsheet for or copy the results to a clipboard using the corresponding buttons in the main menu.

Note that the Spreadsheet Compare tool is read-only. It does not let you modify the content of your spreadsheets, so be sure to save the results to a new file if you want to track the differences.

That’s all there is to the Spreadsheet compare tool. It’s easy to use, but for frequent users of Spreadsheets this tool could be a huge timesaver – especially for comparing huge spreadsheets.