Skip to main content

How to compare two columns and highlight when found in another column?

Author: Xiaoyang Last Modified: 2020-04-21

For example, I have a worksheet with List A and List B s which have some same values both in these two lists, now, I would like to compare these two columns and then highlight the values both in the two lists from the List A to get the following result. And this article, I will introduce some methods to deal with this task in Excel.

doc highlight duplicates another column 1

Highlight cells if duplicate in another column with Conditional Formatting

Highlight cells if duplicate in another column with Kutools for Excel


Highlight cells if duplicate in another column with Conditional Formatting

In Excel, we can apply the Conditional Formatting feature to highlight the same values when found in another column, please do with following steps:

1. Select the cells in Column A that you want to highlight the cells where the values exit in Column C as well.

2. Then click Home > Conditional Formatting > New Rule, in the New Formatting Rule dialog box, please click Use a formula to determine which cells to format from the Select a Rule Type section, and then enter this formula =MATCH(A2,$C$2:$C$12,0) into the Format values where this formula is true text box, see screenshot:

doc highlight duplicates another column 2

Note: In the above formula: A2 is the first cell of the column that you want to highlight the same values, C2:C12 is another column range which contains the duplicate values you want to be compared with.

3. Then click Format button to go to the Format Cells dialog box, and choose one color you like under the Fill tab, see screenshot:

doc highlight duplicates another column 3

4. Then click OK > OK to close the dialogs, and now, you can see the values both in List A and in List B are highlighted from List A, see screenshot:

doc highlight duplicates another column 4

Note: If you want to highlight the duplicate values in List B, you just need to select List B first, and swap the cell references in the formula as this: =MATCH(C2,$A$2:$A$15,0) in the New Formatting Rule dialog.


Compare two columns and highlight cells if duplicate in another column

If you need to compare two columns and highlight the cell values if duplicate in another column, the Kutools for Excel's Select Same & Different Cells feature can help you to finish this job quickly and easily.       Download and free trial Kutools for Excel Now!


Highlight cells if duplicate in another column with Kutools for Excel

If you are not skilled with the formulas in the Conditional Formatting, here, I can recommend a handy tool-Kutools for Excel, with its Select Same & Different Cells utility, you can quickly highlight the same value in another column without any formula.

After installing Kutools for Excel, please do as follows:

1. Click Kutools > Select > Select Same & Different Cells, see screenshot:

2. In the Select Same & Different Cells dialog box, please do the following operations:

(1.) Select the two ranges that you want to compare from the Find values in and According to boxes separately;

(2.) Select Each row under the Based on section;

(3.) Choose Same Values form the Find section;

(4.) Then specify a background color or font color that you want to highlight the values under the Processing of results.

doc highlight duplicates another column 6

3. After finishing the settings, please click Ok button, and a prompt box will pop out to remind you how many matching cells are selected and highlighted, click OK to close the dialogs, then the duplicates values are kept selected and highlighted in List A, see screenshot:

doc highlight duplicates another column 7

Note: To highlight the duplicate values in List B, please switch the ranges from the Find values in and According to in the dialog box.

Download and free trial Kutools for Excel Now !


Demo: Highlight cells if duplicate in another column with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related articles:

How to check / find if value exists in another column?

How to compare two columns and list differences in Excel?

How to highlight cells if not in another column in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

 

Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This is close, but no cigar. I want to format column E (for example) if there are dups (>1 of it) in column Q. There will always be at least one entry in column Q matching a cell from column E, because column E is populated from the top 10 values of column Q. What I want to do is format the cell in column E if there are dups in column Q. In other words, if there are dups in column Q, then there is a tie for position in column E. I am hoping for a format of the cell in column E that warns me we have a tie, so when I announce the results, I will be made aware that we have a tie and adjust the results accordingly. I am already using a helper column to rank the top 10, but can't figure out how to format column E so that it only warns me when there are >1 entry in column Q matching the entry in column E. I have formatted column Q to highlight the dups, but would like to format column E as well. I don't like macros, so I'm trying to avoid them, and I'm cheap, so I don't want to buy anything.
This comment was minimized by the moderator on the site
Thank you for this tutorial. But how about Highlighting matching data on 2 different tables so that we that if I enter a number on table b, if it exists on table a it automatically highlights those cells? Thank you
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations