Comparing two Excel files or sheets for differences can be a critical task for anyone working with data. Whether you’re a project manager, accountant, analyst, or just someone who needs to ensure data accuracy, knowing how to compare Excel files can save you time and prevent costly mistakes. In this article, we’ll show you how to compare two Excel files for differences, highlighting any discrepancies in data, formulas, or formatting.

By learning powerful tools and techniques, such as using Excel’s built-in features or specialized comparison software, you can quickly and efficiently spot any variations between your datasets. Let’s explore the best ways to compare Excel sheets, ensuring your data remains accurate and reliable.

Compare two Excel files or sheets for differences

How to Compare two Excel Sheets Side by Side

Step 1: Open Excel

Open Excel by clicking on it and click on the blank sheet that is being shown.

Open Excel

Step 2: Launch the two workbooks

Ensure that the reports which you want to have side by side are accessible in Excel.

Enter data into the sheet or open an existing sheet

Step 4: Go to the View tab

Select the view option, which is being shown on the upper side.

Go to View Tab

Step 5: Launch side by side

Select “View Side by Side” from the view tab. You may find this button under the Window group on the View tab. It appears to be two sheets that overlap.

Note: The button may be buried if you have one worksheet open. First, open the other workbook.

View Tab >>Arrange all>>View Side by Side

Step 6: View them collectively (if desired)

Excel displays both reports horizontally by default, indicating that they will be shown adjacent.

Step 7: Vertical view

If you would rather see them piled on top of one another (vertically), click the “Arrange All” button in the Window group on the View tab.

Step 8: Select “Vertical”

Arrange All” from the menu after that and Examine each row individually.

How to Compare two sheets in Same Workbook

Step 1: Open your Excel File

Step 2: Go to View Tab>>Windows Group and Click on New Window button

Step 3: Now Same Excel File will be opened in a Different Window

Step 4: Enable View Side by Side mode from the ribbon.

Step 5: Select sheet 1 in the first window and sheet 2 in the second window.

How to Compare two Excel Sheets for differences in Values

Step 1: Get your Excel sheet

Ensure the workbook containing the sheets you wish to compare is the same.

Step 2: Make a new sheet for the result

To add a new blank sheet, click the (+) plus sign in the lower-left corner of the sheet tabs.

add a new sheet and put the data

Step 3: Enter the formula for comparison

Enter this formula in cell A1: =IF(Sheet1!A1<>Sheet2!A1, “Sheet1: ” & Sheet1!A1 & ” vs Sheet2: ” & Sheet2!A1, “”).

Step 4: Use “<“, “>” sign

This sign indicates a difference. If equal, the formula displays nothing to indicate no difference.

Step 6: To apply this formula to more cells in Excel

Click and hold A1’s bottom right corner, known as the fill handle. Drag across rows/columns to duplicate formulas.

Highlight Differences Between 2 sheets with Conditional Formatting

Step 1: Open the Sheet

Open the sheet you want to view.

Step 2: Select Cell A1

Click on cell A1 (upper left corner).

Step 3: Go to the last cell

Use Ctrl + Shift + End to navigate to the last cell in the sheet.

Step 4: Go to the Home tab and Press the conditional formatting

Go to the Home in the excel. Click on “Conditional Formatting” in the Styles group, then select “New Rule” from the dropdown.

Step 5: Set Up the Custom Formula

In the pop-up window, enter the custom formula to specify the differences to highlight. Example formula: =A4<>Sheet2!A2.

Step 6: Choose highlight color

Click on “Format cells” and choose a color to highlight the differences.

Step 7: Apply the Formatting

Click OK to apply the formatting.

How to Compare Excel Files for Differences Using Formulas

Step 1: Get your data ready

Ascertain that the worksheets you wish to compare are in the same workbook.

Step 2: Make a fresh results sheet

To add a new blank sheet, click the (+) Plus sign in the lower-left corner of the sheet tabs. Here’s when the variations become apparent.

Step 3: Type the formula for comparison in

Enter this formula in the new sheet’s cell A1: Excel = IFJan!A1 & CHAR(10) & “Feb Value:” & Feb!A1, “Jan Value:” & Jan!A1, “”) Proceed with caution while using the formula.

Step 4: Use the following formula in additional cells

