Skip to main content

How to Remove Duplicates in Excel

Sometimes your Excel spreadsheet gets away from you and you might find that it's in need of a good clean up. A particularly common problem is to encounter duplicate information in multiple cells or rows. So here are some easy ways to find and delete duplicate data in Excel to help fix it.

How to Find Duplicate Values in Excel

It is good practice to find and highlight duplicate values in Excel before you delete them. Deleting the values permanently removes them from the Excel spreadsheet, so highlighting them first gives you an opportunity to review the duplicates and make sure you don't need them.

In our example, we will use the following spreadsheet:

Our fictional user is tracking all the fountain pens she has inked up. The spreadsheet contains information about the pen and the color of ink currently in the pen. But, she suspects that she accidentally entered some of her pens more than once.

The easiest way to identify duplicate values is to use conditional formatting. To highlight duplicate values using conditional formatting follow these steps:

  1. Select the cells you want to check for duplicates.
  2. Under the Home tab, click on Conditional Formatting in the Styles group.
  3. Select Highlight Cells Rules > Duplicate Values.
    how to highlight duplicate values in a spreadsheet
  4. A popup box appears and allows you to choose a style format for the duplicate cells.
  5. Once finished, click OK.

This method highlights the duplicates in each column or row. As you can see below, any company, model, or ink color listed more than once appears highlighted.

For this example, we need to look at the Model column to find duplicate pens. We have two Student, 41, and Preppy pens. The 41s have different inks, so they are likely different pens. But the Student and the Preppy pens might be duplicates.

As you can see, this method isn't ideal.

It finds every single duplicate cell, but in this case we are only interested in finding duplicate rows. You can have many different pens from the same company with the same ink. But you are less likely to have more than one pen that is the same company, model, and ink color.

Related: Automatically Format Data in Excel Spreadsheets With Conditional Formatting

How to Highlight Duplicate Rows in Excel with a Custom Formula

Excel allows you to create custom conditional formatting with formulas. We can use this method to identify duplicate rows. Selecting this option is similar to how we selected the duplicate values above, except instead of selecting Highlight Cell Rules, select New Rule instead.

This brings up a popup menu. Set the Style to Classic, then open the next dropdown menu and choose Use a formula to determine which cells to format.

The formula we want to enter is:

COUNTIFS($A$2:$A$14,$A2,$B$2:$B$14,$B2,$C$2:$C$14,$C2)>1

Let's take a closer look at this formula.

Conditional formating formulas have to return an answer that is either true or false. If the answer is true, the formatting is applied. This equation is true when it counts more than one row with the same information.

To check the rows for duplicates, the formula first selects a column ($A$2:$A$14). We use the absolute location because we want all to use the same range of cells when evaluating rows for duplicates.

The next variable is the target that we are checking for a duplicate value ($A2). This time, the absolute location is used for the column, but not the row. This allows our formula to incrementally check each row.

We repeat this for each column in the row.

Once you enter the formula, don't forget to choose a formatting style. The default is no style. So, even if you do everything right, if you forget that step, you won't see the results.

Afterward, our table looked like this:

This method only highlights the entire rows that are duplicates. Once you've found all the duplicates in your Excel spreadsheet, you can decide if they need to be removed.

How to Remove Duplicates in Excel

Removing duplicates is easy in Excel. Under the Data tab, in the Data Tools group, you will find an option to Remove Duplicates. This tool is very handy because it allows you to select which columns you would like to check.

To use this tool, select your data and then click on Remove Duplicates.

When you do so, a popup appears that lets you choose which columns to check for duplicates. If you Select All columns, it only removes duplicate rows.

However, you do not have to choose all of the columns. For example, if I wanted to know which inks are currently in the pens, you could select Ink Colour and remove duplicate colors. This will leave the first pen that uses a new color and remove any subsequent entry.

Using Excel's Remove Duplicates tool automatically deletes the duplicate entries.

Related: How to Remove Blank Rows in Excel the Easy Way

