Flagging duplicate values in Excel

I was working with Sandra the other day on a project that involved an Excel spreadsheet and about 2400 lines of data. I had to check to see if I had any duplicate values in one of the columns. Since I’m not the sharpest tool in the drawer when it comes to Excel, I asked her if she could help me somehow find potential duplicates instead of me scanning through the list manually. I was duly embarrassed when she showed me just how simple that is… 🙂

Here I have a spreadsheet where I want to find duplicate random numbers. I highlight the Random Number column and select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values:

excel-duplicatevalues-20190423-1

The Duplicate Values dialog panel defaults to formatting cells that are Duplicates, and it will use Light Red Fill with Dark Red Text:

excel-duplicatevalues-20190423-2

If desired, I can also choose different formatting:

excel-duplicatevalues-20190423-3

When I click OK, my duplicate random number cells show up in the selected formatting color option:

excel-duplicatevalues-20190423-4

If I want to filter things down to *only* show the duplicate rows, I can click the dropdown menu next to the Random Numbers column header, and Filter by Cell Color:

excel-duplicatevalues-20190423-5

And there you have it!

excel-duplicatevalues-20190423-6

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s