**How To Compare Two Excels For Differences: A Comprehensive Guide**

Comparing two Excel files for differences is a common task, whether you’re merging data, auditing spreadsheets, or tracking changes. COMPARE.EDU.VN offers expert insights and a range of methods to make this process efficient. This guide explores various techniques, from built-in Excel features to third-party tools, ensuring you find the best solution for your needs, including highlighting disparities, identifying discrepancies, and performing comparative analysis.

1. Visual Comparison: Viewing Excel Files Side by Side

Question: How can I visually compare two Excel files without using formulas or advanced features?

Answer: Excel’s “View Side by Side” mode lets you visually compare two Excel files or sheets. This feature is especially useful when you have relatively small workbooks and need a quick overview.

You can open two Excel files side by side by doing the following:

Open the workbooks you want to compare.
Go to the View tab, Window group, and click the View Side by Side button.

By default, two separate Excel windows are displayed horizontally.

To split Excel windows vertically, click Arrange All button and select Vertical:

As the result, two separate Excel windows will be arranged side by side.

If you want to scroll through both worksheets simultaneously to compare data row-by-row, make sure the Synchronous Scrolling option it turned on. This option resides on the View tab, in the Window group, right under the View Side by Side button, and is usually turned on automatically as soon as you activate View Side by Side mode.

1.1. Comparing Multiple Excel Windows Simultaneously

Question: Is it possible to view more than two Excel files at the same time for comparison?

Answer: Yes, Excel allows you to view multiple Excel files side by side or in other arrangements.

To view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button. The Compare Side by Side dialog box will appear, and you select the files to be displayed together with the active workbook.

To view all open Excel files at a time, click the Arrange All button on the View tab, in the Window group, and choose your preferred arrangement: tiled, horizontal, vertical or cascade.

1.2. Comparing Two Sheets Within the Same Workbook

Question: How can I compare two different sheets within the same Excel workbook?

Answer: You can easily compare two sheets within the same workbook using the “New Window” feature in Excel.

To view them side by side, perform the following steps.

Open your Excel file, go to the View tab > Window group, and click the New Window button.
This will open the same Excel file in a different window.
Enable View Side by Side mode by clicking the corresponding button on the ribbon.
Select sheet 1 in the first window and sheet 2 in the second window.

2. Formula-Based Comparison: Identifying Value Differences

Question: What is the simplest formula to compare data in two Excel sheets and create a difference report?

Answer: You can use a simple IF formula to identify cells with different values. This will create a difference report in a new worksheet.

To compare two Excel worksheets for differences, just open a new empty sheet, enter the following formula in cell A1, and then copy it down and to the right by dragging the fill handle:

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

Due to the we use of relative cell references, the formula will change based on a relative position of the column and row. As the result, the formula in A1 will compare cell A1 in Sheet1 and Sheet2, the formula in B1 will compare cell B1 in both sheets, and so on.

As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells. Please note that in the difference report, dates (cell C4) are presented by serial numbers as they are stored in the internal Excel system, which is not very convenient for analyzing differences between them.

3. Conditional Formatting: Highlighting Differences Visually

Question: How can I highlight cells with different values in two sheets using conditional formatting?

Answer: Excel’s conditional formatting feature allows you to highlight cells that have different values in two sheets with a color of your choosing.

To highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:

In the worksheet where you want to highlight differences, select all used cells. For this, click the upper left cell of the used range, usually A1, and press Ctrl + Shift + End to extend the selection to the last used cell.
On the Home tab, in the Styles group, click Conditional Formatting > New rule, and create a rule with the following formula: =A1<>Sheet2!A1

Where Sheet2 is the name of the other sheet you are comparing.

As the result, the cells with different values will get highlighted with the selected color:

3.1. Limitations of Formula and Conditional Formatting Methods

Question: What are the limitations of using formulas and conditional formatting for comparing Excel files?

Answer: While formulas and conditional formatting are useful, they have limitations that make them unsuitable for comprehensive comparisons:

They only find differences in values and cannot compare formulas or cell formatting.
They cannot identify added or deleted rows and columns.
They work on a sheet level but cannot detect workbook-level structural differences.

4. Compare and Merge Shared Workbooks

Question: How can I merge different versions of the same Excel file, especially when multiple users are collaborating?

Answer: Excel’s “Compare and Merge” feature is designed for merging different versions of a shared workbook. It allows you to view changes and comments from all users.

To leverage this feature, be sure to do the following preparations:

Share your Excel workbook before you make it available to other users. To share a workbook, just click the Share Workbook button on the Review tab, in the Changes group, select the Allow Changes by More Than One User… box, and click OK. Allow Excel to save the workbook if prompted. Turning on the Track Changes feature shares the workbook automatically.
Each person who makes any edits to the shared workbook must save a copy (.xls or xlsx file) using a unique file name.