If you only want to temporarily remove duplicate entries, but not delete them, then you might want to filter your data instead.

How to Filter Out Duplicates in Excel

Filters allow you to manipulate how much of your data is shown, without actually removing any of the values. You can find the filter options under the Data tab in the Sort & Filter group.

There are many different ways to filter data in Excel. The example below only shows how to filter out duplicate values:

  1. Select the data you would like to filter through.
  2. Click Advanced in the Sort & Filter Group.
  3. In the popup, enable the option to show Unique Records Only.
  4. Click OK.

Filtering this data makes Excel hide any duplicate rows. So, in our example, the duplicate Student and Preppy pens were hidden.

But the duplicate data is not gone, it is just hidden from view. This is why our table is still formatted according to the rule we created earlier, despite no duplicate pens appearing.

In fact, if you look along the row numbers, you can see where the two rows are hidden. The rows jump from seven to nine and jump again between 12 and 14. The data is not gone, it is just out of sight.

Reviewing How to Remove Duplicates

The first step in cleaning up your data is identifying any duplicates. Removing duplicates in Excel permanently deletes the data. Highlighting them gives you an opportunity to evaluate the duplicates first. And if you're interested in duplicate rows, you need to use conditional formatting formulas to find them.

And finally, if you don't want to delete your duplicate data, consider using a filter instead. This hides them from view without actually removing any duplicate data from Excel.

Comments

Popular posts from this blog

The Best 10 Social Media Platforms for Photographers to Flaunt Their Talent

Social media offers an excellent opportunity for photographers to connect with potential clients. In the digital era, it's a great asset. By showcasing your work on these networks, you can reach new audiences. Whether you are a professional or freelance photographer, the following social platforms will help you show off your work and get the right people to take notice... 1. Behance Behance is a classic portfolio publishing network that functions like a LinkedIn for creatives. Designed by Adobe, this is one of the best photography networking sites currently out there. The platform is ideal for sharing your portfolio and favorite images, allowing other Behance users to like and comment on your photos. By learning from their feedback and professional critiques, you can improve your work. The coolest feature of Behance is that it lets you find professional gig opportunities right on the platform. With your portfolio already available on the site, getting work becomes effortless.

The 6 Best Platforms for Sharing Your Digital Art Online

Whether you're looking for somewhere to host your digital art portfolio or simply want to share your latest artworks, it can be difficult to choose a website to upload to. Or at least, it definitely is more so than before, now that art websites aren't bubbling with as much excitement as they used to be. You know that each site has its pros and cons, but it's hard to figure out what those are unless you make an account and see for yourself. Don't worry if you don't have time for that—we've got your back. Here are the websites we recommend for sharing digital art, and why you might want to consider them. 1. Pixiv If you were around when the online art scene was ridiculously active, chances are that your art style is influenced by anime and/or manga in some way. Otaku culture began its slow sneak into mainstream media back then, and Pixiv is a great home for artists that fall in that category. Pixiv started as a small online community based in Japan, but has s

Snapchat Suspends Two Anonymous Messaging Apps Over Cyberbullying Claims

In light of a lawsuit that was filed earlier, two Snapchat apps, Yolo and LMK have been suspended by Snap. The apps allowed users to send anonymous messages on the platform. The Lawsuit Calls for an Immediate Ban of Yolo and LMK According to a LA Times report, the lawsuit was filed on behalf of Kristin Bride, the mother of a teen who committed suicide in 2020. The lawsuit alleges that Bride's son took his own life after being cyberbullied via Yolo and LMK. In addition to this, the lawsuit alleges that Yolo and LMK aren't doing enough to tackle cyberbullying, and have consequently violated consumer protection law as well as their own terms of service and policies. Both apps use Snap Kit, a set of tools that allows developers to directly connect to Snapchat for better integration features. Today the family of a 16-year-old Oregon boy who took his own life after being cyberbullied sued Snap and the makers of apps YOLO and LMK, alleging that the companies should be "h