How to Remove Duplicates in ExcelFeb 28, 2022
A Pesky Affair
Whether you're a student working on random data, or a young or even a senior professional, we all will encounter some sort of duplicate value that will be need to be dealt with.
Duplicate data in Excel means having data or a record of the same thing in a table in Excel. In data processing in Excel, the required data tab or the unique records cannot contain data duplication because it will complicate the processing if you import data or analyze the data tab using a built-in tool. You need only the unique values.
In this article, we'll be talking about how we can remove duplicates in Excel; whether it be duplicate rows or duplicate columns, and multiple columns, using the different tools that you can readily find in Excel such as the ones in the Data Tools group, COUNTIF function, and conditional formatting to highlight duplicate rows.
If this happens to the company's data, it will have a negative impact on the company and on you. For example, if your company sends gifts to customers three times cost-effectively, it should only be enough once. This is because there is a duplication of data about the customer's name; of course, this would be detrimental if the gifts were delivered not just to one customer but dozens of repeat customers.
Therefore, it is essential to clean up all the duplicates in data cleansing (data cleaning) to avoid various errors. Excel provides the users with many methods to remove duplicate cells or several duplicate rows or columns, such as using; Remove Duplicates Command or Advanced Filter or deleting duplicate entire rows or cells or an entire table. For a more detailed explanation, let's look at the description below.
The First Method: Remove Duplicates Values
1. Double-click on the Excel document. This step will open the spreadsheet in Excel. You can also open an existing excel document from the recently opened excel documents section under the Open tab.
2. Select the data set. Do this by clicking on the upper entry, holding the Shift button, and clicking on the lower entry. Clicking and dragging also works fine. Click on the entry on the top left, then click on the value on the bottom right while holding the Shift button if you select one or more columns.
3. Click on the Data tab. This tab is to the left of the green bar at the top of the Excel window.
4. Click on the "Remove Duplicates" option. This option is located in the Data Tools section of the Data toolbar at the top of the Excel window. Clicking it will bring up a popup to select and deselect columns.
5. Make sure to select each column you want to edit. You will see one or three columns names or more (such as "Column A," "Column B," etc.) with select boxes next to them; Clicking on the selection box will deselect the column next to it. All the columns adjacent to the selected column will be displayed and selected by default. However, you can click on the Select All option to select more than one column displayed or all columns.
6. Click on the OK option. This step will remove duplicate values from the selected portion of the Excel spreadsheet. If you don't see any duplicates, select each combined column individually to find all the duplicate values in the excel spreadsheet.
The Second Method: Shading/Highlight Duplicate Rows
To highlight Duplicates you can use the conditional formatting tool. Follow the next few steps:
1. Double-click on the Excel document. This will open the spreadsheet in Excel, allowing you to find cells with duplicate values using conditional Formatting. This method is suitable for seeing only duplicate values without deleting them. You can also open an existing excel document or a data has headers excel file from the Recently Opened excel Documents section of the Open tab.
2. Click on the cell in the top left of your data set. This step will select the cell. Exclude the Column headers option (e.g., "date," "time," etc.) from the selection process, if you working on data has headers. Click the value on the top left corner if you only have one row selected. Click the value at the top end if you only have one column selected.
3. Hold down the Shift key on your keyboard and click on the cell in the lower right corner of the data preview. This will select all the original data between the top left and bottom right data list. Next, click on the single cell of data on the far right to select one entire row. Finally, click the bottom-most data cell if you select a single column.
4. Click Conditional Formatting. You'll find this option in the Styles section of the main bar, and clicking on Conditional Formatting will bring up a drop-down menu. You may need to click the main button at the top of the Excel Microsoft window first before Conditional Formatting to make this option appear.
5. Click the "Highlight Cells Rules" option. This step will bring up a popup.
6. Click on the Duplicate Values option. You'll find this option at the bottom of the popup, and clicking on it will select duplicate values in the selected data's entire range.
To remove duplicates in Excel using this method, we can use the sort and filter tool! Simply select your dataset (including the header this time), in the Home Tab, in the Editing Group, click on Sort and Filter.
Select Filter and a filter button appears on the header of the dataset.
This doesn't have a remove duplicates dialog box, but something pretty close. Click on the button to get the sort and filter dropdown. Select Filter by color and select the color that you set for the duplicate cells.
It will only show the rows that are in that color (which also coincidentally happens to be the duplicate rows) and from here, you can just select and delete the cells using the keyboard shortcut Ctrl + -.
Go back in the Sort and Filter dialog box and remove hit Clear Filter. What you're left with are the unique values.
If you're looking for a way remove duplicates in Excel in a more customizable way, using functions and formulas is the way to go! Since we'll be using formulas, you can use these techniques in Google Sheets as well! We'll be mainly talking about the UNIQUE function and the COUNTIF function.
The UNIQUE Function
The UNIQUE function returns the unique records in an array. Simply type down the formula and select your dataset.
It will return the unique records in the dataset. Do note that the list provided is from a spill function so if there's any blockage underneath, it will return a #SPILL! error which you can check out our video on right here.
The COUNTIF Function
The second method of using functions to check for duplicate rows is the COUNTIF function. When checking for duplicate rows, the COUNTIF function probably doesn't come into mind, but as a refresher, the COUNTIF function can count the instances for text strings.
In our sample table, we can add a Count Column. It's here where we'll place our function. Reference the entire dataset and remember to lock it using shortcut F4. For the criteria, select the first cell.
It will give off some numbers. Those with duplicate values are going to return values that are greater than 1. You cam take this a step further you can delegate a Check Duplicate Row column right next to it.
You can then use the steps in the second method to remove duplicate rows.
What is the Difference Between Filtering Unique Values and Removing Duplicate Values?
If you have a magnificently made dataset and need to classify this data and organize it so, you have to remove duplicates. Now, Excel can delete the duplicate rows, and a dialog box appears. The dialog box appears and summarizes how many duplicate values are found and removed and the count of unique values. Let's move forward and understand how to remove duplicates in Excel using the Advanced Filters option. Using the Advanced Filter Option The Advanced Filter option in Excel helps you organize your data.
Filtering unique values by the Advanced Filtering option and removing duplicate values are similar tasks because the target displays a list of outstanding entries. However, there is a significant difference: only duplicate values are temporarily hidden when filtering out unique values. On the other hand, removing duplicate values means permanently will delete duplicates from the Microsoft Excel spreadsheet.
A duplicate value is considered one so that all data in at least the first row match all the values in another row. Comparing duplicate values is based on the items that appear in all the cells, not the base value stored in that single cell. So, for example, if you have the exact date value in different cells, one formatted as "3/8/2006" and the other formatted as "March 8, 2006", the values , in this case, are unique records.
When filtering out unique values by removing duplicate information, the only effect is on the values in the range of cells or one or single column or more columns or the entire table. Other values outside the entire range of cells or the table will not be changed or moved. When duplicates are removed, the first occurrence of the value in the list is preserved, but other matching values are deleted in the whole table. You will be left with a spreadsheet in which the first occurrence of every row is hidden. I.e. only the duplicate all the rows are displayed.
Since you are permanently deleting duplicate data set in the removing duplicates process, it is recommended that you back up the original range of cells or the entire table to another worksheet or workbook before you remove duplicate values.
Some Issues With Removing Duplicates From Excel from an Outline or Grouped Data Tab
You cannot remove duplicate values from an outline or filter group data in the Unique Values or unique records Filter (containing subtotals). To remove duplicates, the outline and subtotals must be removed.
If you want to recover data at any step during all the previous steps, click Undo (or press Ctrl + Z keys on your keyboard). Excel users may find similarities and confusion between filtering unique values by using the advanced filter and removing duplicate values. Accordingly, attention should be paid to the differences between them. Instead of using the remove duplicates command or the advanced filter or the data tools group, you can use the ready-to-use templates provided by Simple Sheets to facilitate using Excel Microsoft software and save time and resources.
81% of companies across the use Microsoft Excel.
Mastering Excel can increase your earnings up to 4x over the course of your career!
Bottom Line: It pays to master Excel. Take our world-class Excel University and take your spreadsheet skills to the next level.
Get 5 FREE Excel Templates and Trainings
What email do you want us to send your access to?
We hate SPAM. We will never sell your information, for any reason.