How to Remove Duplicates from Excel

how to remove duplicates in excel
Tired of sifting through duplicate data in Excel? With a few simple tricks, you can quickly clean up your spreadsheets and keep only the unique gems that matter most.

Drowning in a sea of duplicate data? It is time to pull out the life raft and sail through your spreadsheet with ease.

Whether you want to keep one of each or give dupes the boot entirely, we have some crafty tricks that will make you a spreadsheet ninja in no time. Ready to slice through the clutter and make that data sparkle? Let’s get started!

How to remove duplicates in Excel using the UNIQUE function

Probably the simplest way to remove all duplicates from your spreadsheet is the UNIQUE function as it will scan your range and leave you with just the unique values.The coolest thing about this method is that it will work seamlessly on any spreadsheet software, be it Office Suite Sheets or Google Sheets.

To get this method to work you should:

  1. Select an empty column of your spreadsheet.
  2. Type in the UNIQUE function with your cell range. It should look like this “=UNIQUE(A2:C14)“. You can customize your cell range to your needs.
  3. Hit Enter.
  4. Your unique values will pop up in the empty column.

How to remove duplicate values using the remove duplicates feature

remove duplicates in excel

Another straightforward way to remove duplicates from your spreadsheet is by using the “Remove Duplicates feature. This is available in some spreadsheet software so you will have to make sure it is available in the one you are using. However, if you are using Excel or Office Suite you can easily find it. Before you start, though, you should know that this method will permanently erase all duplicates from your spreadsheet, so if you think you might need them at a later stage, you may want to back them up first.

Here is what you need to do to make it all work:

  1. Select any cell that contains data.
  2. Select “Data” from the ribbon above.
  3. Navigate to the “Data Tools” section.
    delete duplicates function
  4. Click on “Remove Duplicates“. Next, you will see the remove duplicates window.
    delete duplicates function
  5. There you will have to select which columns you would like to scan for duplicate data.
    delete duplicates function
  6. Click OK upon selection.
  7. You should see another window pop up with the amount of deleted duplicate values.
    delete duplicates function

How to find duplicates in Excel without deleting them

Sometimes you might need to find the repeating values, but keep them intact. If that’s the case, then you are in luck as most spreadsheet processors out there can highlight the duplicate entries without deleting the data. 

To do that in Excel you should:

  1. Pick the cells you want checked for duplicates. 
  2. Select “Conditional Formatting” from the home tab to open the dropdown menu.
    highlight duplicates in excel
  3. Hover over “Highlight Cell Rules“.
    highlight duplicates in Excel
  4. Click on “Duplicate Values“.
  5. Choose a formatting option and then click OK.
    highlight duplicates in Excel

If done correctly, this will automatically highlight all duplicates in the selected data tab of your excel spreadsheet.

How can you count the number of duplicates in Excel?

Excel also lets you simply count the number of repeating values in your spreadsheet using the COUNTIF formula. To do that you will first want to create a new column and name it “Combined“. 

Next you should:

  1. Use the “Combine Operator” to combine all columns if needed)
  2. Create another column where you will use the COUNTIF function. You can name it “Count” so it is easier to understand as you will use it to count the number of duplicate entries. The formula should read like this: “=COUNTIFS(C$2:C6,C2)“.

How to remove duplicates from OfficeSuite Sheets?

Just like with Excel, Sheets offers a remove duplicates functionality that is easily accessible. Unlike Excel, though, the built-in “Remove Duplicates” functionality gives you the freedom to remove the repeating values just the way you want so there is no need for a UNIQUE formula.

Here is how you can remove the duplicates you do not need by just a few simple steps:

  1. Open your spreadsheet.
  2. Select a column or a cell range.
  3. Click on “Data” in the main ribbon.
    remove duplicates in sheets
  4. Select “Remove Duplicates“.
  5. Choose the columns you want the duplicate values removed from.
    remove duplicates in sheets
  6. If your data has headers, mark the “My data has headers” checkbox.
    remove duplicates in sheets
  7. Click OK.
  8. And you are done. There will be no duplicate values in the selected data range of your spreadsheet.

