4 Simple Ways to Compare Spreadsheet Data in Microsoft Excel

PDF download Download Article
Quickly view spreadsheets and columns side-by-side
PDF download Download Article

Do you have two different columns or spreadsheets to compare in Excel? Whether you're looking for matching or unmatching sets of data, you can easily find what you need by using formulas or features built right into Excel. Here's how to compare data in Excel using your Windows or Mac computer.

Comparing Two Sets of Data in Excel

To compare two columns of data in Excel, enter the formula =IF(A2=B2,"Match","No match") into the first cell of a blank column, and then double-click the Fill box. To compare two workbooks, use the View Side by Side feature on the View tab or use Spreadsheet Compare on Professional/Enterprise editions.

Method 1
Method 1 of 4:

Comparing Columns for Matches or Differences

PDF download Download Article
  1. When comparing two columns in a worksheet, you'll be outputting your results into a blank column. Make sure you are starting on the same row as the two columns you're comparing.
    • For example, if the two columns you want to compare start on A2 and B2 , highlight C2 .
  2. Type the following formula, which will compare A2 and B2 . Change the cell values if your columns start on different cells:
    • =IF(A2=B2,"Match","No match")
    Advertisement
  3. This will apply the formula to the rest of the cells in the column, automatically adjusting the values to match.
  4. These will indicate whether the contents of the two cells had matching data. This will work for strings, dates, numbers, and times. Note that the case is not taken into consideration ("RED" and "red" will match).
    5. Advertisement
Method 2
Method 2 of 4:

Comparing Two Workbooks With View Side By Side

PDF download Download Article
  1. You can use the View Side by Side feature in Excel to view two different Excel files on the screen at the same time. This has the added benefit of scrolling both sheets at once.
  2. You should now have two instances of Excel open on your computer.
  3. This is the tab at the top of the page.
  4. You'll find this in the Window section of the ribbon. Both workbooks will appear on the screen, oriented horizontally or stacked vertically, depending on your screen.
  5. You can select Horizontal , Vertical , Cascade , or Tiled .
  6. When Side by Side is enabled, scrolling will be synchronized between both windows. This will allow you to easily look for differences as you scroll through the spreadsheets.
    • If the workbooks aren't scrolling at the same time, make sure you have Synchronous Scrolling enabled. This icon looks like a document icon with a blue up/down arrow.
    • If you don't want the workbooks to scroll at the same time, click this icon to disable it.
    7. Advertisement
Method 3
Method 3 of 4:

Comparing Two Workbooks With Spreadsheet Compare

PDF download Download Article
  1. 1
    Launch Spreadsheet Compare. This program is available for Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. [1]
    • If you don't see Spreadsheet Compare in your Start menu, try typing "spreadsheet compare" into the Windows search bar.
    • If you don't have this program, try another method.
  2. 2
    Click Home . This is at the top.
  3. 3
    Click Compare Files . You can find this on the Home tab.
  4. 4
    Click the blue folder icon and select the first Excel file. Use your device's file explorer to find the file. You can also enter a web address.
  5. 5
    Click the second folder icon and select the second Excel file. Use your device's file explorer to find the file. You can also enter a web address.
  6. 6
    Check the box for Select All . This is what you'll see in the workbook comparison. If you don't want to see everything, check the box next to the option(s) you want to see.
  7. 7
    Click OK . The comparison is run, and you'll see the two files in a two-pane grid.
    8. Advertisement
Method 4
Method 4 of 4:

Comparing Two Worksheets

PDF download Download Article
  1. To use this comparison formula, both sheets must be in the same workbook file.
  2. You'll see this at the bottom of the screen to the right of your open sheets.
  3. This is where you'll see the comparisons.
  4. Type or copy the following formula into A1 on your new sheet:
    • =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
    • If your sheets have different names, replace Sheet1 and Sheet2 with their respective names.
  5. This is the small square at the bottom-right corner of the cell.
  6. Drag it down as far down as the first two sheets go. For example, if your spreadsheets go down to Row 27, drag the Fill box down to that row.
  7. After dragging it down, drag it to the right to cover the original sheets. For example, if your spreadsheets go to Column Q, drag the Fill box to that column.
  8. After dragging the Fill box across the new sheet, you'll see cells fill wherever differences between the sheets were found. The cell will display the value of the cell in the first sheet and the value of the same cell in the second sheet.
    • For example, A1 in Sheet1 is "Apples," and A1 in Sheet2 is "Oranges." A1 in Sheet3 will display "Sheet1:Apples vs Sheet2:Oranges" when using this comparison formula.
    9. Advertisement

Community Q&A

Search
Add New Question
  • Question
    How do I find duplicate values in a row?
    Community Answer
    To find duplicate values simply use the Data --> Remove Duplicates feature and follow the prompts on the screen.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 10 Helpful 10
  • Question
    How do I subtract two rows?
    Community Answer
    You have to select the 2 rows {shift} and then right click on the now highlighted rows. A box will pop up with a list of actions. You should click on 'delete rows'.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 12 Helpful 2
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Use Macros in Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Apply Conditional Formatting in Excel
      How to
      Recover a Corrupt Excel File
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Unprotect an Excel Sheet
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      The Best Ways to Make & Format Lists in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Add Numbers & Cells in Excel: Complete Guide
      How to
      Password Protect a Microsoft Excel Spreadsheet
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/basic-tasks-in-spreadsheet-compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

      About This Article

      Article Summary X

      1. Highlight the first cell in a blank column.
      2. Type " =IF(A2=B2,"Match","No match")".
      3. Drag the formula down to populate all cells in the column.
      4. Look for "Match" or "No match" in the results.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 1,867,418 times.

      Reader Success Stories

      • Max Ramirez

        Aug 21, 2017

        "Needed to compare a list of employee data with sheets containing different elements, but one sheet was 3 employees ..." more
        Rated this article:
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement