Compare Excel Sheets Side by Side: Easy Guide

Comparing Excel sheets can be a daunting task, especially when you need to analyze large datasets for discrepancies or consistency. Whether you're a financial analyst, a data manager, or simply someone looking to reconcile two reports, learning how to effectively compare Excel sheets side by side can dramatically enhance your productivity and accuracy. This guide will walk you through several methods to compare Excel sheets, ensuring you can spot differences, errors, or duplicate entries with ease.

Manual Comparison

The most straightforward approach to comparing Excel sheets is by performing a manual comparison. Here’s how you can do it:

• Open both Excel files you want to compare.
• Position the files side by side by dragging their windows until they snap to half of the screen each.
• Scroll through both sheets simultaneously to manually check for differences.

❗ Note: This method works best with smaller datasets. For larger datasets, consider using other methods described below for efficiency and accuracy.

Using Conditional Formatting

Conditional formatting allows you to visually highlight cells based on certain criteria, which can be extremely useful for spotting differences:

• Open both sheets in the same workbook or different workbooks in the same instance of Excel.
• Select the first sheet you want to compare.
• Go to the Home tab and click on Conditional Formatting.
• Choose New Rule, then select Use a formula to determine which cells to format.
• Enter the formula =A1<>Sheet2!A1 (adjust sheet and cell references as needed).
• Set the format you want to apply when cells differ, like a bright color or pattern.
• Repeat for the second sheet if necessary.

Excel’s Inbuilt Compare Feature

If you’re using Excel for Microsoft 365, Excel 2016, or later versions, you have access to the built-in compare feature:

• Open both sheets.
• Go to File > Compare > Compare Documents.
• Select the second workbook or sheet for comparison.
• Excel will open a new workbook showing the differences side by side with changes highlighted.

💡 Note: The inbuilt compare feature does not work for different instances of Excel running simultaneously; all sheets must be within the same Excel instance.

Third-Party Add-ins and Tools

For more complex or detailed comparisons, consider using third-party add-ins or tools:

• Spreadsheet Compare: From Microsoft, designed to work with Excel for finding and analyzing differences.
• WinDiff: A free tool from Microsoft, useful for comparing binary files but can be used for Excel sheets too.
• Kutools for Excel: Provides several comparison functionalities beyond Excel's default capabilities.

👁️ Note: Be cautious when using third-party tools as they might not always be compatible with the latest Excel versions or could contain outdated features.

VBA (Visual Basic for Applications) Macros

If you’re comfortable with coding, using VBA macros can automate the comparison process:

• Open the Excel Visual Basic Editor by pressing Alt + F11.
• In the new window, insert a new module.
• Paste the following macro code:

      Sub CompareSheets()
          Dim ws1 As Worksheet, ws2 As Worksheet
          Set ws1 = Worksheets("Sheet1")
          Set ws2 = Worksheets("Sheet2")
          
          Dim cel As Range, lastRow As Long
          
          lastRow = Application.Max(ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row, ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row)
          
          For Each cel In ws1.Range("A1").Resize(lastRow, ws1.UsedRange.Columns.Count)
              If cel.Value <> ws2.Range(cel.Address).Value Then
                  cel.Interior.Color = RGB(255, 0, 0) ' Highlight different cells in red
              End If
          Next cel
      End Sub
  

• Run the macro to highlight differences between the selected sheets.

🛠️ Note: This code will only compare cells that have the same addresses in both sheets. Adjust the code if your sheets do not align perfectly.

Comparing Formula Results

It’s not just the data you need to compare; sometimes, formulas can return different results due to different settings or updates:

• Use the FORMULATEXT function to display formulas as text in cells for comparison.
• Check calculation options (under File > Options > Formulas) to ensure both sheets are using the same settings.
• Check the workbook calculation mode to ensure consistency in formula calculation.

⚠️ Note: Ensure you're comparing cells with formulas and not just the end result to avoid confusion from formatting or calculation differences.

In summary, comparing Excel sheets side by side can be approached in multiple ways, depending on the complexity of the data, your familiarity with Excel, and your software setup. Manual comparison suits small datasets, whereas conditional formatting and Excel's inbuilt compare tool work well for visual differences in larger sets. For automation or more detailed analysis, using VBA macros or third-party tools can significantly reduce the time spent comparing. Remember that comparing the structural integrity of your data, like formulas and calculation settings, is just as important as the data itself. These methods and tools give you the flexibility to choose the best approach for your needs, ensuring that you can quickly and accurately identify and reconcile differences.

Can I compare Excel sheets if they have different numbers of rows or columns?

+

Yes, you can compare Excel sheets with different sizes. Tools like conditional formatting or VBA macros can highlight differences, but you need to manually check for structural changes or use advanced third-party tools for that purpose.

Is it possible to compare two Excel files on different computers?

+

Not directly. Both files must be opened in the same instance of Excel on the same computer, or you can manually compare by exchanging the files through a shared drive or cloud service like Dropbox or OneDrive.

What if I need to compare more than two sheets?

+

Compare one set of sheets at a time. For multiple sheets, you might need to use a combination of methods, or consider third-party tools that allow for multi-sheet comparison.