Author:
John Stephens
Date Of Creation:
23 January 2021
Update Date:
1 July 2024
Content
This wikiHow tutorial will show you how to compare two different data sets in Excel, from two columns in the same workbook to two independent Excel files.
Steps
Method 1 of 3: Compare two columns
Select the first cell of an empty column. When comparing two columns in a spreadsheet, you will have to display the results in a blank column. Make sure you are starting from a cell in the same row as the first two columns you are comparing.
- For example, if two columns you want to compare start at A2 and B2, select cell C2.
Enter the comparison formula for the first row. This formula will compare A2 and B2. Change cell values if your column starts in other cells:- = IF (A2 = B2, "Match", "No match")
Double-click the Fill box in the bottom corner of the cell. As a result, the formula will be applied to all remaining cells in the column, automatically adjusting the corresponding value.
Find results Joint and Mismatched. They will show if the data in the two cells has a match. This works for strings, dates, numbers, and times. Note that capitalization or lower case is not considered when comparing ("RED" and "red" will be considered a match). advertisement
Method 2 of 3: Parallel comparison of two workbooks
Open the first workbook of the two that you want to compare. You can use the Parallel Display function included in Excel to view two different Excel files on the screen at the same time. You also have the added benefit of scrolling two pages at once.
Open the second workbook. For now, you should have two Excel workbooks open on your computer.
Click the card Display (View) on any window.
Click Options Parallel Display (View Side by Side). You will find this option in the Window area of the menu bar. Both workbooks are displayed on the screen and are arranged top to bottom.
Click All Sort (Arrange All) to change the arrangement direction.
Click Vertical (Vertical) and then Agree (OK). The workbook will change so that one is on the left and one is on the right.
Scroll one window to move both. When Parallel Display is enabled, scrolling is active in both windows simultaneously. This makes it easy to find differences as you scroll across the spreadsheet.
- You can exit this feature by clicking the Synchronous Scrolling button in the Display tab.
Method 3 of 3: Compare, find differences of two worksheets
Open the workbook containing the two worksheets that you want to compare. To use this comparison formula, both worksheets must be in the same file.
Click the button + to create a blank spreadsheet. You'll find it in the lower part of the screen, to the right of your open spreadsheets.
Place the cursor in cell A1 in the new worksheet.
Enter the comparison formula. Type or copy the following formula into cell A1 in your new spreadsheet:
- = IF (Sheet1! A1 <> Sheet2! A1, "Sheet1:" & Sheet1! A1 & "vs Sheet2:" & Sheet2! A1, "")
Click and drag the Fill box in the corner of the cell.
Drag the Fill box down. Scroll down to the row where the data in the two worksheets ends. For example, if your spreadsheet spans Row 27, drag the box down to that row.
Drag the Fill box to the right. Once dragged down, drag it to the right to cover the original workbook. For example, if your spreadsheet extends to Column Q, drag the box to that column.
Find differences in cells that do not match. After dragging the Fill box in the new workbook, you will get cells with information that indicates whether there is a difference between the worksheets. The cell will display the value of the cell on the first sheet and the value of the same cell on the second sheet.
- For example, A1 in Worksheet 1 is "Apples" and A1 in Worksheet 2 is "Oranges". A1 in Worksheet 3 will display "Sheet1: Apples vs Sheet2: Oranges" when using this comparison formula.