Select and hold cell A1’s lower right corner (the little square). We refer to this as the fill handle. Pull the fill handle across and down the column of the row to replicate the formula across further cells. The formula’s cell references will be automatically adjusted to compare the appropriate cells in the two sheets.

Step 5: Examine the outcomes

Now you will only see the cells in the new sheet that have different values.

Specialized tools to compare files in Excel

Xltrail

By using Xltrail, an Excel-focused web-based application similar to GitHub, you may avoid the headache of manually comparing spreadsheets. It is an invaluable tool for anybody who wants to maintain their spreadsheets stress-free and organized since it keeps track of all changes made to the spreadsheet and displays them in an easy-to-read format, including VBA code. Changes are indicated with colors, such as green for new additions and red for deletions.

Xltrail

Synkronizer

Another excellent spreadsheet comparison tool is Synkronizer. It’s a great option for anyone looking for a hassle-free way to stay on top of spreadsheet updates because, unlike others, it handles changes automatically. It displays both of your original workbooks side by side with color-coded cells (green for new info, red for deletions) for a clear picture of what’s been modified.

Synkronizer

Spreadsheet Compare

Spreadsheet Compare uses Microsoft Excel to compare spreadsheets. Spreadsheet Compare is a free desktop application concealed in some versions of the Office suite (ProPlus in Office 365 and ProPlus since 2013). This tool can compare your spreadsheets, but it requires the correct version of Excel, so it’s only for some. Even Excel itself has an add-in named “Inquire” that you may use to launch it.

Spreadsheet Compare

DiffEngineX

DiffEngineX is a little picky about rows and columns getting mixed up. Thistool believes that if you have made any sophisticated changes to your spreadsheets, you should provide a unique “primary key” for each sheet, including the rows and columns. That would work OK for a brief comparison, but this “primary key” thing isn’t going to cut it if you have a ton of workbooks to go through.

DiffEngineX

Online services to compare two Excel sheets

Other than Softwares on your computer, there are websites where you can compare Excel sheets quickly. They’re free but less secure.

xlcomparator

XL Comparator is a free tool that allows you to compare two Excel or CSV spreadsheets without programming macros or installing any software on your computer1. It’s a convenient way to identify differences between data sets, making it useful for tasks like data reconciliation, validation, and quality control.

xlcomparator

Aspose Cells Comparison

Aspose Cells Comparison is a website where you can compare Excel files easily. You can upload two Excel or CSV files to see the differences between them. It works on any device like Windows, Mac, Android, or iOS without needing to install anything. Your files are processed securely and automatically deleted after 24 hours.

Aspose Cells Comparison

Xlcompare

xlCompare.com is the best tool to compare Excel files and highlight the difference. Compare Excel sheets just in one click. Find the difference between two Excel files. Pull matching and unique rows into another file and save it on disk. This online Excel compare tool will save time.

Xlcompare

Cloudyexcel

CloudyExcel is an online service that provides tools for comparing and analyzing Excel files. You can upload two Excel workbooks or sheets and compare them to identify differences between the active sheets. The service highlights discrepancies, making it easier to spot changes, inconsistencies, or missing data. Generate customized reports directly from your Excel files.

Cloudyexcel

Conclusion

You may compare Excel files or sheets using a few different methods to identify discrepancies. Different worksheets or the same workbook may be used to see them side by side. You may use conditional formatting to draw attention to differences across sheets. Another use of formulas is to identify and show just the differences between two data sets. How you want to see the findings and arrange your data will determine which approach is ideal for you.

FAQ’s on Excel File Comparison

How can I view two Excel sheets simultaneously?

A: Excel’s “View Side by Side” function is available. Open the two sheets you wish to compare, select the “View” tab, look for the “Window” group, and press the button that displays two overlapping sheets. You can arrange them vertically or horizontally to make it easier to observe both.

How can I compare two Excel sheets to see what changes there are?

A: There are several options! You can use a particular formula to highlight cells with different values in a different colour. Alternatively, make a new sheet using a formula that displays just the cells in which the information from the two sheets differs.

I have my data in two different Excel files. Can I still compare workbooks?

A: Yes! Every technique for comparing sheets may also be used to compare separate Excel files. Open both worksheets and follow the same procedures to compare the two workbooks or use formulae to identify data discrepancies.