Top 4 Methods to Compare two Excel files for differences

Comparing two Excel files is an important skill for data analysis, financial reporting, or inventory management. In this article, we will cover the top 4 methods on how to compare two Excel files.

Key Takeaways:

• You can compare two Excel files by viewing them side by side.
• Use New Window to compare two sheets in the same workbook.
• Formulas can quickly find differences between two worksheets.
• Conditional Formatting can highlight cells based on certain conditions.

 

How to Compare two Excel files

Viewing Multiple Sheets Side by Side

STEP 1: To utilize this feature and juxtapose your data sets, start by opening the two sheets you wish to compare.

• Sales Report- January

• Sales Report- February

STEP 2: Then, with a simple navigation to the “View” tab and a click on the “View Side by Side” button, your sheets will snugly align, permitting a simultaneous, panoramic view of your data landscapes.

• Use ‘Arrange All’ and then select ‘Vertical’ to align the sheets vertically.

STEP 3: As a result, you can swiftly scroll through both sheets simultaneously, matching data line by line or column by column, ensuring nothing eludes your analysis.

Note: The same process can be done for anyone working on multiple workbooks i.e., > 2 workbooks.

Compare Two Sheets within the Same Workbook

STEP 1: Open your workbook in Excel.

STEP 2: Go to the “View” tab, and in the “Window” group, click the “New Window” button. This opens the same workbook in a new window.

STEP 3: Click the “View Side by Side” button on the ribbon.

STEP 4: In the first window, select Sheet 1, in the second window, select Sheet 3 and finally in the second window, select Sheet 3.

• Use ‘Arrange All’ and then select ‘Vertical’ to align the sheets vertically.

• RESULT-

 

How to Compare Two Worksheets for Differences in Values

Jumping into Inquire for Excel comparisons is like having a detective at your fingertips, keen to unravel the mysteries between your worksheets.

Let’s have a look at the two sheets; namely

• Sheet 1

• Sheet 2

It is the most straightforward method for identifying cells with differing values when comparing data in Excel. You’ll have a difference report in a new worksheet as a result.

STEP 1: Simply create a new, blank Excel page here, Sheet3 type the following formula in cell A1;

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

STEP 2: And then move and drag the fill handle to the right and down to compare the two spreadsheets for differences.

• Because relative cell references are used, the formula will vary depending on how the column and row are positioned relative to one another. Here we can see the difference between the two sheets in cell A2, A5, A6 & A7 of Sheet 3.

INFERENCE:- It means that in Sheet 3;

• Cell A2 shows a discrepancy in the spelling of the First name of the person.
• Cell A5, A6 & A7 highlight that the First Names; Sara, Louis, and Phil are not a part of Sheet 1 data.

Hence, this tool is perfect for auditing purposes, ensuring that all alterations are intentional and accounted for, keeping your data’s integrity in check.

Compare two sheets using Conditional Formatting

STEP 1: Highlight the data range in the first sheet.

STEP 2: Go to the “Home” tab, click “Conditional Formatting,” then choose “New Rule.”

STEP 3: Select “Use a formula to determine which cells to format.” Enter =B2<>’Sales Jan Budget’!B2, set the desired format, and click “OK.”

STEP 4: This highlights discrepancies between the sheets.

 

FAQs