Compare Worksheets in Excel: Techniques to Identify Differences

When working with Excel, you often encounter situations where you need to Compare Worksheets In Excel to identify discrepancies, track changes, or consolidate data. Whether you’re managing financial records, sales reports, or project timelines, knowing how to effectively compare Excel worksheets is crucial. This guide provides a comprehensive overview of various techniques to compare worksheets in Excel, ranging from built-in features to advanced third-party tools, ensuring you can choose the method that best suits your needs.

Let’s delve into the methods you can use to compare worksheets in Excel and pinpoint the differences effectively.

Side-by-Side Viewing: A Visual Approach to Worksheet Comparison

For a quick and straightforward comparison, especially with smaller datasets, viewing worksheets side by side offers a simple visual solution to compare worksheets in Excel. Excel’s “View Side by Side” mode allows you to arrange two worksheet windows adjacent to each other, facilitating a direct visual comparison.

Comparing Two Excel Workbooks Side by Side

Imagine you have monthly budget spreadsheets and want to quickly compare worksheets in Excel from two different months to spot variations in expenses or income. Here’s how to set up a side-by-side view:

Begin by opening both Excel workbooks that you intend to compare.
Navigate to the View tab on the Excel ribbon. In the Window group, locate and click the View Side by Side button.

Immediately, Excel will arrange the two open workbooks in a side-by-side configuration, typically horizontally by default.

For a vertical arrangement, which might be preferable for comparing columns of data, click the Arrange All button within the Window group on the View tab and select the Vertical option.

With the workbooks displayed side by side, consider enabling Synchronous Scrolling. This feature, also found under the View Side by Side button in the Window group of the View tab, links the scrolling of both windows. When activated, scrolling in one worksheet will automatically scroll the other, ensuring that you are always viewing corresponding rows simultaneously, perfect for a row-by-row compare worksheets in Excel.

Viewing Multiple Excel Windows Simultaneously

Beyond comparing just two workbooks, Excel allows you to arrange multiple workbooks side by side. To compare worksheets in Excel from several files at once:

Open all the Excel workbooks you wish to compare.
Click the View Side by Side button. Excel will present a Compare Side by Side dialog box.
In this dialog, select the additional workbooks you want to display alongside the active workbook for comparison.

To view all currently open Excel workbooks at once, utilize the Arrange All button on the View tab. This offers several arrangement options – tiled, horizontal, vertical, or cascade – allowing you to choose the layout that best facilitates your compare worksheets in Excel task.

Comparing Two Worksheets within the Same Workbook

Often, the worksheets you need to compare worksheets in Excel reside within the same workbook. Here’s how to view them side by side:

Open the Excel workbook containing the two worksheets. Go to the View tab, then to the Window group, and click New Window. This action opens a second window displaying the same workbook.
Activate View Side by Side mode by clicking the View Side by Side button.
In each of the two workbook windows, navigate to the respective worksheet you want to compare. For instance, select “Sheet1” in the first window and “Sheet2” in the second window.

This setup allows you to visually compare worksheets in Excel that are within the same file, treating them as if they were in separate workbooks for easier comparison.

Formula-Based Worksheet Comparison: Identifying Value Differences

For a more analytical approach to compare worksheets in Excel, especially when focusing on value discrepancies, Excel formulas provide a powerful method to generate difference reports. This technique is particularly useful for identifying cells where the values differ between two worksheets.

To implement a formula-based comparison:

Open your Excel workbook and insert a new, blank worksheet. This sheet will serve as your difference report.
In cell A1 of the new sheet, enter the following formula:

=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

Remember to replace “Sheet1” and “Sheet2” with the actual names of the worksheets you are comparing.
After entering the formula in A1, use the fill handle (the small square at the bottom-right of the selected cell) to drag the formula down and across to cover the entire range of data you need to compare in both worksheets. This action copies the formula to the other cells, automatically adjusting cell references due to the use of relative cell references.

This formula works by comparing the value of each corresponding cell in “Sheet1” and “Sheet2”. If the values are different, it will display a text string indicating the values from both sheets. If the values are identical, the cell in the difference report will remain blank.

It’s important to note that while this method effectively highlights value differences when you compare worksheets in Excel, it presents dates as serial numbers in the difference report, which may not be ideal for date comparisons. This is due to Excel’s internal storage of dates as numerical serial values.

Conditional Formatting for Highlighting Differences

Conditional formatting offers a visually striking method to compare worksheets in Excel and immediately highlight cells that contain different values. This technique uses color-coding to draw attention to discrepancies directly within one of the worksheets being compared.

Here’s how to use conditional formatting for worksheet comparison:

Select the worksheet where you want the differences to be highlighted. Choose the entire used range of cells. A quick way to do this is by clicking on the top-left cell of your data range (usually A1) and then pressing Ctrl + Shift + End to extend the selection to the last used cell.
Go to the Home tab on the Excel ribbon. In the Styles group, click on Conditional Formatting, and then select New Rule.
In the New Formatting Rule dialog box, choose “Use a formula to determine which cells to format”.
In the “Format values where this formula is true” field, enter the following formula:

=A1<>Sheet2!A1

Again, ensure “Sheet2” is replaced with the name of the other worksheet you are comparing against.
Click the Format button to specify how you want the different cells to be highlighted. Choose your preferred style, such as fill color, font style, or border.
Click OK in both the Format Cells and New Formatting Rule dialog boxes to apply the conditional formatting rule.

After setting up the rule, Excel will automatically highlight all cells in the selected worksheet that have different values compared to their corresponding cells in “Sheet2”. This visual cue makes it very easy to quickly compare worksheets in Excel and identify discrepancies.

However, it’s important to recognize the limitations of these formula and conditional formatting methods when you compare worksheets in Excel:

Value Comparison Only: They only identify differences in cell values. They do not compare formulas or cell formatting.
Row/Column Sensitivity: They cannot effectively handle added or deleted rows or columns. If the structure of the worksheets is not identical (e.g., one has an extra row), the comparison becomes misaligned from that point onwards, marking subsequent rows/columns incorrectly as different.
Worksheet-Level Limitations: These methods operate at the worksheet level and cannot detect structural differences at the workbook level, such as added, deleted, or renamed worksheets.

Compare and Merge Shared Workbooks: Collaborative Change Tracking

When collaboration is involved, and multiple users are working on different copies of the same Excel workbook, Excel’s Compare and Merge Workbooks feature becomes invaluable. This feature is specifically designed for scenarios where several individuals might be editing copies of a shared workbook, and you need to consolidate all changes into a single version when you compare worksheets in Excel versions.

Before using this feature, ensure the following preparation steps are taken:

Share the Workbook: The original workbook must be shared before copies are distributed for editing. To share, go to the Review tab, in the Changes group, click Share Workbook (in older versions of Excel, this might be under “Track Changes”). Check the box labeled “Allow changes by more than one user at the same time…” and click OK. If prompted to save the workbook, allow Excel to do so. Enabling “Track Changes” automatically shares the workbook.
Save Copies Uniquely: Each user editing the workbook must save their copy with a unique filename. It’s crucial that they are saving copies of the shared workbook.

With these steps completed, you can proceed to merge the copies:

Enabling the “Compare and Merge Workbooks” Command

The Compare and Merge Workbooks command is not readily visible in Excel’s ribbon by default. You’ll need to add it to your Quick Access Toolbar:

Click the dropdown arrow at the end of the Quick Access Toolbar and select More Commands.
In the Excel Options dialog box, choose All Commands from the “Choose commands from” dropdown menu.
Scroll down the list of commands and find Compare and Merge Workbooks. Select it and click the Add button to move it to the right-hand side, adding it to your Quick Access Toolbar.
Click OK to close the dialog box. The Compare and Merge Workbooks command icon will now be visible in your Quick Access Toolbar.

Comparing and Merging Workbooks

To merge the changes from different copies back into the original shared workbook:

Open the original, shared workbook.
Click the Compare and Merge Workbooks command from your Quick Access Toolbar.
In the dialog box that appears, locate and select the copies of the shared workbook that you want to merge. You can select multiple copies by holding down the Shift or Ctrl key while clicking on filenames. Click OK to proceed with the merge.

Excel will then merge the changes from the selected copies into the primary workbook.

Reviewing Merged Changes

After merging, you can review all the changes made by different users:

Go to the Review tab, Changes group, and click Track Changes, then select Highlight Changes.
In the Highlight Changes dialog, set the When dropdown to All, the Who dropdown to Everyone, clear the Where box, and ensure Highlight changes on screen is checked. Click OK.

Excel will highlight columns and rows with changes in dark red and use different colors to mark edits made by different users within cells. Hovering over a changed cell will display information about who made the change and when.

Important Note: The Compare and Merge Workbooks feature is specifically for merging copies of a shared workbook. It will not work if you are trying to merge completely different Excel files. If the command is greyed out, double-check that you are working with the original shared workbook and that the files you are trying to merge are indeed copies of this shared file.

Third-Party Tools for Advanced Worksheet Comparison

While Excel’s built-in features offer basic functionalities to compare worksheets in Excel, they may fall short when dealing with complex comparisons, especially when you need to identify differences beyond just values, such as formulas, formatting, or structural changes. For more advanced and efficient worksheet comparison and merging, consider using third-party tools specifically designed for Excel. These tools often provide more comprehensive features and greater accuracy.

Synkronizer Excel Compare: A Comprehensive Comparison, Merge, and Update Tool

Synkronizer Excel Compare is an Excel add-in designed to provide robust features for comparing, merging, and updating Excel files. It aims to eliminate the manual effort in identifying differences between Excel worksheets and workbooks.

Key features of Synkronizer Excel Compare include:

