How Can I Compare Two Excel Sheets For Differences?

How Can I Compare Two Excel Sheets? You can compare two Excel sheets by using built-in features like “View Side by Side,” formulas, and conditional formatting. For more advanced comparisons, third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets offer comprehensive solutions. Visit COMPARE.EDU.VN for more detailed comparisons and reviews of these tools, ensuring you make the most informed decision and streamline your data comparison processes with efficiency and precision. Discover the best strategies to compare Excel spreadsheets effortlessly.

1. Comparing Two Excel Files By Viewing Them Side By Side

The simplest method is to visually compare two Excel files using the “View Side by Side” feature. This is particularly useful for smaller workbooks where you can easily spot differences with a careful eye.

1.1. Comparing Two Excel Workbooks

To open two Excel files side by side:

1. Open both workbooks you wish to compare.
2. Go to the View tab, in the Window group, and click the View Side by Side button.

By default, the Excel windows will appear horizontally.

To arrange them vertically, click Arrange All and select Vertical:

To scroll through both worksheets at the same time, enable Synchronous Scrolling located on the View tab, under the View Side by Side button.

For a more comprehensive guide, read about viewing Excel workbooks side by side.

1.2. Arranging Multiple Excel Windows Side By Side

To view more than two Excel files at once:

1. Open all the workbooks you want to compare.
2. Click the View Side by Side button. The Compare Side by Side dialog box will appear.
3. Select the files to be displayed with the active workbook.

Alternatively, to view all open Excel files, click Arrange All on the View tab, then choose your preferred arrangement: tiled, horizontal, vertical, or cascade.

1.3. Comparing Two Sheets in the Same Workbook

When the sheets you want to compare are in the same workbook:

1. Open the Excel file and go to View > Window.
2. Click the New Window button. This opens the same file in a new window.
3. Enable View Side by Side.
4. Select the first sheet in one window and the second sheet in the other.

2. How to Compare Two Excel Sheets for Differences in Values

A straightforward way to compare data is using Excel formulas to identify cells with different values. This method creates a difference report in a new worksheet.

To compare two Excel worksheets:

1. Open a new, blank sheet.
2. Enter the following formula in cell A1:

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

3. Drag the fill handle to copy the formula down and to the right.

This formula compares corresponding cells in Sheet1 and Sheet2 and displays the differences.

Note that dates may appear as serial numbers because of Excel’s internal storage system, which can make it difficult to analyze differences.

3. Highlighting Differences Between Two Sheets with Conditional Formatting

Another effective method is to use conditional formatting to highlight cells with different values.

To highlight differences:

1. In the worksheet where you want to highlight differences, select all used cells (e.g., click A1 and press Ctrl + Shift + End).
2. On the Home tab, go to Conditional Formatting > New Rule.
3. Create a new rule with the formula:

=A1<>Sheet2!A1

(Replace Sheet2 with the name of the sheet you are comparing.)

As a result, cells with different values will be highlighted with the color of your choice.

For more detailed steps, refer to the tutorial on Excel conditional formatting based on another cell value.

However, these methods have limitations:

• They only detect value differences, not formula or formatting differences.
• They cannot identify added or deleted rows and columns accurately.
• They work on a sheet level and cannot detect workbook-level structural differences.

4. Comparing and Merging Copies of a Shared Workbook

For scenarios where multiple users collaborate on the same Excel file, the Compare and Merge feature is invaluable. It allows you to view changes and comments from all users in one place.

Before using this feature:

• Share the Excel workbook by clicking the Share Workbook button on the Review tab, under Changes.
• Select the Allow Changes by More Than One User… box.
• Each user must save a copy with a unique file name.

4.1. Enabling the Compare and Merge Workbooks Feature

In Excel 2010 through Excel 365, this feature is not displayed by default. To add it to the Quick Access toolbar:

1. Open the Quick Access drop-down menu and select More Commands.
2. In the Excel Options dialog box, choose All Commands.
3. Scroll to Compare and Merge Workbooks, select it, and click Add.
4. Click OK.

4.2. Comparing and Merging Workbooks

1. Open the primary version of the shared workbook.
2. Click the Compare and Merge Workbooks command on the Quick Access toolbar.
3. Select the copies of the shared workbook to merge. Hold Shift to select multiple copies and click OK.

4.3. Reviewing the Changes

To review the edits:

1. Go to the Review tab, under Changes.
2. Click Track Changes > Highlight Changes.
3. In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, and clear the Where box.
4. Select Highlight changes on screen and click OK.

Excel highlights the column letters and row numbers with differences in dark red. Different users’ edits are marked with different colors at the cell level. Hover over a cell to see who made the change.

Note: The Compare and Merge Workbooks command is only for copies of the same shared workbook, not different Excel files.

5. Third-Party Tools to Compare Excel Files

Built-in Excel features might not comprehensively compare sheets for differences in values, formulas, or formatting. Third-party tools provide advanced functionalities for comparing, updating, and merging Excel sheets and workbooks.

5.1. Synkronizer Excel Compare: 3-in-1 Tool

Synkronizer Excel Compare add-in is a tool to compare, merge, and update Excel files.

Key Features:

• Identify differences between Excel sheets.
• Combine multiple Excel files without duplicates.
• Highlight differences.
• Show relevant differences.
• Merge and update sheets.
• Provide detailed difference reports.

5.1.1. Comparing Two Excel Files

To run Synkronizer Excel Compare:

1. Go to the Add-ins tab and click the Synchronizer icon.

The Synkronizer pane appears on the left:

1. Select two workbooks to compare.

2. Select the sheets to compare. The add-in will automatically match and select sheets with the same names.

3. Select a comparison option:

   • Compare as normal worksheets.
   • Compare with link options.
   • Compare as database.
   • Compare selected ranges.

4. Choose content types to compare (optional). On the Select tab, under Compare, select:

   • Content: comments and names.
   • Formats: alignment, fill, font, border, etc.
   • Filters: ignore case, spaces, formulas, etc.

5. Click the Start button.

5.1.2. Visualizing and Analyzing Differences

Synkronizer provides two summary reports on the Results tab:

• A summary report showing all difference types.
• A detailed difference report for specific differences.

Clicking a difference in the detailed report selects the corresponding cells in both sheets.

You can create a difference report in a separate workbook, with hyperlinks to specific differences.

5.1.3. Comparing All Sheets

If comparing two multi-sheet Excel files, all matching worksheet pairs will appear in the summary report.

5.1.4. Highlighting Differences

By default, Synkronizer highlights:

• Yellow: Cell value differences.
• Lilac: Cell format differences.
• Green: Inserted rows.

To highlight only relevant differences, click Outline on the Results tab and select the required options.

5.1.5. Updating and Merging Sheets

You can transfer individual cells or columns/rows from the source to the target sheet.

To update differences, select them in the Synkronizer pane and click one of the four update buttons:

• First and last buttons: Update all differences.
• Second and third buttons: Update selected differences.

An evaluation version is available for download here.

5.2. Ablebits Compare Sheets for Excel

Ablebits Compare Sheets is another tool for comparing worksheets.

Key Features:

• A step-by-step wizard.
• Comparison algorithm selection.
• Review Differences mode.

To use Ablebits Compare Sheets:

1. Click the Compare Sheets button on the Ablebits Data tab, in the Merge group.

2. Select the worksheets to compare. You can select the entire sheets, the current table,

or a specific range.

3. Select a comparison algorithm:

   • No key columns.
   • By key columns.
   • Cell-by-cell.

   Also, select a match type:

   • First match.
   • Best match.
   • Full match only.

4. Specify which differences to highlight and ignore.

5. Click the Compare button.

5.2.1. Review and Merge Differences

The worksheets are opened in Review Differences mode, with the first difference selected.

Differences are highlighted:

• Blue rows: Rows only in Sheet 1.
• Red rows: Rows only in Sheet 2.
• Green cells: Difference cells in partially matching rows.

Use the vertical toolbar to merge or ignore differences.

When done, save the workbooks and exit Review Differences mode.

An evaluation version is available for download here.

5.3. xlCompare: Compare and Merge Workbooks

With xlCompare, you can compare Excel files, worksheets, names, and VBA Projects.

Key Features:

• Find duplicate records and remove them.
• Update records with values from another sheet.
• Add unique rows and columns.
• Merge updated records.
• Sort data by key column.
• Filter comparison results.
• Highlight results with colors.

5.4. Change Pro for Excel

Change pro for Excel is a desktop and mobile solution to compare Excel sheets.

Key Features:

• Find differences in formulas and values.
• Identify layout changes.
• Recognize embedded objects.
• Create difference reports.
• Filter and sort the reports.
• Compare files from Outlook or document management systems.
• Support all languages.

6. Online Services to Compare Excel Files

Online services allow you to compare Excel sheets without installing software. Security can be a concern, so use these services only for non-sensitive data.

Examples:

• XLComparator
• CloudyExcel

With CloudyExcel, upload two Excel workbooks and click Find Difference. The differences will be highlighted.

7. FAQ About Comparing Excel Sheets

Q1: How can I quickly compare two small Excel sheets?

A: Use Excel’s “View Side by Side” feature to visually compare the sheets, making it easy to spot differences.

Q2: Can I compare two Excel files without installing any software?

A: Yes, online services like XLComparator and CloudyExcel allow you to upload and compare Excel files directly through your web browser.

Q3: What is the best way to highlight differences in two Excel sheets?

A: Use conditional formatting with a formula to highlight cells that have different values. This makes the differences stand out visually.

Q4: How do I compare and merge two versions of a shared Excel workbook?

A: Enable the “Compare and Merge Workbooks” feature in Excel. Ensure that the workbooks are copies of the same shared file.

Q5: What should I do if the built-in Excel features are not enough for my comparison needs?

A: Consider using third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets, which offer more advanced comparison and merging capabilities.

Q6: How can I compare two Excel sheets for formula differences?

A: Third-party tools such as Synkronizer Excel Compare or Change Pro for Excel are designed to identify differences in formulas, formats, and values.

Q7: Is there a way to ignore case sensitivity when comparing Excel sheets?

A: Yes, some third-party tools like Synkronizer Excel Compare allow you to filter out case differences during the comparison process.

Q8: How do I identify added or deleted rows and columns between two Excel sheets?

A: Use third-party tools like Ablebits Compare Sheets or Change Pro for Excel, which are capable of detecting layout changes, including added or deleted rows and columns.

Q9: Can I compare Excel sheets that have different layouts or sizes?

A: Yes, tools like Ablebits Compare Sheets offer different comparison algorithms, including options suitable for sheets with different layouts or sizes.

Q10: How can I ensure that my data is secure when using online services to compare Excel files?

A: Avoid uploading Excel files with sensitive information to online services. If you must use such services, ensure they have robust security measures and a privacy policy that you trust.

8. Call to Action

Comparing Excel sheets can be complex, but with the right tools and methods, you can easily identify and manage differences. Whether you’re a student, a professional, or anyone in between, COMPARE.EDU.VN provides comprehensive comparisons and reviews of tools to help you make informed decisions. Simplify your data comparison process and ensure accuracy by exploring our resources. Visit COMPARE.EDU.VN today to find the perfect solution for your needs. Contact us at 333 Comparison Plaza, Choice City, CA 90210, United States or via Whatsapp at +1 (626) 555-9090. Visit our website at compare.edu.vn. Optimize your spreadsheet comparisons with our expert insights and user-friendly guides.