How do you highlight repeating values in OfficeSuite Sheets?

With Sheets highlighting duplicate values in your data tab is just a few clicks away.

This is how you do it:

  1. Select the data range where you want to find duplicates and highlight them.
  2. Select “Conditional Formatting” in the ribbon.
  3. Click on “Highlight Cell Rules” in the newly appeared dropdown menu.
    highlight duplicates in sheets
  4. In the window that pops up, you will see two new dropdown menus and two cell range menus.
  5. To highlight duplicates, you will only need to select the function from the first dropdown menu that by default says Cell Value.
    highlight duplicates in sheets
  6. Now you can:
    6.1 Highlight the unique value of each set of duplicates by selecting “Unique“.
    6.2 Highlight all repeating values by selecting “Duplicate“. 
  7. Click OK.
  8. Voilà! All your repeating values will be highlighted.

How to remove duplicates from Google Sheets

Removing duplicates in Google Sheets is as simple as the previous two spreadsheet processors but requires you to take slightly different actions. As Google Docs is available not only on web, but also as a standalone app on both iOS and Android, it is worth mentioning that removing duplicates is only available on Google Sheets for web

If you have already opened your spreadsheet on Google Sheets on your PC or Mac, here is how you can remove all duplicate values:

  1. Open your spreadsheet in Google Docs.
  2. Choose the data range where you want the duplicates removed. Keep in mind that cells with identical values but different cases, formulas or formatting will be considered duplicates.
  3. Select “Data” from the top menu.
  4. Click on “Data cleanup“.
    google sheets remove duplicates
  5. Click on “Remove duplicates“.
    google sheets remove duplicates
  6. Select the data range you want cleansed from duplicates.
    google sheets remove duplicates
  7. Click “Remove duplicates“.
  8. You are done! There should be no repeating values left in your spreadsheet

Just like in Excel, in Google Docs you can also use the UNIQUE function to only leave the unique values in your spreadsheet. Here is how you can do it:

  1. Create a New Column or Sheet: Choose a place where you want the de-duplicated list to appear.f
  2. In a new cell, enter the formula =UNIQUE(range), replacing range with the actual range of your data.
    Example: If your data is in column A, you would use “=UNIQUE(A:A)“.
  3. Press Enter: The formula will generate a list of unique values from the specified range.
  4. Done! You will be left with just the unique values and every repeat of them will be gone.

Google Sheets can also help you automatically highlight all the duplicate values. 

To do that you should:

  1. Select the data range where you want your duplicates highlighted.
  2. Open “Conditional Formatting“.
    google sheets highlight
  3. Set rules for duplicates. To do that you should:
    highlighing duplicates
  4. 3.1 Select “Custom formula is” under “Format cells if” from the side panel;
    3.2 Type in your range
    3.3 Type the following formula “=COUNTIF (A:A, A1)>1” 
  5. Choose a formatting style by selecting the color you want the repeating values to be highlighted in.
  6. Click “Done“. 

More ways to remove duplicates from your spreadsheet

Using the Advanced Filter Option

Excel also gives you the opportunity to remove duplicates using an advanced filter. This could come in handy as there may be some cases when the UNIQUE formula or the “Remove Duplicates” function does not work the way you want them to.

Here is how you can get rid of repeating values in your data table:

  1. Select your data range.
  2. Go to “Data Tab” and select “Advanced Filter“.
    how to remove duplicates using advanced filter
  3. Tick the “Unique Records Only” checkbox.
    how to remove duplicates using advanced filter
  4. Preview the removed duplicates.

Using the Power Query Tool

