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 highlightduplicate 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.
If you found this tip helpful, please share it with your friends and colleagues.
To get more tips every week like this one...
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
Plus you also get my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.