This wikiHow teaches you how to use Excel to compare different sets of data, from two columns in the same table to two different Excel files.
method
Method 1 of 3: Compare two columns
Step 1. Select the first cell of an empty column
When comparing two columns on a worksheet, you will put the results in a blank column. Make sure you start on the same line.
If the two columns you want to compare e.g. B. start at A2 and B2, mark C2
Step 2. In the first line, type in the formula for the comparison
Type in the following formula that compares A2 and B2. Change the values of the cells if your columns start in different cells:
= IF (A2 = B2, "True", "Not true")
Step 3. Double-click the "Fill" field in the lower corner of the cell
This will apply the formula to the rest of the cells in the column and automatically adjust the values to fit.
Step 4. Search for True and False
These indicate whether the data in the content of the two cells match. This works for strings, dates, numbers and times. Note that it is case-insensitive (i.e. "ROT" and "red" match).
Method 2 of 3: Compare two workbooks side by side
Step 1. Open the first workbook you want to compare
You can use the Side by Side feature in Excel to view two different files in Excel at the same time. This has the added benefit of scrolling through both worksheets at the same time.
Step 2. Open the second workbook
You should now have two instances of Excel open.
Step 3. Click the View tab in both windows
Step 4. Click View Side by Side
You can find this in the ribbon in the "Window" area. Both workbooks are now displayed horizontally on the screen.
Step 5. Click Arrange All to change the orientation
Step 6. Click Vertical, then click OK
The workbooks change so that one is now on the left and the other on the right.
Step 7. Scroll one window to scroll both
When the Side by Side function is activated, both windows scroll synchronously. This makes it easy to find differences as you scroll through the worksheets.
You can deactivate this function by clicking on Synchronous Scrolling in the View tab
Method 3 of 3: Compare two worksheets for differences
Step 1. Open the workbook that contains the two worksheets you want to compare
For this comparison formula, both worksheets must be in the same workbook.
Step 2. Click the + button to create a new, blank worksheet
You'll see this button at the bottom of the screen, to the right of the open worksheets.
Step 3. Position the cursor in cell A1 of the new worksheet
Step 4. Enter the formula to compare
Type or copy the following formula into cell A1 of the new worksheet:
= IF (Sheet1! A1 Sheet2! A1, "Sheet1:" & Sheet1! A1 & "vs Sheet2:" & Sheet2! A1, "")
Step 5. Click the fill field in the corner of the cell
Step 6. Drag the fill field down
Pull it down as far as you can. If the table is e.g. B. goes to line 27, drag the fill field to this line.
Step 7. Drag the fill field to the right
After pulling it down, drag it to the right. For example, if your table B. to column Q, drag the fill field into this column.
Step 8. Look for differences in the cells that do not match
After dragging the fill field over the new worksheet, you will see that the cells fill where there are differences in the two worksheets. The cell shows the value of the cell in the first worksheet and the value of the same cell in the second worksheet.
