With hundreds of rows in today’s spreadsheets, it is virtually impossible for anyone to spot a duplicate value or duplicate row in Excel.
With Microsoft Excel 2007, and further enhancements in Excel 2016 and Excel 2019, it is now extremely easy to spot duplicates, and there are multiple ways to find them or eliminate them.
1. Spot a duplicate value, and get it to change its color automatically
In this simple method, we make use of Conditional Formatting, available on the Home Tab.
- Simply select the data range where you want to spot any duplicate values, and then click on Conditional Formatting.A drop down menu of choices appears. Select the first choice – Highlight Cell Rules. Then select the Last option -Duplicate Values.The short cut key combination is Alt +H L H D.
Select your favorite color to highlight the duplicate values.
Both the duplicate values are now highlighted.
There you are. A quick and nifty way to find the duplicate values in any column, quickly.
Want to Improve Your Excel Skills?
Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.
To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training!
We will talk about another nifty way to eliminate duplicates, once for all, in the next article.
You may find these resources useful:
Cheers,
Vinai Prakash
Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts Excel workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
Master Excel Lookup Functions like VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET
