This wikiHow teaches you how to compare two different data sets in Microsoft Excel, from two columns on the same spreadsheet to two different Excel files.
Step
Method 1 of 3: Comparing Two Columns
Step 1. Mark the first cell in the blank column
When you want to compare two columns in the same worksheet, you need to display the results of the comparison in one blank column. Make sure you select cells in the same row as the two columns you want to compare.
For example, if the column you want to compare is in cells A2 and B2, highlight cell C2
Step 2. Type the comparison formula into the first column
Type the formula below to compare the data in columns A2 and B2. Change the cell value if the column starts in a different cell:
=IF(A2=B2, "Match", "No match")
Step 3. Double-click the fill box in the bottom corner of the cell
After that, the formula will be applied to all the cells in the result column. Automatically, the cells in the result column will be adjusted to match or mismatch data in columns A2 and B2.
Step 4. Pay attention to the Match and No match labels
The label will indicate whether the contents of the cells in the two compared columns have the appropriate data or not. This also applies to strings, dates, numbers, and times. Keep in mind that case size doesn't matter (eg "RED" and "red" are treated as the same data).
Method 2 of 3: Comparing Two Files (Workbook) Side by Side
Step 1. Open the first Excel file or worksheet that you want to compare
You can use Excel's View Side by Side feature to view two different files on one screen at once. With this feature, you can slide worksheets at the same time.
Step 2. Open the second file
Now, there are two Excel files open on your computer.
Step 3. Click the View tab in any file window
Step 4. Click the View Side by Side button
It's in the Windows section of the. After that, both files will be displayed on the screen horizontally.
Step 5. Click Arrange All to change the orientation
Step 6. Click Vertical, then select OK
After that, the appearance of the worksheet will change so that one of the sheets is displayed on the left side, and the other sheet is displayed on the right side.
Step 7. Slide pages in one window to scroll pages in both windows
When the Side by Side feature is enabled, screen shifts will apply to both Excel windows. This way, you can easily see the difference in the data while sliding the worksheet.
You can disable this feature by clicking the “Synchronous Scrolling” button on the “View” tab
Method 3 of 3: Finding Differences in Two Sheets
Step 1. Open the file containing the two pages you want to compare
To use this comparison formula, both worksheets must be stored in the same file or worksheet.
Step 2. Click the + button to create a new page/sheet
You can see the button at the bottom of the screen, next to an existing/opened spreadsheet.
Step 3. Place the cursor on cell A1 in a new worksheet
Step 4. Enter the comparison formula
Type or copy the following formula into cell A1 in a new worksheet:
=IF(Sheet1!A1 Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
Step 5. Click and drag the fill box in the bottom corner of cell A1
Step 6. Drag the fill box downwards
Drag the cells as far as the cells that contain the data in the first sheet. For example, if the data in the first column is displayed up to the 27th row, drag the fill box until it reaches that row.
Step 7. Drag the fill box towards the right
After dragging it down, drag the fill box to the right until it matches the data cells on the first column sheet. For example, if the first column sheet contains data until it reaches column Q, drag the fill box on the new sheet until it reaches the same column.
Step 8. Find the data differences in the cells being compared
After you drag the fill box on a new sheet, the cells that contain the difference between the data on the two sheets being compared will be filled with the results of the comparison. Cells containing differences will display values or data from the first column sheet and data from the same cell in the second column sheet.
