Easily Highlight Duplicate Values
by Francis Hayes (The Excel Addict)
In previous versions of Excel , it took a multi-step approach using a COUNTIF formula in Conditional Formatting to highlight duplicate values in your data. Beginning with Excel 2007, highlighting Duplicate Values got a whole lot easier.
You simply click Conditional Formatting on the Home tab, then point to Highlight Cell Rules, Duplicate Values and click OK.
If you'd prefer to highlight your cells with a different color, you can do that too or you can choose a completely different formatting (e.g. red + bold + strikethrough) by clicking the Custom... option at the bottom of the dropdown menu and choosing your formatting.
The great thing about using Conditional Formatting to highlight duplicates is that this is not just for a one-time highlighting of duplicate values. Doing this applies Conditional Formatting to the range of cells you select so that in the future, when more data is added, any duplicates will be automatically highlighted.
Note however that with a normal range of cells, this Conditional Formatting will be applied only to the range you select. If additional records are subsequently added to your data, they will not have the Conditional formatting applied to them. The best way to avoid this to format the range as an Excel table. From the Home tab, choose Format as Table. With an Excel table, as more data is added to the table, the formatting will be copied down from the cells above.
|Copyright © 2017
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.