Compare data in Excel

Table of contents:

Compare data in Excel
Compare data in Excel
Anonim

In this article you can read how you can compare different sets of data in Excel, from, for example, two columns within the same spreadsheet, to two different Excel files.

Steps

Method 1 of 3: Compare two columns

Compare Data in Excel Step 1

Step 1. Highlight the first cell of a blank column

To compare two columns, you will eventually have to put the results in a blank column. Make sure you start on the same row as the two columns you want to compare.

For example, if the two columns you want to compare start on A2 and B2, highlight C2

Compare Data in Excel Step 2

Step 2. Type the equation formula for the first row

For example, to compare A2 and B2, type the formula below. Change the values ​​of the cells if your columns start in other cells:

=IF(A2=B2, "Similarities", "Differences")

Compare Data in Excel Step 3

Step #3. Double click the fill handle at the bottom corner of the cell

This is how you apply the formula to the other cells in the column. The values ​​are then automatically adjusted to match each other.

Compare Data in Excel Step 4

Step #4. Look for the Similarities and Differences button

These buttons will indicate whether the contents of the two cells contain matching data. This works for series, dates, numbers and times. Note that capitalization is not taken into account (eg 'RED' and 'red' match).

Method 2 of 3: Compare two workbooks side by side

Compare Data in Excel Step 5

Step 1. Open the first workbook you want to compare

You can use the Show Side by Side feature in Excel to view two different Excel files on the screen at the same time. This has the added advantage that you can also scroll through both files at the same time.

Compare Data in Excel Step 6

Step #2. Open the second workbook

You should now see two open Excel files on your screen.

Compare Data in Excel Step 7

Step 3. Click the View tab in one of the windows

Compare Data in Excel Step 8

Step #4. Click on Show Side by Side

You'll find this option in the window section of the ribbon (also called the ribbon). Both workbooks now appear horizontally on the screen.

Compare Data in Excel Step 9

Step 5. Click Sort to adjust the orientation

Compare Data in Excel Step 10

Step 6. Click on Vertical and then OK

The worksheet will then be adjusted so that one worksheet is on the left half and the other on the right half.

Compare Data in Excel Step 11

Step 7. Scroll in one of the windows to scroll through both

When you have activated the Side by side function, the scrolling function between both windows will be synchronized. This allows you to easily find differences as you scroll through the worksheets.

You can turn this feature off by clicking the Synchronous Scrolling button in the View tab

Method 3 of 3: Find the differences between two Excel sheets

Compare Data in Excel Step 12

Step 1. Open the workbook containing the two sheets you want to compare

To use this comparison formula, both worksheets must be in the same workbook file.

Compare Data in Excel Step 13

Step 2. Click the plus sign + to create a new blank sheet

You will see the plus sign at the bottom right of the open sheets.

Compare Data in Excel Step 14

Step 3. Place your cursor on the new sheet in cell A1

Compare Data in Excel Step 15

Step 4. Enter the equation formula

Type or copy the formula below in cell A1 on the new sheet:

=IF(Sheet1!A1 Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

Compare Data in Excel Step 16

Step 5. Click the fill handle in the corner of the cell and drag it

Compare Data in Excel Step 17

Step 6. Drag the fill handle down

Drag it down as far as the first two sheets go. For example, if your spreadsheets continue up to Row 27, drag the fill handle down to that row.

Compare Data in Excel Step 18

Step 7. Drag the fill handle to the right

After dragging it down, drag the fill handle to the right so that it covers the original sheets. For example, if your spreadsheets extend to column Q, drag the fill handle to the column.

Compare Data in Excel Step 19

Step 8. Look for the differences in the cells that don't match

After you drag the fill handle over the new sheet you will see that wherever differences between cells are found, cells are being filled. The cell will show the value of the cell in the first sheet, and the value of the same cell on the second sheet.

Popular by topic