Detailed Difference Identification: Identifies differences in values, formulas, formatting, comments, and even named ranges.
Intelligent Merging: Allows selective merging of differences from one worksheet to another, preventing data loss and duplication.
Difference Highlighting and Reporting: Highlights differences directly in worksheets and generates detailed, easy-to-read difference reports.
Customizable Comparison: Offers various comparison options, including comparing as normal worksheets, with link options, as databases, or selected ranges.
Filtering Options: Allows filtering of comparison results to focus on relevant differences, such as ignoring case, spaces, or formula differences with the same result.

To use Synkronizer Excel Compare for compare worksheets in Excel:

Install the Synkronizer Excel Compare add-in.
Go to the Add-ins tab in Excel and click the Synkronizer icon to open the Synkronizer pane.
Select the two workbooks and specific worksheets you want to compare.
Choose your comparison options and content types to compare (e.g., values, formulas, formats).
Click the Start button to initiate the comparison.

Synkronizer then displays a summary and detailed reports of all differences found. You can navigate through these differences, view them highlighted in the worksheets, and selectively update or merge changes as needed.

Ablebits Compare Sheets for Excel: Intuitive and User-Friendly Comparison

Ablebits Compare Sheets for Excel is another powerful tool, part of the Ablebits Ultimate Suite for Excel. It focuses on making the worksheet comparison process intuitive and user-friendly through a step-by-step wizard and a unique “Review Differences” mode.

Key features include:

Wizard-Driven Interface: Guides you through the comparison process, simplifying setup and option selection.
Multiple Comparison Algorithms: Offers algorithms tailored for different data structures, including no key columns, by key columns, and cell-by-cell comparison.
Review Differences Mode: Displays compared worksheets side-by-side in a special mode that allows you to review and manage differences one by one.
Detailed Highlighting: Highlights rows and cells based on the type of difference, making it easy to distinguish between added rows, deleted rows, and changed cells.
Backup and Safety: Automatically creates backup copies of your worksheets before comparison, ensuring data safety.

To use Ablebits Compare Sheets for Excel to compare worksheets in Excel:

Install Ablebits Ultimate Suite and find the Compare Sheets tool under the Ablebits Data tab, in the Merge group.
Launch the Compare Sheets wizard.
Select the two worksheets you want to compare, and specify the comparison range if needed.
Choose a comparison algorithm that best suits your data structure.
Specify which types of differences to highlight and how to mark them.
Click Compare to start the process.

Alt text: Ablebits Compare Sheets wizard step to select the current table range for worksheet comparison, defining the data scope.

After comparison, the worksheets open in “Review Differences” mode, displaying differences highlighted with distinct colors. Toolbars next to each worksheet allow you to navigate through differences, merge changes, or ignore them.

Other Notable Third-Party Tools

xlCompare: (xlCompare) – A utility focused on comparing and merging workbooks, worksheets, and VBA projects. It offers features to find duplicate records, update and merge data, and filter comparison results.
Change pro for Excel: (Change pro for Excel) – Designed for desktop and mobile use, it compares formulas, values, and layout changes, including embedded objects. It’s capable of creating detailed difference reports and supports multiple languages.

Online Excel Comparison Services: Quick and Accessible

For users who need to compare worksheets in Excel occasionally and prefer not to install software, online Excel comparison services offer a convenient alternative. These web-based tools allow you to upload two Excel files and quickly identify differences directly in your browser.

While online services might raise security concerns for sensitive data, they can be a practical solution for non-confidential worksheets. Services like XLComparator and CloudyExcel are examples of online tools that provide basic worksheet comparison features.

Using online services typically involves:

Navigating to the website of the online Excel comparison service.
Uploading the two Excel files you want to compare.
Initiating the comparison process, usually by clicking a “Compare” or “Find Difference” button.

The service then processes the files and displays the differences, often highlighting them directly in a browser-based view of the worksheets.

Choosing the Right Method for Worksheet Comparison

Selecting the best method to compare worksheets in Excel depends on several factors, including the complexity of your data, the level of detail required in the comparison, and your comfort with different types of tools.

Visual Side-by-Side Comparison: Best for quick, manual checks of smaller datasets or for gaining a general overview of differences.
Formula-Based Comparison and Conditional Formatting: Suitable for identifying value differences in structured data, especially when you need to highlight discrepancies directly in a worksheet. However, they are limited in handling structural changes and comparing formats or formulas.
Compare and Merge Workbooks Feature: Ideal for collaborative environments where multiple users are editing copies of a shared workbook and changes need to be consolidated.
Third-Party Tools (e.g., Synkronizer, Ablebits Compare Sheets): Recommended for advanced comparison needs, including detailed analysis of values, formulas, formatting, and structural changes. They offer more comprehensive features, better accuracy, and often provide merging capabilities.
Online Services: Convenient for occasional, quick comparisons of non-sensitive data without software installation.

By understanding these different methods and their strengths and limitations, you can effectively compare worksheets in Excel and choose the approach that best meets your specific requirements, ensuring data accuracy and efficient workflow.