Now that all initial preparations are done properly, you are ready to combine the copies of a shared workbook.

4.1. Enabling the Compare and Merge Workbooks Feature

Question: How do I enable the “Compare and Merge Workbooks” feature in Excel, as it is not displayed by default?

Answer: The “Compare and Merge Workbooks” feature, although available in Excel 2010 through Excel 365, is not displayed by default. You need to add it to the Quick Access toolbar.

Open the Quick Access drop down menu and select More Commands.
In the Excel Options dialog box, select All Commands under Choose commands from.
In the list of commands, scroll down to Compare and Merge Workbooks, select it and click the Add button to move it to the right-hand section.
Click OK.

4.2. Merging Workbooks

Question: What are the steps to compare and merge workbooks using the “Compare and Merge Workbooks” feature?

Answer: Once the feature is enabled, you can merge copies of a shared workbook into one file.

Open the primary version the shared workbook.
Click the Compare and Merge Workbooks command on the Quick Access toolbar.
In the dialog box that appears, select a copy of the shared workbook that you want to merge. To select several copies, hold the Shift key while clicking the file names, and then click OK.

4.3. Reviewing Changes

Question: How can I review the changes made by different users in a merged workbook?

Answer: Excel highlights the changes made by different users, making it easy to review and understand the edits.

Switch to the Review tab > Changes group, and click Track Changes > Highlight Changes.
In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, clear the Where box, select the Highlight changes on screen box, and click OK.

To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color. At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell.

4.4. Limitations of Compare and Merge Workbooks

Question: What are the limitations of the “Compare and Merge Workbooks” feature in Excel?

Answer: The “Compare and Merge Workbooks” feature only works with copies of the same shared workbook. It cannot be used to combine different Excel files.

If the Compare and Merge Workbooks command is greyed out in your Excel, most likely you are trying to combine different Excel files. Please remember, the Compare and Merge Workbooks feature allows merging only copies of the same shared workbook.

5. Third-Party Tools: Advanced Comparison Solutions

Question: What are some advanced third-party tools for comparing Excel files that offer more comprehensive features?

Answer: For more advanced and efficient comparisons, consider using third-party tools specially designed for comparing, updating, and merging Excel sheets and workbooks.

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

Question: What key features does Synkronizer Excel Compare offer for comparing, merging, and updating Excel files?

Answer: The Synkronizer Excel Compare add-in can quickly compare, merge and update two Excel files saving you the trouble of searching for differences manually.

Identifying the differences between two Excel sheets.
Combining multiple Excel files into a single version without producing unwanted duplicates.
Highlighting the differences in both sheets.
Showing only the differences that are relevant to your task.
Merging and updating the sheets.
Presenting detailed and easy to read difference reports.

5.1.1. Comparing Two Excel Files with Synkronizer

Question: How does Synkronizer Excel Compare perform when comparing two Excel files for differences, such as in an event planning scenario?

Answer: Synkronizer efficiently compares two sheets and identifies differences by selecting the workbooks, sheets, and comparison options, and then starting the comparison.

Select 2 workbooks to compare:

Select sheets to compare. If the selected workbooks have any sheets with the same names, all those sheets will be matched and automatically selected for comparison (like Participants sheets in the below screenshot).

Also, you can select worksheets manually or instruct the add-in to match sheets by other criteria, for example by worksheet type – all, protected, or hidden.

Once you’ve selected the sheets, the Synkronizer add-in will open them side by side, arranged vertically or horizontally, like in Excel’s View Side by Side mode.

Select one of the following comparison options:
- Compare as normal worksheets – the default option that works in most cases.
- Compare with link options – if the selected sheets do not contain any new or deleted rows and columns, you can compare them “1 on 1”.
- Compare as database – recommended for comparing sheets that have a database structure.
- Compare selected ranges – if you do not want to compare the entire sheets, define the ranges to be compared.
Choose the content types to be compared (optional). On the Select tab, in the Compare group, you can choose the content type(s) relevant to your current task:
- Under Content, you can select comments and names (in addition to cell values, formulas and calculated values that are compared by default).
- Under Formats, you choose what cell formats such as alignment, fill, font, border etc.
- The Filters option lets you filter out differences that you don’t want to display. For example, you can ignore case, leading or trailing spaces, all formulas or different formulas with the same result, hidden rows or column, and more.
Finally, click the big red Start button on the ribbon and proceed to examining the results.

5.1.2. Visualizing and Analyzing Differences with Synkronizer

Question: How does Synkronizer Excel Compare present and help analyze the differences found between two Excel sheets?

