Technology

Comparing 2 Excel files to find duplicate or different data

Are you looking for ways to compare two Excel files but don't know how? This article will guide you on quick and easy ways to compare two Excel files to find duplicate data or differences.

Administrator

26 December, 2023

141

Comparison of 2 Excel Files using the View Side by Side Feature

If you are working on a computer with multiple similar Excel files or multiple versions of a document, it can be difficult to identify the main version. Below are some quick and easy ways to compare two Excel files:

For two Excel files with fewer data, you can use this method to compare them quickly and easily in just 3 steps.

Step 1: In the Excel workspace window, click the “View” tab and select “View Side by Side”.

Step 2: By default, the separate Excel windows are displayed horizontally, making comparison difficult. To split the windows vertically, on the “View” tab, select “Arrange All”, then the Arrange Window dialog box appears and you select “Vertical” and then click “OK”.

Step 1: In the Excel workspace window, click the “View” tab, then click “New Window”.

Step 2: Then, select sheet 1 in the first window and sheet 2 in the second window.

Step 3: Like method 1, select the “View Side by Side” option to compare.

Comparing 2 Excel Files using the IF Function

If you have two Excel files with extensive data and manual comparison takes too much time, you can use this method to find the differences between the two worksheets. Follow these steps:

Step 1: In the Excel workspace, click the plus sign in the bottom left corner to create a new worksheet.

Step 2: In cell A1 of the newly created worksheet, enter the formula =IF(Sheet1!A1 <> Sheet2!A1, “Sheet1:”&Sheet1!A1&” vs Sheet2:”&Sheet2!A1, “”) and press Enter.

Step 3: Drag the formula to the end of the comparison range to display the differences in Excel.

Using Conditional Formatting to Compare 2 Excel Files

If you want to compare two worksheets in the same Excel workbook, you can use conditional formatting to highlight the differences. Here are 4 simple steps:

Step 1: First, select the entire worksheet you want to highlight the differences in.

Step 2: Then, click “Conditional Formatting” and select “New Rule”.

Step 3: Next, click “Use a formula to determine which cells to format”, enter the formula “=A1<>Sheet2!A1″, and click “Format”.

Step 4: In the “Fill” tab of the new window that appears, select the color you want to highlight the differences, and click “OK” to finish.

Using the Compare and Merge Workbooks Feature

This feature is useful when you want to compare different versions of the same Excel file and merge them into one complete file. The edited areas are marked.

Step 1: Click “File” and select “Options”.

Step 2: Then, select “Quick Access Toolbar”, click “All Commands”, select “Compare and Merge Workbooks”, click “Add”, and click “OK” to confirm.

Step 3: In the Excel sheet, click the “Compare and Merge Workbooks” icon, then select the files you want to compare and merge in the new window that appears.

Using Third-Party Software to Compare

In addition to the built-in features of Excel, users can use third-party tools to compare spreadsheets. These tools are specially designed to compare, update, and merge Excel worksheets and workbooks. Some tools you can consider are: Synkronizer Excel Compare, Cloud Excel, XL Comparator,…