How to Remove Duplicates from Excel

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 MobiSheets 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

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 MobiOffice 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.

4. Click on “Remove Duplicates“. Next, you will see the remove duplicates window.
   

5. There you will have to select which columns you would like to scan for duplicate data.
   

6. Click OK upon selection.

7. You should see another window pop up with the amount of deleted duplicate values.

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.

3. Hover over “Highlight Cell Rules“.
   

4. Click on “Duplicate Values“.

5. Choose a formatting option and then click OK.
   

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 MobiSheets?

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.

4. Select “Remove Duplicates“.

5. Choose the columns you want the duplicate values removed from.

6. If your data has headers, mark the “My data has headers” checkbox.
   Click OK.

7. 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 MobiSheets?

With MobiSheets 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.
   

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.

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“.
   

5. Click on “Remove duplicates“.
   

6. Select the data range you want cleansed from 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“.
   

3. Set rules for duplicates. To do that you should:
   

   3.1 Select “Custom formula is” under “Format cells if” from the side panel;
   3.2 Type the following formula “=COUNTIF($A$1:$A$100, A1) > 1” where $A$1:$A$100 is your actual data range.

4. Choose a formatting style by selecting the color you want the repeating values to be highlighted in.

5. 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“.
   

3. Tick the “Unique Records Only” checkbox.
   

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

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!