The last way to remove duplicates on our list is by using the “Power Query Tool“. To do that you should:

  1. Go to the Data Tab and click on “Select From Table“.
  2. If you have added headers to your table you should check the “My table has headers” box.
  3. Next, you will see the Power Query Editor
  4. There you should go to the “Home tab” and select “Remove Duplicates“.
  5. Excel will remove all repeating values from your table and give you a preview.

Frequently Asked Questions

removing duplicates in excel

Can you remove duplicates in Excel without a formula?

Yes, you can. To do that you should select the data range where you want the duplicates removed, navigate to the Data ribbon and then click on “Remove Duplicates“.

How do you delete duplicates in Excel but keep one?

The most straightforward way to remove duplicates but leave just 1 unique value is with the UNIQUE formula. You should type it in an empty field with the range you want it to scan and hit Enter.

Does “remove duplicates” remove both values?

Yes, the “Remove Duplicates” does in fact remove all the values that show up in your worksheet more than once. To leave one value of the duplicate ones you will need to use the UNIQUE formula.

What is the best Excel formula to find duplicates?

To quickly find duplicates in Excel, use the COUNTIF formula. Here is how:

  1. In a new column, enter the formula “=COUNTIF(A:A, A2) > 1
    Replace A:A with the column range you are checking, and A2 with the first cell in that range.
  2. Drag the formula down to apply it to other cells.
  3. Cells where the formula returns TRUE are duplicates.

This formula highlights duplicates effectively, making it easy to identify and manage them in your data.

How do I remove duplicates from both sets in Excel sheets?

To remove duplicates from both sets in Excel, follow these steps:

  1. Combine both sets into a single column in a new sheet.
  2. Highlight the combined column and use the Remove Duplicates feature:
    • Go to the Data tab.
    • Click Remove Duplicates and make sure the correct column is selected.
    • Excel will keep the first occurrence and remove duplicates.
  3. Filter out the remaining duplicates:
    • Use the COUNTIF formula to find values appearing more than once in the combined list.
    • Example formula: =COUNTIF(A:A, A2) > 1 (adjust the range as needed).
    • Filter the column to show only TRUE values, then delete these rows.

Final Thoughts

Congratulations, you have just leveled up your data analysis game! With these techniques in your toolkit, duplicate data does not stand a chance. Whether you’re using the sleek UNIQUE function or diving into more advanced methods, you are now equipped to keep your spreadsheets clean, efficient, and headache-free. So the next time you see those pesky duplicates creeping in, you will know exactly what to do. Happy data cleaning!

You May Also Like
excel value error

What does the #VALUE error mean in excel?

Dealing with the #VALUE error in Excel? This common issue occurs when a formula can't process certain data. In this guide, we cover the main causes—like wrong data types or hidden characters—and show you how to fix them. Whether it’s a SUM, VLOOKUP, or IF formula, you'll find practical tips to resolve errors and improve your Excel skills!
How to Add Columns in Excel

How to Add Columns in Excel

Adding columns in Excel is key to organizing and analyzing data efficiently. This guide covers simple methods and advanced tips, ensuring you can manage your data with ease.
pivot table

What Is a Pivot Table and How to Create One?

Unlock the magic of Pivot Tables and turn your data chaos into clarity! Dive into this guide to discover what Pivot Tables are, why you need them, and how to create them in Excel, Google Sheets, and even Google Slides. Get ready to simplify your data and make smarter decisions with ease!
How Do You Delete a Page in Word?

How Do You Delete a Page in Word?

Struggling with unwanted pages in Word or PDFs? Our guide covers deleting blank pages, using paragraph marks, and the Go To dialog box. Learn to manage pages, troubleshoot issues, and recover deleted content. Plus, get tips on handling PDFs with free online tools and built-in editors. Say goodbye to extra pages with our expert advice!
How Many Pages is 1000 Words

How Many Pages is 1000 Words?

Learn what affects the page count for 1000 words, including handwriting vs. typing, fonts, margins, and more. Get answers to common writing questions and tips on writing faster and choosing the right word processor.