Helen Bradley - MS Office Tips, Tricks and Tutorials

I'm a lifestyle journalist and I've been writing about office productivity software for a long time. Here you'll find handy hints, tips, tricks, techniques and tutorials on using software as diverse as Excel, Word, PowerPoint, Outlook, Access and Publisher from Microsoft and other applications that I love. My publishing credits include PC Magazine, Windows XP mag, CNet, PC User mag, SmallbusinessComputing.com, Winplanet and Sydney Morning Herald.

Thursday, February 22, 2007

Excel - finding duplicates

Excel's conditional formatting option has lots of handy uses including highlighting cells that contain duplicate data.

To do this, highlight the range to investigate, for example, a series of entries in column C starting with cell C1. Now choose Format, Conditional Formatting, select Formula Is and enter this in the formula area:

=COUNTIF($C:$C,$C1)>1

Click Format and then select a pattern and color to use and click Ok.

Now duplicate entries in the column will be indicated with the shading you've provided. You can copy and paste this formatting down to other cells in column C to include them.

Labels: conditional formatting, Excel 2003, finding duplicates