Quick & Easy Ways to Find & Remove Duplicate Values in Excel

Download Article

Highlight duplicates in your spreadsheets with the Conditional Formatting tool

Written by Megaera Lorenz, PhD

Last Updated: February 16, 2026 Fact Checked

Download Article

Using Conditional Formatting

|

Using Remove Duplicates

|

Video

|

Q&A

|

Tips

|

Warnings

ARTICLE

VIDEO

When working with a Microsoft Excel spreadsheet with lots of data, you'll probably encounter duplicate entries. Microsoft Excel's Conditional Formatting feature shows you exactly where duplicates are, while the Remove Duplicates feature will delete them for you. Viewing and deleting duplicates ensures that your data and presentation are as accurate as possible.

Highlighting and Deleting Duplicates in Excel

Select the cell range you want to work with. In the Home tab, click Conditional Formatting > Highlight Cell Rules > Duplicate Values. Choose a highlight color and click OK.
To delete duplicates, click the Data tab and select the Remove Duplicates tool. Check the columns where you want to delete duplicates and click OK.

Method 1

Method 1 of 2:

Using Conditional Formatting

Download Article

1
Select the cells where you want to find duplicates. Click the cell in the upper left-hand corner of your data group. Hold down the ⇧ Shift key and click the final cell. Note that the final cell should be in the lower right-hand corner of your data group. This will select all of your data.
- You can do this in any order (e.g., click the lower right-hand box first, then highlight from there).
- You can also use Ctrl + A (Windows) or Command + A (Mac) to select the entire sheet.
The Conditional Formatting tool can be found in the Home tab/ribbon of the toolbar (if you’re using Excel for Windows, you’ll find it in the Styles section of the Home tab).^{[1]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}

Advertisement
Make sure your data is still highlighted when you do this.^{[2]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
^{[3]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
6
Choose “Duplicate” in the dialog box. By default, the option to highlight duplicate values should already be selected. If it’s not, click the dropdown that allows you to choose which type of values to highlight and choose “Duplicate.”
- If you instead wish to display all unique values, you can select "Unique" instead.
Select this option from the dropdown next to “values with” or “Format with” (depending on your version of Excel). The highlight color will designate duplicates. The default is light red with dark red text.^{[4]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
All duplicate values should be highlighted in the color you selected.
9
Use the COUNTIF function for more complex situations. The basic “Duplicate Values” rule will highlight any cells that contain duplicates. However, there might be situations where you want to adjust or limit what kinds of duplicates are highlighted. For example, you might want to highlight only the Nth and subsequent duplicates, highlight entire rows that contain duplicates, or find only duplicate rows. To do this:^{[5]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- Select the cells you want to work with, then go to Conditional Formatting > New Rule.
- In Windows, select Use a formula to determine which cells to format from the Select a Rule Type menu. On Mac, select Classic from the Style dropdown, then choose Use a formula to determine which cells to format from the next dropdown.
- Enter the COUNTIF formula in the formula field. The right way to format this will depend on the cell range you’re working with and what information you want to highlight. For detailed instructions on how to format this formula to get the results you want, see this guide from Microsoft.
  - There are numerous examples of COUNTIF formulas you can use for a variety of duplicate-finding situations here. Note that these examples need to be personalized to the specific worksheet and cell values you are working with.
- Choose your fill color and click OK.
10
Select a duplicate's cell and press Delete to delete it (optional). If you’re trying to get rid of unwanted or accidental duplicate values, you can do so by manually deleting them. If you have a huge dataset with lots of duplicates, it might be easier to use the Remove Duplicates tool.
- Once you delete a one-time duplicate, its partner value will lose its highlight.
11
Remove the highlighting when you’re done. Whether you deleted your duplicates or not, you may want to remove the highlight formatting before exiting the document. To do so:
- Select Conditional Formatting from the toolbar again.
- Click Clear Rules > Clear Rules from Entire Sheet.
  - If you have multiple sections of your spreadsheet formatted, you can select a specific area and click Clear Rules from Selected Cells to remove their highlighting.
- Save your changes before closing the document.

Method 2

Method 2 of 2:

Using Excel's Remove Duplicates Feature

Download Article

1
Select the cells where you want to find and delete duplicates. Click the cell in the upper left-hand corner of your data group. Hold down the ⇧ Shift key and click the final cell. Note that the final cell should be in the lower right-hand corner of your data group. This will select all of your data.
- You can do this in any order (e.g., click the lower right-hand box first, then highlight from there).
- You can also use Ctrl + A (Windows) or Command + A (Mac) to select the entire sheet.
It’s in the toolbar at the top of Excel.^{[6]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
It’s the icon that looks like a column of three alternating blue and white cells with a red X at the bottom. If you’re in Excel for Windows, this will be in the section labeled “Data Tools.” In Excel for Mac, the sections are unlabeled, but you’ll see it near the right end of the toolbar. Clicking this icon will bring up a customization window.^{[7]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
4
Select the types of data you want to delete. To delete all duplicates, check the box next to “Select All.” If you only want to delete duplicates from specific columns, tick the boxes next to the names of those columns (e.g., “Name,” “Date of Birth,” “Address”).^{[8]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- If your columns have headers, click the “My data has headers” or “My list has headers” option at the top. This will prompt the program to label the first entry in each column as a header, leaving them out of the deletion process.
5
Click OK to remove duplicates. When you are satisfied with your selections, click OK. This will automatically remove any duplicate values from your selection.
- If you get an error message saying your sheet doesn’t contain any duplicates, make sure you have the column(s) containing the duplicates selected in the Remove Duplicates dialog box.
- Don’t forget to save your changes when you’re done!

Community Q&A

Search

Add New Question

Question

How do I get rid of colored highlighted areas in Excel?

Community Answer

Press Ctrl-H, click Options, and then click the top Format button to search for colored cells (using the Fill tab). Leave the "Replace with" field blank to delete the contents of cells with the format you specified.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 1 Helpful 4
Question

How can I tell if a motherboard or hard drive is bad in a PC?

Community Answer

Faulty hard drives will often have data that is said to be corrupted. Motherboards that are faulty result in hardware orientated failures.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 6 Helpful 0
Question

Can this method be used to find duplicates between several different sheets? If not, what method can I use to do that?

Kadriguler

Community Answer

The repeating data with count of repetitions can be found easily with Excel Vba.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 2 Helpful 0

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

wikiHow Video: How to Find & Delete Duplicates in Microsoft Excel (Fast & Easy)

Watch

Tips

You can also identify duplicate values by installing a third-party add-in utility. Some of these utilities enhance Excel's conditional formatting feature to enable you to use multiple colors to identify duplicate values.

Thanks
Helpful 0 Not Helpful 0
Deleting your duplicates comes in handy when reviewing attendance lists, address directories, or similar documents.

Thanks
Helpful 0 Not Helpful 0