How to Remove Duplicates in Excel

Whether you're a student working on random data or a young or even a senior professional, we all will encounter duplicate values that must 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 documents cannot contain data duplication because it will complicate the processing if you import or analyze the data tab using a built-in tool. It would be best if you only had 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.

Why Should You Remove Duplicates in Excel?

If this happens to the company's data, it will harm the company and 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 to dozens of repeat customers. Therefore, cleaning up all the duplicates in data cleansing (data cleaning) is essential to avoid errors. Excel provides users with many methods to remove duplicate cells or several duplicate rows or columns, such as using; the 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 threshold. Clicking and dragging also work fine. Click on the entrance on the top left, then 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 left of the green bar at the top of the Excel window. 4.      Click on the "Remove Duplicates" option. This option is 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 are adjacent to the selected column will be displayed and selected by default. However, you can click on the Select All option to choose more than one column shown or all. 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 same deals 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 the same deals without deleting them. You can also open an existing Excel document or a data with headers Excel file from the Open tab's Recently Opened Excel Documents section. 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 are working on data that has headers. Click the value on the top left corner if only one row is selected. Click the value at the top end if only one column is 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 lists. Next, click the single data cell on the far right to choose 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 dropdown menu. You may need to click the main button at the top of the Excel Microsoft window 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.

Using this method, we can use the sort and filter tool to remove duplicates in Excel! Select your dataset (including the header this time) in the Home Tab; in the Editing Group, click on Sort and Filter to remove duplicates and delete duplicates in Excel while viewing how many unique values remain from the sheet.

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 you set for the duplicate cells. It will only show the rows in that color (which also happens to be duplicates), and from here, you can select and delete the cells using the keyboard shortcut Ctrl + -. Go to the Sort and Filter dialog box, remove, and hit Clear Filter. What you're left with are unique values.

Method Three: Using Formulas (Spoiler Alert: This Works with Google Sheets too!)

If you're looking for a way to remove duplicates in Excel in a more customizable way, using functions and formulas is the way to go! Since we'll be using procedures, you can also use these techniques in Google Sheets! 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. Type down the formula and select your dataset. It will return the unique records in the dataset. Note that the list provided is from a spill function, so if there's any blockage underneath, it will produce a #SPILL! The 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 to 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 can take this further by delegating a Check Duplicate Row column 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 and organize it, you must remove duplicates. Excel can delete the duplicate rows, and a dialog box appears. The dialog box summarizes how many same 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: exact values are temporarily hidden when filtering out unique values. On the other hand, removing duplicate values ​​will permanently 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. Comparing the same 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, more columns, or the entire table. Other values outside the whole 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 from the table. You will be left with a spreadsheet in which the first occurrence of every row is hidden. I.e., only the duplicate rows are displayed. Since you are permanently deleting duplicate data sets 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 drawn.

Additional Notes:

If you want to recover data at any step during all the previous actions, click Undo (or press Ctrl + Z keys on your keyboard). Excel users may find similarities and confusion between filtering unique values ​​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.