How to compare multiple Excel files

Whether you want to review changes or consolidate data, there are several methods to compare multiple Excel files including:

1. Excel functions: VLOOKUP or INDEX and MATCH
2. Specialist tool: Draftable’s Bulk Compare feature

We provide step-by-step guides for each method, so you can choose the most effective option next time you need to compare multiple Excel files.

1. Excel functions: VLOOKUP or INDEX and MATCH

If you need to compare three or more Excel files, you can use a combination of VLOOKUP or INDEX-MATCH functions to cross-reference data from multiple sources.

Note: While this process may be sufficient for small datasets, it’s not ideal for large files as it’s time-consuming and prone to human error.

1. Open all the Excel workbooks you need to compare.

2. In a new workbook or one of the existing ones, create a summary sheet where you will perform the comparisons.

3. Write VLOOKUP or INDEX and MATCH functions to pull data from each workbook. For example:

• =IFERROR(VLOOKUP(A2, '[Workbook2.xlsx]Sheet1'!$A$2:$B$100, 2, FALSE), "Not Found")
• =IFERROR(INDEX('[Workbook2.xlsx]Sheet1'!$B$2:$B$100, MATCH(A2, '[Workbook2.xlsx]Sheet1'!$A$2:$A$100, 0)), "Not Found")

These formulas will look up the value in A2 in Workbook2.xlsx and return the corresponding value or "Not Found" if it doesn't exist. You can repeat for additional workbooks.

4. You can combine the results from multiple VLOOKUPs or INDEX and MATCH functions into a comprehensive comparison. Place the results from each function in adjacent columns to see the differences side by side.

5. To create a final comparison, you can use conditional formatting to highlight differences between the columns in your summary sheet. For example, you can use rules to highlight cells in one colour if they differ from the corresponding cell in another column, making it easier to find the differences.

• Select the range you want to compare, then go to Home > Conditional Formatting > New Rule. Choose “Use a formula to determine which cells to format.”
• Enter a formula to compare the cells (e.g., =A1<>Sheet2!A1). Set the format to highlight differences.

2. Specialist tool: Draftable’s Bulk Compare feature

Draftable’s Bulk Compare feature allows users to easily compare a single Excel file with multiple other Excel files, at the same time. It’s ideal when you’ve got multiple versions of an Excel spreadsheet and need to compare each of these versions against a single base document and find the differences fast.

If you need to see  differences in text, values, or  formatting, you can view the output as a side-by-side comparison or as Tracked Changes with Excel’s native Spreadsheet Compare function.  You can also see changes to formulas with the Tracked Changes output. Redline output for Excel comparisons is currently in development and will be available in 2025.

You can access the feature in Draftable Legal, the document comparison software built for lawyers, legal teams, and large organisations.

How to use Bulk Compare
‍

1. Load multiple Excel files

• In the Draftable Desktop launcher, load your ‘older’ document, then select multiple (up to hundreds) of ‘newer’ Excel files.
• You can upload documents from your local files or DMS including iManage, NetDocuments, SharePoint, Worldox and Epona 365.
• Select your preferred comparison type (side-by-side or Tracked Changes), then hit the Compare button.

2. Review your comparisons

A pop-up screen will open showing the progress of each file comparison. You can open, review and export each comparison independently or save all outputs in bulk with one action.

Side-by-side comparison type

Side-by-side comparisons will show changes using highlights instead of redline markup.

• See all changes in a dynamic Change List. Scroll through the Change List and click on any change to jump to its location.
• Tag changes or leave Notes on any Change Card.
• Share the Change List, along with any Tags and Notes with external parties via the Changes Report, or save/share your Excel comparison as a PDF.

Tracked Changes comparison type

Tracked Changes comparisons will open directly to Spreadsheet Compare.

• Excel files are shown in their windows inside the Spreadsheet Compare tool so you can easily navigate the changes made in both documents.
• The bottom section shows specific details of changes including a Change List. This is useful if you need to view formulas within the comparison, providing even more detail than the side-by-side comparison.
• Enable and disable what changes are shown, including values, formulas, formatting, and more.
• Export all the changes into a single report.
  ‍

Read more about how to use Bulk Compare in this detailed Knowledge Base article.

If you need to compare other types of documents, Bulk Compare also supports:

• Word documents
• PDFs
• Excel documents
• PowerPoint documents
• Text files
• Scanned PDF files (built-in OCR)
  ‍

Read more: The efficient way to compare multiple documents at once

Conclusion

Comparing multiple Excel files can range from manual checks to advanced automated solutions. The method you choose depends on your specific needs, dataset size, and available tools. For quick and small-scale comparisons, built-in Excel tools might suffice. For more complex or ongoing tasks, specialist tools like Draftable Legal offer a reliable solution to ensure data accuracy and efficiency in your workflows.

You can test Draftable Legal’s Bulk Compare feature with a free five-day trial.