Ever scan a list trying to spot duplicates with your eyes? You may even sort it in ascending order and then go down and look for matches sitting next to each other? Stop torturing yourself.
The 10-second fix
- Select your data column (let's say A2:A100)- Go to Conditional Formatting >> Highlight cell Rules >> Duplicate Values- Pick a color (a light red fill will do nicely here)- Click OK- Dunzo!
That is literally it! Every duplicate will light up for you, without confusion, without formulas, without eye strain, without missed dups.
When this may save your neck
- Spotting duplicate invoice numbers
- Cleaning email address lists
- Cleaning up your SKU inventory
- Spotting duplicate customer IDs
Pro Tip!
You want to do it the other way around, not find duplicates and instead look for values which have no duplicates? In the same steps written above, use “Unique” instead of “Duplicate” in the same menu!
Or use pivot tables and count the records
Quick. Dirty. Very efficient!
- Highlight your data- Insert >> Pivot Table- Put your desired column in the rows (left lower quadrant)- Put that same column in the data (right lower quadrant)- Done!
Now you have a table listing out all your values and if it found more than one instance of that record, its count will be greater than 1, so all you have to do is sort the count column in descending order.
Though, in the data options be sure to select count, in case your column is numeric, so it doesn’t start summing up the numbers for you by mistake.
Hey!
And have you ever tried to double click on that number inside a pivot table???? Don’t tell me I didn’t tell you I told you so! :-)
You’re welcome!
Need Help?
Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.
Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for newsletter updates: goarcherdynamics.com
Leave a Reply