# How To Remove Duplicates in Excel | Capterra | Capterra > Duplicate data in Excel can lead to miscalculations and data entry headaches. Learn how to quickly remove duplicate data in Excel across rows, columns, and sheets. Source: https://www.capterra.com/resources/how-to-remove-duplicates-in-excel --- IT & Software Development # How To Remove Duplicates in Excel By Sydney Chamberlain Sydney Chamberlain Sydney Chamberlain is a content writer specializing in business, marketing, and technology topics. With extensive experience in nonprofit organizations, she ... [See bio & all articles](https://www.capterra.com/resources/author/schamberlain/) Published July 14, 2023 5 min read Table of Contents - [What is a duplicate in Excel?](#what-is-a-duplicate-in-excel)  - [How to delete duplicates in Excel](#how-to-delete-duplicates-in-excel) - [Key considerations when removing duplicates in Excel](#key-considerations-when-removing-duplicates-in-excel) ## Learn how to quickly remove duplicate data in Excel across rows, columns, and sheets. Excel has been a staple in company toolkits for over 40 years. It serves a fundamental purpose and may be responsible for managing everything from your client list to product SKUs. Knowing how to remove duplicates in Excel is critical to ensuring seamless workflows and avoiding tedious (and potentially costly) errors in your business's database.  ## What is a duplicate in Excel?  A duplicate, also known as **duplicate data**, occurs when the same information is populated in multiple places within an Excel workbook. A duplicate could appear in multiple cells sequentially, but it may also be hidden further down a sheet, in another column, or in a different sheet within the workbook.  Duplicates are not always bad. For instance, you may have a sheet in your workbook that summarizes other sheets, causing there to be duplicates of values found elsewhere. In this case, you’d want to keep all versions of the data. However, duplicates are not always intentional or desirable, so it’s essential that you are able to find and highlight them for review.  There are also many scenarios in which duplicate data can have a direct impact on your business. For example, if you're using Excel for bookkeeping, duplicate data can lead to improper calculations. Or, if you're using Excel to store email subscribers, duplicate data can lead to the same person receiving your emails more than once, increasing the likelihood of your communications going to the spam folder. Unintentional duplicates can be caused by incorrect data preparation processes and data entry errors. Without unique identifiers and validation checks, it can be difficult to prevent duplicates in your documents. The good news? Learning how to remove duplicates in Excel is simple. ## How to delete duplicates in Excel What do you do in Excel to remove duplicates? Well, you can't simply take a shortcut and automatically delete all duplicate instances. Before you can remove duplicate data in Excel, you need to be able to find them. This means you need to start by highlighting all of the data and then you can review the duplicates and decide which ones you want to keep or remove.  ### Method 1: Find and remove duplicates through conditional formatting 1\. The first step is to highlight the cells you wish to check by holding your mouse button and dragging your cursor over them. If you want to select an entire sheet, press **Command+A** on Mac or **Ctrl+A** on Windows.  2\. Excel has a built-in tool to highlight duplicates. Activate this setting by navigating to **Home** > **Conditional Formatting** > **Highlight Cells Rules** > **Duplicate Values**. This will show a pop-up. _Navigate to Conditional Formatting and select Highlight Cells Rules, then Duplicate Values[\[1\]](#sources)_ 3\. Once the pop-up appears, leave **Duplicate** selected in the first box and then choose how you want them formatted in the second box. When you're ready, click **OK.** _Leave Duplicate selected and choose formatting[\[1\]](#sources)_ 4\. After highlighting the duplicates, make sure to review the findings before deleting any data. It's best to make a copy of your workbook at this stage before moving forward and deleting duplicates.  5\. Under **Data** find the **Remove Duplicates** option. This will open a dialog box. _In the Data pop up, select Remove Duplicates[\[1\]](#sources)_ 6\. In the pop-up, either check or uncheck the columns you wish to remove the duplicates from under **Columns**. For instance, if you do not want to remove any data from the January column, you would make sure to leave it unchecked. _Check or uncheck the columns you wish to remove the duplicates from under Columns[\[1\]](#sources)_ 7\. When you're ready, click **OK**. After all of the duplicates have been removed, Excel will show you the number of duplicate values removed and the number of unique values that remain. These counts may include empty cells, spaces, etc. ### Method 2: Find and remove duplicates with a shortcut  1\. Press **Ctrl**+**Space** on Windows or **Command+Space** on Mac to select the entire column your cursor is on or **Shift**+**Space** to select the entire row. 2\. Press **Alt** on Windows or **Option** on Mac to display the shortcuts. Press **A** to move to the **Data** tab and then press **M** to open the **Remove Duplicates** dialog box. 3\. If you have not selected all the data within your worksheet, you will see the **Remove Duplicates Warning**. Press the **Tab** key to select the **Remove Duplicates** button and then use your **Enter** key to continue. _In the Remove Duplicates Warning pop up, press the Tab key to select the_ _Remove Duplicates button, then use your Enter key to continue[\[2\]](#sources)_ 4\. Press the **Tab** key to select the **OK** button and then press the **Enter** key twice to remove all duplicates in your selection. _Press the Tab key to select the OK button, then press the Enter key twice to remove all duplicates in your selection[\[3\]](#sources)_ ## Key considerations when removing duplicates in Excel - **Remove trailing or leading spaces.** Even if two cells are duplicates, Excel can't find them if there are slight differences, like leading or trailing spaces. This means "John" and "John " will both remain in your workbook.   - **Standardize numbering styles**. Numerals and spelled-out numbers are not recognized as duplicate data in Excel. For instance, "One two three" is different from "123." On the other hand, Excel sees "10" and "10.00" as the same number, but the Remove Duplicates tool still treats them as unique values.  - **Audit your formula ranges**. If you set up a formula to check for duplicates in Excel, it is common for the worksheet to grow but the formula is not adjusted over time to check all of the rows. Use the auditing toolbar to make sure your formulas are looking at all of the data you intend to clean. Improve data quality in your Excel workbooks [Data entry tools](https://www.capterra.com/data-entry-software/) like Excel serve an essential purpose for businesses, but managing [spreadsheets](https://www.capterra.com/spreadsheet-software/) can be tedious at times. Now that you know how to remove duplicates in Excel, take the time to refresh your memory on the other best practices and how to make the most of the tool for [core responsibilities like accounting](https://www.capterra.com/resources/using-excel-accounting/). ## Capterra's 2026 Software Buying Trends Report ### Download our 2026 Software Buying Trends Report to see how successful software adopters avoid disappointment and how your business can, too. * * * Sources 1. [Find and remove duplicates](https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d), Microsoft Support 2. [How to use shortcut to remove duplicates in Excel column or row](https://www.extendoffice.com/documents/excel/4892-excel-shortcut-to-remove-duplicates.html), ExtendOffice 3. [Remove Duplicates in Excel List](https://www.contextures.com/excelremoveduplicatesnumbers.html), Contextures * * * ### Was this article helpful? * * * ## About the Author [### Sydney Chamberlain](https://www.capterra.com/resources/author/schamberlain/) Sydney Chamberlain is a content writer specializing in business, marketing, and technology topics. With extensive experience in nonprofit organizations, she is a frequent contributor to trusted business resources including Capterra. ### RELATED READING - [Bookkeeper vs. Accounts Payable Software: Which One Should You Choose?](https://www.capterra.com/resources/category-compare-bookkeeper-vs-accounts-payable-software/) - [How to Unlock Real AI Benefits in Sales and Marketing](https://www.capterra.com/resources/ai-benefits-sales-marketing-software/) - [How To Build the Right Project Management Tech Stack for Your Business](https://www.capterra.com/resources/project-management-tech-stack/) - [Why You Need Project Management Software With ADP Integration](https://www.capterra.com/resources/project-management-software-with-adp-integration/) - [5 Key Help Desk Software Features With Top Products That Offer Them](https://www.capterra.com/resources/help-desk-software-key-features/) - [Help Desk Software for IT Support: Features and Benefits 1,300+ Users Value](https://www.capterra.com/resources/it-help-desk-software-features-benefits/) - [AI in Talent Management Software: The Bridge Between Recruiting and Retention](https://www.capterra.com/resources/ai-talent-management-software/) - [Switching Accounting Software: Common Risks to Avoid in 2026](https://www.capterra.com/resources/switching-accounting-software-problems-risks/) - [Why Projects Fail, and What Actually Helps Teams Fix Them](https://www.capterra.com/resources/4-steps-to-completely-recover-from-project-failure/)