Conditional Formatting - Duplicates: With an infinite amount of data out there, is anything really unique?
Most people only ever use conditional formatting a few ways. Some use it and the gradient effect it offers to make data more visual, others use data symbols to give that top 40 list a bit more punch, and a portion of Excel’s user base uses it to provide dynamic formatting based on user inputs.
But none of those are what we will be looking at here.
Screenshot of Microsoft Excel 365 - Conditional Formatting tool description
We are going to stay within the basic options that the conditional formatting drop-down menu offers us, and we are only going to be applying one colour scheme across our entire range. We are going to look at how we can identify all duplicates, and all our unique values, within a selected range.
While we do have the remove duplicates tool, sometimes it is best to first validate if these rows are indeed duplicates, or if they may be a case of mistaken identity. It is under these circumstances that we will gain the most benefit from the highlight duplicates settings.
Somewhat misleading by its name, but you can also use this prebuilt format to highlight unique values if those are more of interest to you.
Without further ado: Here is a step by step guide to using Conditional Formatting - Highlight Duplicate Values.
Step 1: Selecting a range
As simple as it sounds, it is very important to properly identify the range you want to analyze. Selecting multiple columns, for example, will actually evaluate if a value exists in either column when calculating if it is a duplicate or not. This can be problematic if you are looking at first and last names, where it can be possible that the first name of one individual matches the last name of another (while it may be a rare occurrence, it is also not impossible!)
Screenshot from Microsoft Excel 365 - Select entire data range and click on the Conditional Formatting > Duplicate Values tool button
Step 2: Selecting what to format
Under the Home tab, in the Styles group, you will find the conditional formatting option.
In the drop-down menu you will hover over the highlight cell rules option, and that will lead you to the duplicate values selection.
Once selected, the window which appears will ask you if you would prefer to highlight values which have duplicates (Duplicate), or which only have one occurrence within the range of selected cells (Unique).
Screenshot from Microsoft Excel 365 - Decide if you want to highlight duplicate or unique values
Step 3: Selecting your formatting
You will have the option, in the duplicate values window, to select what fill you want to use for the cells which fit the selected criteria. By default it will offer a light red (pink-ish) fill, with a dark red font colour. The drop-down menu here will present a few other potential combinations, and also gives you the option to define your own formatting.
Screenshot from Microsoft Excel 365 - Highlight duplicate values over multi column range
Step 4: Applying your conditional formatting
Simply enough, once you are happy with the options you have chosen, press on the OK button, and your formats will be applied!
Take this time to then verify that all the columns you wanted were indeed in the range of your conditional formatting
That is all it takes to successfully isolate your duplicate values!
From there, you can use advanced filters to view only your duplicate or unique values, and triage this data according to your needs.
Pair this guide with the one we have on creating unique identifiers and you now have a quick and practical way to identify duplicate data entries without causing permanent damage to your overall dataset.
Thanks for reading!