Answer: Synkronizer presents two summary reports: a brief overview of difference types and a detailed report, with color-coded highlighting and easy navigation.

Usually it takes Synkronizer only a few seconds to compare 2 sheets and present two summary reports on the Results tab:

In a summary report, you can see all difference types at a glance: changes in columns, rows, cells, comments, formats, and names.
To view the detailed difference report, just click a specific difference type on the summary report.

The following screenshot shows the summary report (in the upper part of the Results pane), and cell difference report (in the lower part of the pane) that were created for our sample sheets:

Clicking on a difference in the detailed report will select the corresponding cells on both sheets (the below screenshot shows just one sheet because there’s enough room to show both 🙂

In addition, you can create a difference repot in a separate workbook, either standard or hyperlinked, and jump to a specific difference with a mouse click:

5.1.3. Comparing Multiple Sheets at Once

Question: Can Synkronizer Excel Compare compare all sheets in two workbooks simultaneously?

Answer: Yes, Synkronizer can compare all matching worksheet pairs in two Excel files at the same time, providing a comprehensive summary report.

If the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be presented in the summary report for your perusal:

5.1.4. Highlighting Differences with Synkronizer

Question: How does Synkronizer Excel Compare highlight the differences between sheets, and can these highlights be customized?

Answer: Synkronizer highlights differences with default colors that are easily customizable.

By default, the Synkronizer Excel Compare add-in highlights all found differences, like in the following screenshot:

Yellow – differences in cell values
Lilac – differences in cell formats
Green – inserted rows

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

5.1.5. Updating and Merging Sheets with Synkronizer

Question: How can Synkronizer Excel Compare be used to update and merge sheets, transferring individual cells or rows?

Answer: Synkronizer allows you to transfer individual cells or move different columns/rows from the source to target sheet, and have your primary sheet updated in seconds.

To update one or more differences, select them on the Synkronizer’s pane and click one of the 4 update buttons – the first and last buttons update all differences, while the 2nd and 3rd buttons update selected differences only (the button arrows indicate the transfer direction):

5.2. Ablebits Compare Sheets for Excel

Question: What makes Ablebits Compare Sheets a user-friendly tool for comparing worksheets in Excel?

Answer: Ablebits Compare Sheets is designed with a step-by-step wizard, various comparison algorithms, and a “Review Differences” mode for intuitive comparison.

Click the Compare Sheets button on the Ablebits Data tab, in the Merge group:
The wizard will show up asking you to select the two worksheets you’d like to compare for differences. By default, the entire sheets are selected, but you can also select the current table

or a specific range by clicking the corresponding button:

On the next step, you select the comparison algorithm:
- No key columns (default) – works best for sheet-based documents like invoices or contracts.
- By key columns – is appropriate for column-organized sheets that have one or more unique identifiers such as order numbers or product ID’s.
- Cell-by-cell – best to be used to compare spreadsheets with the same layout and size, like balance sheets or year-to-year reports.

Tip. If you are unsure which option is right for you, go with the default one (No key columns). Whichever algorithm you select, the add-in will find all the differences, it will only highlight them differently (entire rows or individual cells).

On the same step, you can choose the preferred match type:

First match (default) – compare a row in Sheet 1 to the first found row in Sheet 2 that has at least one matching cell.
Best match – compare a row in Sheet 1 to the row in Sheet 2 that has the maximum number of matching cells.
Full match only – find rows in both sheets that have exactly the same values in all the cells, and mark all other rows as different.

In this example, we will look for Best match by using the default No key columns comparing mode:

Finally, specify which differences to highlight and which to ignore, and how to mark the differences. Cell formatting is important to us, so we select Show differences in formatting. Hidden rows and columns are irrelevant, and we tell the add-in to ignore them:

Click the Compare button and allow the add-in a couple of minutes to process your data and make the backup copies. Backups are always created automatically, so you may not worry about the safety of your data.

5.2.1. Reviewing and Merging Differences with Ablebits

Question: How does Ablebits Compare Sheets help review and merge the differences between worksheets once they have been processed?

Answer: Once the worksheets are processed, they are opened side-by-side in a special Review Differences mode, with the first difference selected:

On the screenshot above, the differences are highlighted with the default colors:

Blue rows – rows that exist only in Sheet 1 (on the left).
Red rows – rows that exist only in Sheet 2 (on the right).
Green cells – difference cells in partially matching rows.

To help you review and manage the differences, each worksheet has its own vertical toolbar. For the inactive worksheet (on the left) the toolbar is disabled. To enable the toolbar, just select any cell in the corresponding sheet.

By using this toolbar, you go through the found differences one-by-one and decide whether to merge or ignore them:

As soon as the last difference is dealt with, you will be prompted to save the workbooks and exit the Review differences mode.

If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:

Save the changes you’ve made and remove the remaining difference marks, or
Restore the original workbooks from the backup copies.

5.3. xlCompare: Workbook, Sheet, and VBA Project Comparison

Question: What additional features does xlCompare offer, particularly for comparing workbooks, sheets, and VBA projects?

Answer: The xlCompare utility is useful for comparing two Excel files, worksheets, names and VBA Projects.

Find duplicate records between two worksheets and remove them.
Update all existing records in one sheet with values from another sheet.
Add unique (new) rows and columns from one sheet to another.
Merge all updated records from one workbook to another.
Sort data on the sheets by the key column.
Filter the comparison results to display differences or identical records.
Highlight comparison results with colors.

5.4. Change pro for Excel: Desktop and Mobile Comparison

Question: What are the key features of Change pro for Excel, especially regarding desktop and mobile comparisons?

Answer: With Change pro for Excel, you can compare two sheets in desktop Excel as well as on mobile devices with optional server-based comparison.

Find differences between 2 sheets in formulas and values.
Identify layout changes, including added / deleted rows and columns.
Recognize embedded objects such as charts, graphs and images.
Create and print difference reports of formula, value and layout differences.
Filter, sort, and search the difference report on key changes.
Compare files directly from Outlook or document management systems.
Support for all languages, including multi-byte.

6. Online Services: Quick Comparisons Without Installation

Question: What are some online services available for quickly comparing Excel sheets without installing any software?

Answer: There are online services like XLComparator and CloudyExcel that offer quick comparisons without needing to install software.

For example, you can try XLComparator or CloudyExcel, or find some other similar services on the net.

You just upload the two Excel workbooks you want to compare, and click the Find Difference button at the bottom of the screen.

FAQ Section

Question 1: Why is it important to compare two Excel files for differences?

Answer: Comparing Excel files helps in merging data, auditing spreadsheets, tracking changes, spotting potential problems like broken links, duplicate records, inconsistent formulas, or wrong formatting.

Question 2: Can I compare Excel files without using any software or add-ins?

Answer: Yes, you can use Excel’s built-in features like “View Side by Side,” formulas, and conditional formatting for basic comparisons. Online services also offer quick comparisons without installation.

Question 3: What if the built-in Excel features are not sufficient for my comparison needs?

Answer: For more advanced comparisons, consider using third-party tools like Synkronizer Excel Compare, Ablebits Compare Sheets, xlCompare, or Change pro for Excel.

Question 4: How can I compare Excel files that contain sensitive information securely?

Answer: Avoid using online services for sensitive data. Instead, use desktop-based third-party tools that offer secure and private comparison options.

Question 5: What should I do if I encounter errors while comparing Excel files using formulas?

Answer: Ensure that the cell references in your formulas are correct and that the sheets you are comparing exist in the workbook. Also, check for any syntax errors in the formula.

Question 6: How can I merge two Excel files without losing any data?

Answer: Use the “Compare and Merge Workbooks” feature in Excel for shared workbooks. For other cases, use third-party tools that offer advanced merging options with conflict resolution.

Question 7: What types of differences can be identified using third-party Excel comparison tools?

Answer: Third-party tools can identify differences in values, formulas, formatting, comments, names, added or deleted rows and columns, and workbook-level structural differences.

Question 8: Is it possible to compare Excel files on mobile devices?

Answer: Yes, some tools like Change pro for Excel allow you to compare Excel files on mobile devices with optional server-based comparison.

Question 9: How can I highlight only the relevant differences between two Excel files?

Answer: Some third-party tools like Synkronizer Excel Compare allow you to filter and highlight only the differences that are relevant to your current task.

Question 10: What are the limitations of using online services for comparing Excel files?

Answer: Online services may not be suitable for comparing sensitive data due to security concerns. Additionally, they may have limitations in terms of the types of differences they can identify and the size of files they can handle.

COMPARE.EDU.VN hopes this guide has provided a comprehensive overview of how to compare two Excel files for differences. Whether you’re using built-in features or advanced third-party tools, understanding these methods will help you efficiently manage and analyze your data.

Need more help comparing different products or services? Visit COMPARE.EDU.VN today for detailed comparisons and informed decision-making. Our team of experts provides unbiased reviews and side-by-side analyses to help you choose the best options for your needs. Make smarter choices with COMPARE.EDU.VN.

Contact Us:

Address: 333 Comparison Plaza, Choice City, CA 90210, United States
WhatsApp: +1 (626) 555-9090
Website: compare.edu.vn

How To Compare Two Excels For Differences: A Comprehensive Guide