Abstract
Transcript
Excel's conditional formatting has a few special purpose formats that can be really useful in certain situations. One of these, is the ability to highlight duplicates. If you've ever faced a situation where you need to quickly identify duplicates in a large list or table, this is a perfect solution.
Here we have a table full of numbers, some of which are duplicates. As you can see, it's very difficult to see at a glance which numbers appear more than once. If we had all data in a simple list, we could sort the list to make it easier to see repeated values, but that's not a good option for a table.
Let's use conditional formatting to quickly highlight all duplicates. As with all conditional formats, we need to define a rule. First, select the data in the table. In the conditional formatting menu, in the Highlight Cells Rules category, you'll see an item called "Duplicate Values".
You won't see a live preview when you hover over this option. However, once you select Duplicate Values, Excel will build the preview on the worksheet, and you can try various format presets. Let's go with the red format preset for now.
With the conditional formatting rule in place, the duplicates are instantly visible. As always with conditional formats, all changes are evaluated in real time.
Now let's see what happens if we edit the rule and switch from duplicates to unique values.
As you can see, unique values is the mirror image of duplicate values. All unique values are formatted and duplicates are left alone.
