Buzz
This Mytour article will guide you through comparing two different sets of data in Excel, from comparing two columns within the same worksheet to comparing two separate Excel files.
Steps
Comparing Two Columns
Select the first cell of an empty column. When comparing two columns in a worksheet, you will need to display the results in an empty column. Ensure that you start from the cell that aligns with the first cell of the two columns you're comparing.
- For example, if the columns to compare start at A2 and B2, select C2.
Enter a comparison formula for the first row. This formula will compare A2 and B2. Adjust the cell values if your columns start in different cells:
- =IF(A2=B2,"Match","No match")
Double-click the Fill box in the lower corner of the cell. This will apply the formula to the remaining cells in the column, automatically adjusting the corresponding values.
Find the results: Match and No match. These results indicate whether the data in the two cells match. This method works for text, dates, numbers, and times. Note that case sensitivity is not considered when comparing ("RED" and "red" are treated as matching).
Compare two workbooks side by side
Open the first workbook of the two that you wish to compare. You can use Excel's Side-by-Side View feature to view two different Excel files at once. You also have the added benefit of scrolling both sheets simultaneously.
Open the second workbook. At this point, you should have two Excel workbooks open on your computer.
Click on the View tab in any of the windows.
Click the View Side by Side option. You’ll find this under the Window section on the menu bar. Both workbooks will be displayed on the screen, stacked one on top of the other.
Click on the Arrange All option to adjust the layout.
Click on Vertical and then OK. The workbooks will then adjust so that one is positioned to the left and the other to the right.
Scroll one window to move both simultaneously. When the Split View mode is enabled, scrolling will affect both windows at the same time. This makes it easier to spot differences when scrolling through the spreadsheet vertically.
- You can exit this feature by clicking the Synchronous Scrolling button in the View tab.
Compare and find the differences between two spreadsheets
Open the workbook that contains the two spreadsheets you wish to compare. To use this comparison feature, both spreadsheets must be within the same file.
Click the + button to create a new blank spreadsheet. You will find it at the bottom of the screen, positioned to the right of the open spreadsheets.
Place the cursor in cell A1 of the new spreadsheet.
Enter the comparison formula. Please type or copy the following formula into cell A1 of your new spreadsheet:
- =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
Click and drag the Fill Information box at the corner of the cell.
Drag the Fill Information box downward. Pull it down to the row where the data in both spreadsheets conclude. For example, if your spreadsheet extends to Row 27, drag the box down to that row.
Drag the Fill Information box to the right. After dragging it downward, extend it to the right to cover the original spreadsheet. For instance, if your spreadsheet spans Column Q, drag the box to that column.
Identify discrepancies in non-matching cells. After extending the Fill Information box across the new spreadsheet, you will retrieve cells that indicate whether differences exist between the two spreadsheets. The cell will display the value from the first spreadsheet and the corresponding value from the second spreadsheet.
- For example, if A1 in Spreadsheet 1 contains "Apples" and A1 in Spreadsheet 2 contains "Oranges", A1 in Spreadsheet 3 will show "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.
