Home > Microsoft Office > Microsoft Office How To

How to remove duplicates in Excel – 2 easy methods

Here are two simple methods for the task!
Andrew Kirkcaldy
Last Updated on

Reviewed By: Kevin Pocock

How to remove duplicates in Excel – 2 easy methods
PC Guide is reader-supported. When you buy through links on our site, we may earn an affiliate commission. Read More

Table of Contents

If you want to learn how to delete duplicates in Excel, then you’ve landed on the right page.

When dealing with a large dataset in Excel, you need to be careful as there can often be duplicate values inside the sheet. While some duplicate values are there on purpose, others can be accidentally added while sorting the sheet. But what can you really do about them? Is there a way to get rid of them quickly? Or will you have to delete the entire sheet?

This is where we come in. In this guide, we’ll walk you through the entire process of getting rid of duplicates in Excel.

Today's best deals

*Stock availability and pricing subject to change depending on retailer or outlet.

1

How to delete duplicates in Excel through the Data tab

Let’s take a look at how you can remove duplicates through the Data tab.

Step

1

Select the cells

Firstly, you need to select the range of cells that you want to remove the duplicates from.

Step

2

Go to the Data tab

Find the Data tab along the ribbon banner at the top of your screen. In the Data Tools group, select the button that’s named ‘Remove Duplicates’.

Screenshot of a Microsoft Excel interface displaying a spreadsheet with numerical data and an arrow pointing to the "delete duplicates" button on the toolbar.
Remove Duplicates button

Step

3

Select the columns

Now select one or more of your spreadsheet’s columns in the box that will appear on your screen. To select all of the columns, press the button labeled Select All. On the other hand, to unselect all of the columns, click Unselect All.

Dialog box for deleting duplicates in Excel with options to select columns named jenny, won, kiver, ivy, and cassie.
Remove Duplicates menu

Once done, click ‘OK, and Excel will remove all duplicates that were found. You will get notified about how many duplicates were found and removed from the sheet.

2

How to remove duplicates using Conditional Formatting

Alternatively, you can use Conditional Formatting to remove duplicates from Excel.

Step

1

Select the cells and go to Conditional Formatting

The first thing you need to do is select the cells that need to be checked. Then, go to the Home tab and click on Conditional Formatting.

Screenshot of an Excel spreadsheet showing a table with model names and numerical values, with a red arrow pointing to cell h7 where duplicates are deleted.
Conditional Formatting button

Step

2

Highlight the duplicate values

Now, click on Highlight Cell Rules and then select Duplicate Values.

Screenshot of Microsoft Excel showing the "conditional formatting" menu with arrows pointing to "new rule" and "manage rules" options, including the feature to delete duplicates.
Duplicate Values button

In the box that will pop up on the screen, select the formatting. For this example, we will select ‘Light Red Fill with Dark Red Text.’

Excel spreadsheet displaying models and test scores with a dialog box configured to delete duplicate values in the "ivy" column.
Formatting options

Step

3

Remove the duplicates

After you have selected a suitable format, Excel will highlight all the cells that contain duplicate values. You can then go through the sheet and remove the ones that you don’t need.

Spreadsheet displaying model types in column A and numerical data for five individuals across columns B to G, with some cells highlighted in pink to delete duplicates in Excel.
Duplicates highlighted in the sheet

Summary

Now that you know how to remove duplicates in Excel, you can sort your data without deleting the entire sheet. The first method can help you remove them automatically, while the second method is for those who want to remove only specific duplicate values.

If you want to learn more about Excel, then give these guides a read:

About the Author

Andrew Kirkcaldy

Andrew is one of three co-founders of BGFG, the parent company of PC Guide. A keen gamer and PC enthusiast, Andrew dabbles in a bit of writing sometimes - when he gets the chance to!