Excel: Conditional Highlighting

Posted on 2015-02-09
Last Modified: 2016-02-11
I have a list of numbers, many of which are duplicates.

Is there a way to highlight the duplicates, with different colors for the different sets of unique numbers?

Thanks in advance. Example attached.

Question by:Cactus1994

Author Comment

ID: 40599755
I would prefer that no VBA or array solutions be offered ... I'm a relative rookie in those areas, and would rather use a formula. Thx.

Expert Comment

ID: 40599786
Excel already has built-in Conditional Formatting rules to highlight cells with duplicate values.  There is also a built-in Conditioning Formatting rule to highlight cells with unique values.  

But since you are requesting unique colors for all inputs, take a look at this website with helpful tips and tricks on finding duplicate values in Excel:

Author Comment

ID: 40599857
I'm very familiar with the conditional formatting feature in Excel. What my question concerns is how to conditionally format duplicate values, with different colors highlighting matching sets of values, as per the spreadsheet example I attached. Thanks for the link, but it doesn't quite address my question.

Expert Comment

ID: 40599872
If there are 500 matching sets, will you require 500 different colors?
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Author Comment

ID: 40599924
Lol. I'd only need 20-25 colors. But theoretically, if it works for 25, it should work for 500.

Accepted Solution

Let's Go earned 500 total points
ID: 40600003
The attached file starts to do what you want.  You will need to choose the colours manually.

1. Column B tests whether there are any duplicate values.

2. Column C then allocates a new colour for each cell in column A with a duplicate value (note that there is a different formula in the first row of column C.

3. I have then set conditional formatting for two colours.  You can repeat this process for the number of colours you need.

Expert Comment

ID: 40600217
Ha sorry. I really need to read things more carefully.
LVL 31

Expert Comment

by:Rob Henson
ID: 40600357
Will you be working with one set at a time?

How about setting the CF to highlight all cells that are equal to the current active cell?

Rob H

Author Closing Comment

ID: 40601845
Worked perfectly. Exactly what I needed.


Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 44
Query output to Excel producing error 7 34
Excel 6 18
Excel split data and output all combinations. 2 16
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now