How to Compare Data in Excel

This wikiHow teaches you how to compare two different sets of data in Excel, from two columns in the same spreadsheet to two independent Excel files.

Table of Contents

Compare two columns

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Compare-Data-in-Excel-Step-1-Version-5.jpg/v4-460px-Compare-Data- in-Excel-Step-1-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6e/Compare-Data-in-Excel-Step-1- Version-5.jpg/v4-728px-Compare-Data-in-Excel-Step-1-Version-5.jpg”,”smallWidth”:460,”smallHeight”:342,”bigWidth”:728,”bigHeight” :542,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the first cell of a blank column. When comparing two columns in a worksheet, you will have to display the results in a blank column. Make sure you start from the cell in the same row as the first cell of the two columns you want to compare.

For example, if the two columns you want to compare start at A2 and B2, select cell C2.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f9/Compare-Data-in-Excel-Step-2-Version-5.jpg/v4-460px-Compare-Data- in-Excel-Step-2-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f9/Compare-Data-in-Excel-Step-2- Version-5.jpg/v4-728px-Compare-Data-in-Excel-Step-2-Version-5.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :548,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the comparison formula for the first row. This formula will compare A2 and B2. Change the cell value if your column starts in other cells:

=IF(A2=B2,"Match","Doesn't match")

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/11/Compare-Data-in-Excel-Step-3-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-3-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/11/Compare-Data-in-Excel-Step-3- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-3-Version-9.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :547,”licensing”:”<div class=”mw-parser-output”></div>”}

Double-click the Fill box in the bottom corner of the box. As a result, the formula will be applied to all remaining cells in the column, automatically adjusting the value accordingly.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/93/Compare-Data-in-Excel-Step-4-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-4-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/93/Compare-Data-in-Excel-Step-4- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-4-Version-9.jpg”,”smallWidth”:460,”smallHeight”:349,”bigWidth”:728,”bigHeight” :553,”licensing”:”<div class=”mw-parser-output”></div>”}

Find Match and No Match . They will indicate whether the data in the two cells match or not. This method works with strings of characters, dates, numbers and times. Note that uppercase or lowercase letters are not considered when comparing (“RED” and “red” will be considered a match). ^{[1] X Research Source}

Side-by-side comparison of two workbooks

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f0/Compare-Data-in-Excel-Step-5-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-5-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f0/Compare-Data-in-Excel-Step-5- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-5-Version-9.jpg”,”smallWidth”:460,”smallHeight”:347,”bigWidth”:728,”bigHeight” :549,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the first of the two workbooks 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.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8c/Compare-Data-in-Excel-Step-6-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-6-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/8c/Compare-Data-in-Excel-Step-6- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-6-Version-9.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight” :551,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the second workbook. At this point, you should have two Excel workbooks open on your computer.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/63/Compare-Data-in-Excel-Step-7-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-7-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/63/Compare-Data-in-Excel-Step-7- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-7-Version-9.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click the View tab on any window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8c/Compare-Data-in-Excel-Step-8-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-8-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/8c/Compare-Data-in-Excel-Step-8- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-8-Version-9.jpg”,”smallWidth”:460,”smallHeight”:342,”bigWidth”:728,”bigHeight” :541,”licensing”:”<div class=”mw-parser-output”></div>”}

Click the Side by Side (View Side by Side) option. You will find this option in the Window area of the menu bar. Both workbooks will appear on the screen and are arranged from top to bottom.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4c/Compare-Data-in-Excel-Step-9-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-9-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4c/Compare-Data-in-Excel-Step-9- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-9-Version-9.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :548,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Arrange All to change the sort direction.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bd/Compare-Data-in-Excel-Step-10-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-10-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/bd/Compare-Data-in-Excel-Step-10- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-10-Version-9.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :548,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Vertical (Vertical) and then OK (OK). The workbook will change so that one is on the left and one on the right.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/10/Compare-Data-in-Excel-Step-11-Version-9.jpg/v4-460px-Compare-Data- in-Excel-Step-11-Version-9.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/10/Compare-Data-in-Excel-Step-11- Version-9.jpg/v4-728px-Compare-Data-in-Excel-Step-11-Version-9.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight” :545,”licensing”:”<div class=”mw-parser-output”></div>”}

Scroll one window to move both. When Parallel Display mode is enabled, scrolling takes effect on both windows simultaneously. This makes it easy to find the differences as you scroll across the spreadsheet.

You can exit this feature by clicking the Synchronous Scrpling button on the Display tab.

Compare, find the difference of two worksheets

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Compare-Data-in-Excel-Step-12-Version-8.jpg/v4-460px-Compare-Data- in-Excel-Step-12-Version-8.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6e/Compare-Data-in-Excel-Step-12- Version-8.jpg/v4-728px-Compare-Data-in-Excel-Step-12-Version-8.jpg”,”smallWidth”:460,”smallHeight”:347,”bigWidth”:728,”bigHeight” :549,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the workbook that contains the two worksheets that you want to compare. To use this comparison formula, both worksheets must be in the same file.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a4/Compare-Data-in-Excel-Step-13-Version-8.jpg/v4-460px-Compare-Data- in-Excel-Step-13-Version-8.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a4/Compare-Data-in-Excel-Step-13- Version-8.jpg/v4-728px-Compare-Data-in-Excel-Step-13-Version-8.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight” :550,”licensing”:”<div class=”mw-parser-output”></div>”}

Click the + button to create a blank spreadsheet. You’ll find it at the bottom of the screen, to the right of open spreadsheets.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/89/Compare-Data-in-Excel-Step-14-Version-8.jpg/v4-460px-Compare-Data- in-Excel-Step-14-Version-8.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/89/Compare-Data-in-Excel-Step-14- Version-8.jpg/v4-728px-Compare-Data-in-Excel-Step-14-Version-8.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}

Place the cursor in cell A1 in the new worksheet.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fb/Compare-Data-in-Excel-Step-15-Version-7.jpg/v4-460px-Compare-Data- in-Excel-Step-15-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/fb/Compare-Data-in-Excel-Step-15- Version-7.jpg/v4-728px-Compare-Data-in-Excel-Step-15-Version-7.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight” :551,”licensing”:”<div class=”mw-parser-output”></div>”}

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, "")

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a7/Compare-Data-in-Excel-Step-16-Version-2.jpg/v4-460px-Compare-Data- in-Excel-Step-16-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a7/Compare-Data-in-Excel-Step-16- Version-2.jpg/v4-728px-Compare-Data-in-Excel-Step-16-Version-2.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :547,”licensing”:”<div class=”mw-parser-output”></div>”}

Click and drag the Fill Info box in the corner of the cell.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/95/Compare-Data-in-Excel-Step-17-Version-2.jpg/v4-460px-Compare-Data- in-Excel-Step-17-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/95/Compare-Data-in-Excel-Step-17- Version-2.jpg/v4-728px-Compare-Data-in-Excel-Step-17-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}

Drag the Fill information box down. Scroll down to the row where the data in the two worksheets ends. For example, if your spreadsheet extends to Row 27, drag the box down to that row.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a6/Compare-Data-in-Excel-Step-18-Version-2.jpg/v4-460px-Compare-Data- in-Excel-Step-18-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a6/Compare-Data-in-Excel-Step-18- Version-2.jpg/v4-728px-Compare-Data-in-Excel-Step-18-Version-2.jpg”,”smallWidth”:460,”smallHeight”:343,”bigWidth”:728,”bigHeight” :543,”licensing”:”<div class=”mw-parser-output”></div>”}

Drag the Fill information box to the right. Once it’s dragged down, drag it to the right to cover the original worksheet. For example, if your spreadsheet extends to Column Q, drag the box to that column.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bf/Compare-Data-in-Excel-Step-19-Version-2.jpg/v4-460px-Compare-Data- in-Excel-Step-19-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/bf/Compare-Data-in-Excel-Step-19- Version-2.jpg/v4-728px-Compare-Data-in-Excel-Step-19-Version-2.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :547,”licensing”:”<div class=”mw-parser-output”></div>”}

Find the difference in the cells that do not match. After dragging the Fill Info box in the new worksheet, you’ll get cells with information that shows whether there are differences between the worksheets. The cell will display the value of the cell on the first worksheet and the value of the same cell on the second worksheet.

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. ^{[2] X Research Source}

Steps

Compare two columns

Side-by-side comparison of two workbooks

Compare, find the difference of two worksheets

Related Posts