• About
  • Contact
  • Cookie
  • Disclaimer
  • Privacy Policy
  • Change the purpose of use

Tnhelearning.edu.vn - Various useful general information portal

  • Photo
  • Bio
  • How To
  • Tech

How to Compare Data in Excel

February 11, 2024 by admin Category: How To

You are viewing the article How to Compare Data in Excel  at Tnhelearning.edu.vn you can quickly access the necessary information in the table of contents of the article below.

X

This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.

The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.

This article has been viewed 7,437 times.

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

  • Steps
    • Compare two columns
    • Side-by-side comparison of two workbooks
    • Compare, find the difference of two worksheets

Steps

Compare two columns

Image titled Compare Data in Excel Step 1

Image titled Compare Data in Excel Step 1

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Compare-Data-in-Excel-Step-1-Version-5.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 2

Image titled Compare Data in Excel Step 2

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f9/Compare-Data-in-Excel-Step-2-Version-5.jpg/v4-728px-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")
Image titled Compare Data in Excel Step 3

Image titled Compare Data in Excel Step 3

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/11/Compare-Data-in-Excel-Step-3-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 4

READ More:   How to Dispose of Processed Cooking Oil

Image titled Compare Data in Excel Step 4

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/93/Compare-Data-in-Excel-Step-4-Version-9.jpg/v4-728px-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

Image titled Compare Data in Excel Step 5

Image titled Compare Data in Excel Step 5

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f0/Compare-Data-in-Excel-Step-5-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 6

Image titled Compare Data in Excel Step 6

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8c/Compare-Data-in-Excel-Step-6-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 7

Image titled Compare Data in Excel Step 7

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/63/Compare-Data-in-Excel-Step-7-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 8

Image titled Compare Data in Excel Step 8

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8c/Compare-Data-in-Excel-Step-8-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 9

Image titled Compare Data in Excel Step 9

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4c/Compare-Data-in-Excel-Step-9-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 10

Image titled Compare Data in Excel Step 10

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bd/Compare-Data-in-Excel-Step-10-Version-9.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 11

Image titled Compare Data in Excel Step 11

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/10/Compare-Data-in-Excel-Step-11-Version-9.jpg/v4-728px-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.
READ More:   How to Cook Sticky Rice

Compare, find the difference of two worksheets

Image titled Compare Data in Excel Step 12

Image titled Compare Data in Excel Step 12

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Compare-Data-in-Excel-Step-12-Version-8.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 13

Image titled Compare Data in Excel Step 13

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a4/Compare-Data-in-Excel-Step-13-Version-8.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 14

Image titled Compare Data in Excel Step 14

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/89/Compare-Data-in-Excel-Step-14-Version-8.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 15

Image titled Compare Data in Excel Step 15

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fb/Compare-Data-in-Excel-Step-15-Version-7.jpg/v4-728px-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, "")
Image titled Compare Data in Excel Step 16

Image titled Compare Data in Excel Step 16

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a7/Compare-Data-in-Excel-Step-16-Version-2.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 17

Image titled Compare Data in Excel Step 17

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/95/Compare-Data-in-Excel-Step-17-Version-2.jpg/v4-728px-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.
Image titled Compare Data in Excel Step 18

Image titled Compare Data in Excel Step 18

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a6/Compare-Data-in-Excel-Step-18-Version-2.jpg/v4-728px-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.
  • Image titled Compare Data in Excel Step 19

    Image titled Compare Data in Excel Step 19

    {“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bf/Compare-Data-in-Excel-Step-19-Version-2.jpg/v4-728px-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
    READ More:   How to Deal with Pressure
  • X

    This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.

    The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.

    This article has been viewed 7,437 times.

    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.

    Thank you for reading this post How to Compare Data in Excel at Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.

    Related Search:

    Related Posts

    How to Create Curved Text in Photoshop
    How to fall asleep faster
    How to Install FBReader to Read eBooks

    Category: How To

    Previous Post: « Top 10 most attractive tourist destinations in Con Dao you cannot ignore
    Next Post: Top 17 nail colors for dark skin 2022 so that she can confidently welcome Tet »

    Copyright © 2025 · Tnhelearning.edu.vn - Useful Knowledge