How to Find Duplicates in Excel (Plus Steps to Remove Them)
By Indeed Editorial Team
Published 27 April 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Excel is a program that can allow you to perform functions, such as data analysis and charting. It may be possible to duplicate values when handling a large data set. Learning how to find duplicates in Excel can help you reduce redundancy and manage your data more effectively. In this article, we explain the importance of finding duplicates, outline how to find duplicates and highlight the ways you can remove duplicates.
Importance of learning how to find duplicates in Excel
Learning how to find duplicates in Excel can help you ensure your data is accurate. In case you're performing calculations on Excel, having duplicate values can affect the accuracy of the results. Duplicates may also be valuable in a data set. For instance, if you have a column of 'Name' and another of 'Number of cars', you may decide to find the number of people with two cars by looking for duplicates in the 'Number of cars' column.
How to find duplicates in Excel
To find duplicates, you may use conditional formatting. Conditional formatting is an Excel tool that may allow you to format specific data based on your set rules. For instance, you can set Excel to format only the cells that display the number '2'. Here are two steps you can follow to find duplicates in Excel using conditional formatting:
1. Open the conditional formatting tool
To begin, you can open the Excel spreadsheet that contains your data. Next, you may open the 'Home' tab and highlight the spreadsheet. To select a large spreadsheet, you can click on the top left button between the 'A' column and the '1' row. Under the 'Home' tab, you can search for the 'Conditional formatting' button and click on it.
2. Highlight the duplicates using the tool
Once you click 'Conditional formatting', a drop-down menu appears and you can select 'Highlight cell rules' and then choose 'Duplicate values' on the next menu. Excel may display the duplicate cells, but you can change how Excel reveals them. You can navigate to the 'Format with' section to do this. Next, you can select how you may like Excel to display the duplicates by choosing from the drop-down list. For instance, you can set it so that all duplicates are in bold or in different text colours. Next, you can press 'OK' and Excel reveals the duplicates.
Excel duplicate data removal methods
Once you find duplicates, you may eliminate them from the data set to improve accuracy. Before you perform extensive edits to a data set, it may be essential to duplicate your data in another spreadsheet, as this ensures you still have your data intact. To create a duplicate of your original spreadsheet in the same workbook, you can right-click on the worksheet tab and choose the option 'Move or copy'.
Next, you can click on the checkbox next to 'Create a copy'. The 'Before sheet' option allows you to choose where to place the new copy. Here are instructions on how you can use different methods to remove duplicates on Excel:
How to remove duplicates after conditional formatting
Here are two steps you can follow to remove duplicates after using the 'Conditional formatting' tool:
1. Open the 'Filter' option
Once Excel highlights duplicate values, you can use the 'Filter by colour' option to remove duplicates. To add filters to your data set, you can navigate to the menu bar, select the 'Data' tab and choose the 'Filter' option under the 'Sort and filter' section. You may also press 'Ctrl+Shift+L' on the keyboard and Excel may open the 'Filter' option.
2. Remove duplicates
Once you open the 'Filter' option, you can click on 'Filter by colour' on the drop-down menu. You can then filter the data set by the colour you used to highlight duplicate values. Excel may now allow you to select the cells by clicking 'Alt+;' on the keyboard.
How to remove duplicates using the 'Advanced filter' command
Here are two steps you can follow to erase duplicates:
1. Open the advanced filter window
You can first select the 'Table design' option on the Excel menu bar. Under the 'Sort and filter' section, you can select the 'Advanced filter' option. This selection may open the 'Advanced filter' window.
2. Remove the duplicates
Under the 'Action' section of the 'Advanced filter window', you may see check boxes for 'Filter the list, in place' and 'Copy to another location'. If you select 'Filter the list, in place' Excel may hide the rows that have duplicates. If you choose 'Copy to another location', Excel may copy the data. In case you select 'Copy to another location', you can specify the location in the 'Copy to' section. You can also leave the 'Criteria range' blank and select the checkbox for 'Unique records only'. Once you press 'OK', Excel may remove the duplicate values.
How to remove duplicates using the 'Power query' tool
Here are two steps you can follow to use the 'Power query' tool:
1. Open the 'Power query' window
Before you use the tool, you may first select a range of cells. On the menu bar, select the 'Data' tab and you can navigate to the 'Get & transform' section. You can then select 'From table/range' and a 'Create table' dialogue box appears. Under the question, 'Where is the data for your table?' you can check to see the correct range of cells is on the text box. Next, you can click on 'OK' and the 'Power query editor' window may appear.
2. Remove the duplicates
The 'Power query' tool may allow you to remove duplicates in one or more columns or the whole table. To remove duplicates from columns, right-click on the column name on the 'Power query editor' window. To choose multiple columns, you can press the 'CTRL' button on the keyboard. Press 'OK' once you select the columns and Excel can remove the duplicates.
To remove duplicates from the entire table, you can navigate to the top left corner of the data preview and select the button. Next, choose the 'Remove duplicates' option. Once you click 'Close and load', your spreadsheet may appear without duplicates.
How to remove duplicates using a Pivot table
A pivot table is an Excel tool used to extract data from a spreadsheet. Here, a pivot table can extract non-duplicate values from a large data set and eliminate duplicate values. Here are two steps you can follow to use a pivot table:
1. Create a pivot table
To create a pivot table, select the range of data you may like to analyse. On the menu bar, click on the 'Insert' tab. Under the 'Tables' option, you can select 'Pivot table'. A dialogue box may appear and you can select 'OK' to create a pivot table.
2. Edit the table
Once you create the pivot table, you can add a field under the 'Rows' section of the table. It may be advisable to change the table layout to tabular to use the table effectively. To do this, you can navigate to the menu bar and select the 'Design' tab and then choose 'Report layout'.
Under this section, you can select 'Show in tabular form' and 'Repeat all item labels'. You can also remove subtotals from the table by selecting the 'Design' tab, then choosing 'Subtotals'. Next, you can choose 'Do not show subtotals'. The pivot table may display only the unique values on the data set and not duplicates.
How to remove duplicates using the 'Remove duplicates' button
Here are two steps you can follow to use the 'Remove duplicates' option:
1. Open the 'Remove duplicates' window
You may first highlight the relevant cells by clicking a single cell or highlighting the range of cells using the cursor. You can click on the 'Data' tab and navigate to the 'Data tools' section on the menu bar. Under this section, you can hover the cursor on each item until you discover the 'Remove duplicates' button. Once you click the button, a window may appear.
2. Remove the duplicates
You can select the checkbox next to 'My list has headers' in this pop-up window. This step may apply if the columns in the spreadsheet have headers. If not, you may leave the checkbox blank. You can then click on 'OK'. Excel may then show you a window with the number of duplicates that were present in the cells you selected. You can click on the 'OK' button to view your data without duplicates.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- What Is Succession Planning? A Comprehensive Guide
- What Is a Periodic Inventory System? (With Example)
- 7 Scrum Master Certifications That Can Help You Succeed
- What Is Presales and How Important Is It in a Company?
- 14 CMO Roles and Responsibilities (With Explanations)
- What Is Teamwork? (Plus Skills and Examples of Teamwork)
- Types of IT Engineer Skills (With Definition and Work Roles)
- HAZOP: Definition, Importance and Steps for Implementation
- What Is a Balanced Scorecard? (Definition and Benefits)
- Practical vs. Statistical Significance: Key Differences
- A Comprehensive Guide to SQL vs. MySQL (With Definitions)
- Top-Down vs. Bottom-Up Management: Meaning and Differences