As you continue to grow your Excel skills, you’ll probably find yourself working with large amounts of data. Whether you’re using it as data points or searching for unique answers, it’s important to be able to find duplicates.
Like many features in Excel, you can get the job done multiple ways. Here are a few helpful steps.
How to find duplicates in Excel:
1. Select your range of cells. As with any Excel function, you have to decide which data you want to work with.
2. Head up to the Home tab and locate the Styles section. Now click on Conditional Formatting to open a dropdown menu.
3. Go to Highlight Cell Rules and select the Duplicate values option.
4. Now you should see a menu pop up. This is where you can decide how you want to format your duplicate cells. For our example, we’re going to color the duplicates green. Now click OK to identify the duplicates.
What about triplicates?
Maybe you’re looking for a way to pair up data points and you need to eliminate third points. You can expand the duplicate function to work with triplicates and above using some of the same steps. However, it’s slightly trickier as you’ll start to use the COUNTIF function as well. Here is how to do it:
1. Select your cell range and go back to Conditional Formatting in the Styles section.
2. Instead of going to Highlight Cell Rules, go down to New Rule below Icon Sets.
3. This will bring up a menu for you to build your rule. Now select Use a formula to determine which cells to format.
4. Enter your =COUNTIF formula, in this case, it’s =COUNTIF($A$1:$C$10,A1)=3 because we’re only looking for sets of exactly three. Click OK to get your results.
Excel formulas are cell-based, so A1 in the formula above indicates which cell is being checked. However, the formula is also applied to all other cells with the $A$1:$C$10 conditional formatting instead of retyping the formula individually.
What else can I do?
Now you know how to find duplicates in Excel, but what else can you do? There’s so much more to Excel than can fit into one article. You can learn how to do things like eliminating those duplicates once you find them too. If you’re ready to level up your skills you can check out the Complete Excel Pro Tips Certification Bundle on Tech Deals.
The hands-on learning kit packs six modules and 11 hours of content to help you sharpen your spreadsheet skills. You can work on everything from formatting to analytics and even PivotTables to develop from a beginner to an expert. The learning kit is also broken down into over 100 lectures to help you stay focused.
The Complete Excel Pro Tips Certification Bundle has a retail value of nearly $300, but you can get started for just $19.
Over 1,300 people have already enrolled to develop their skills, so what are you waiting for? Join them via the widget below.
Save $275 .00 $19 .00
Via Phandroid