How to compare two excel files for duplicates

If other people can edit your workbook, you might open it and wonder "Who changed this? And what did they do?" Microsoft Spreadsheet Compare can answer these questions by finding these changes and highlighting them for you.

Important: Spreadsheet Compare is only available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise.

1. Open Spreadsheet Compare.

2. In the lower-left pane, choose the options you want included in the workbook comparison, such as formulas, cell formatting, or macros. Or, just Select All.

3. On the Home tab, choose Compare Files.

4. In the Compare Files dialog box, in the Compare row, browse

   to the earlier version of your workbook. In addition to files saved on your computer or on a network, you can enter a web address to a site where your workbooks are saved.

5. In the Compare Files dialog box, in the To row, browse

   to the version you want to compare that earlier version against.

   Note: You can compare two files with the same name if they're saved in different folders.

6. Choose OK to run the comparison.

The results of the comparison appear in a two-pane grid. The workbook on the left corresponds to the "Compare" file you chose and the workbook on the right corresponds to the "To" file. Details appear in a pane below the two grids. Changes are highlighted by color, depending on the kind of change.

• In the side-by-side grid, a worksheet for each file is compared to the worksheet in the other file, beginning with the leftmost worksheet in each. If a worksheet is hidden in a workbook, it's still shown and compared in Spreadsheet Compare.

• If the cells are too narrow to show the cell contents, click Resize Cells to Fit.
  

• Differences are highlighted with a cell fill color or text font color, depending on the type of difference. For example, cells with "entered values" (non-formula cells) are formatted with a green fill color in the side-by-side grid, and with a green font in the pane results list. The lower-left pane is a legend that shows what the colors mean.

If you want to save your results or analyze them elsewhere, you can export them to an Excel file or copy and paste them into in another program, such as Microsoft Word. There's also an option for getting a high-fidelity look at each worksheet that shows the cell formatting, close to what you'd see it in Excel.

• You can export the results to an easier to read Excel file. Click Home > Export Results.

• Click Home > Copy Results to Clipboard to copy and paste the results to another program.

• To show cell formatting from the workbook, click Home > Show Workbook Colors.

• Say your organization is due to be audited. You need a trail for your critical workbooks that shows the changes month over month or year over year. This trail can help you find and correct mistakes before the auditors find them.

• In addition to comparing contents of worksheets, you can use Spreadsheet Compare to check for differences in VBA (Visual Basic for Applications) code. The results are returned in a window in which you can see the differences side by side.

Let's say you have two Excel workbooks, or maybe two versions of the same workbook, that you want to compare. Or maybe you want to find potential problems, like manually-entered (instead of calculated) totals, or broken formulas. You can use Microsoft Spreadsheet Compare to run a report on the differences and problems it finds.

Important: Spreadsheet Compare is only available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise.

On the Start screen, click Spreadsheet Compare. If you do not see a Spreadsheet Compare option, begin typing the words Spreadsheet Compare, and then select its option.

In addition to Spreadsheet Compare, you'll also find the companion program for Access – Microsoft Database Compare. It also requires Office Professional Plus versions or Microsoft 365 Apps for enterprise.

1. Click Home > Compare Files.

   The Compare Files dialog box appears.

2. Click the blue folder icon next to the Compare box to browse to the location of the earlier version of your workbook. In addition to files saved on your computer or on a network, you can enter a web address to a site where your workbooks are saved.

3. Click the green folder icon next to the To box to browse to the location of the workbook that you want to compare to the earlier version, and then click OK.

   Tip: You can compare two files with the same name if they're saved in different folders.

4. In the left pane, choose the options you want to see in the results of the workbook comparison by checking or unchecking the options, such as Formulas, Macros, or Cell Format. Or, just Select All.

5. Click OK to run the comparison.

If you get an "Unable to open workbook" message, this might mean one of the workbooks is password protected. Click OK and then enter the workbook's password. Learn more about how passwords and Spreadsheet Compare work together.

The results of the comparison appear in a two-pane grid. The workbook on the left corresponds to the "Compare" (typically older) file you chose and the workbook on the right corresponds to the "To" (typically newer) file. Details appear in a pane below the two grids. Changes are highlighted by color, depending on the kind of change.

• In the side-by-side grid, a worksheet for each file is compared to the worksheet in the other file. If there are multiple worksheets, they're available by clicking the forward and back buttons on the horizontal scroll bar.

  Note: Even if a worksheet is hidden, it's still compared and shown in the results.

• Differences are highlighted with a cell fill color or text font color, depending on the type of difference. For example, cells with "entered values" (non-formula cells) are formatted with a green fill color in the side-by-side grid, and with a green font in the pane results list. The lower-left pane is a legend that shows what the colors mean.

In the example shown here, results for Q4 in the earlier version weren't final. The latest version of the workbook contains the final numbers in the E column for Q4.

In the comparison results, cells E2:E5 in both versions have a green fill that means an entered value has changed. Because those values changed, the calculated results in the YTD column also changed – cells F2:F4 and E6:F6 have a blue-green fill that means the calculated value changed.

The calculated result in cell F5 also changed, but the more important reason is that in the earlier version its formula was incorrect (it summed only B5:D5, omitting the value for Q4). When the workbook was updated, the formula in F5 was corrected so that it's now =SUM(B5:E5).

• If the cells are too narrow to show the cell contents, click Resize Cells to Fit.

In addition to the comparison features of Spreadsheet Compare, Excel 2013 has an Inquire add-in you can turn on that makes an "Inquire" tab available. From the Inquire tab, you can analyze a workbook, see relationships between cells, worksheets, and other workbooks, and clean excess formatting from a worksheet. If you have two workbooks open in Excel that you want to compare, you can run Spreadsheet Compare by using the Compare Files command.

If you don't see the Inquire tab in Excel, see Turn on the Inquire add-in. To learn more about the tools in the Inquire add-in, see What you can do with Spreadsheet Inquire.

If you have "mission critical" Excel workbooks or Access databases in your organization, consider installing Microsoft’s spreadsheet and database management tools. Microsoft Audit and Control Management Server provides powerful change management features for Excel and Access files, and is complemented by Microsoft Discovery and Risk Assessment Server, which provides inventory and analysis features, all aimed at helping you reduce the risk associated with using tools developed by end users in Excel and Access.

Also see Overview of Spreadsheet Compare.