HomeHow ToHow to Compare Two Columns for Duplicate or Unique Excel Data

How to Compare Two Columns for Duplicate or Unique Excel Data



Not all the time we can perfectly input data on Microsoft Excel without errors. There are also instances when we add duplicate values on cells that should not be there. However, we may tend to overlook them, especially if we are dealing with a lot of columns and data in our worksheets.

Microsoft Excel can help you minimize errors and find duplicate values on your spreadsheet columns. You can compare two columns at the same time to highlight and check the values for differences and matches.

Image credit: Microsoft

Ways to Compare Two Microsoft Excel Columns for Duplicate/Unique Data

There are several methods and ways you can use to find matches and locate unique or duplicate data on your Microsoft Excel columns. You can check them below and find the format which is more suitable for you.

Method #1 – Create a New Formatting Rule

  • Open the Excel sheet where you want to compare two columns.
  • Click the Column Header of the first column to highlight all cells.
  • On the main menu, go to the Home tab.
  • Navigate to Conditional Formatting on the right side.
  • Select New Rule.
  • Under Rule Type, click Use a formula to determine which cells to format.
  • Scroll down to Format values where this formula is true.
  • Enter the following formula: =countif($B:$B, $A1), whereas A is the first column and B is the second column.
  • Click the Format button.
  • Next, format the duplicate cell.
  • Click OK.

Method #2 – Highlight Duplicate/Unique Values

  • On your Microsoft Excel sheet, highlight the cells or columns that you want to compare.
  • Go to the Home tab on the main menu.
  • Select Conditional Formatting.
  • Choose Highlight Cells Rules.
  • Click Duplicate Values.
  • Under Format cells, select Duplicate.
  • Next, choose the formatting that you want to highlight the duplicate cells beside values with.
  • Click OK.
  • If you want to highlight the unique data instead, Navigate to the Conditional Formatting.
  • Click Highlight Cells Rules.
  • Go to Duplicate Values.
  • Under Format cells, select Unique.
  • Set the formatting for the unique cells.
  • Click OK.

Method #3 – Enter a Formula

  • Insert a new column in between the two columns that you want to compare.
  • Enter the following formula: =IF(ISERROR(MATCH(A2,$C$2:$C$7,0)),”Not Duplicate”,”Duplicate”), whereas A is the first column and C is the second column, number 2 is the start of the column cell, and 7 is the last cell.
  • To remove the duplicate cells, highlight all the cells.
  • Go to the Data tab on the main menu.
  • Navigate to Sort & Filter.
  • Select Filter.
  • Check the box next to Duplicate.
  • Click OK.

That’s it! You have three options to identify and filter duplicate values to find matches and differences in your Microsoft Excel sheets data. Which method do you find the easiest? You can share your thoughts with us below.



Please enter your comment!
Please enter your name here

More From

Use Google Duo for Video Conferencing and Ditch Zoom

Did you know that you can now use Google Duo for videoconferencing? That's right! Despite the name Duo, which means two, Google's video calling...

How to Expand, Add & Install More SSD Internal Storage on PS5 Console

If you are the type of gamer who plays a lot of games, then you might need to add more storage space allocation on...

How to Turn Off or Disable Report Junk iMessage Feature for iPhone Spam Texts

With the release of iOS 16, several improvements have been made to the Messages or iMessage app on iPhone, including the ability to edit...

How to Cancel your Uber Eats Pass Subscription & Uber One Membership

Uber Eats is a food delivery app that allows users to order and pay for meals from local restaurants. The app has been extremely...

Recent Articles

Stay Connected