If you’ve worked with data in Excel, you know duplicate data entries can wreak havoc on your calculations. The good news is it’s a quick fix to remove duplicates to keep your data clean and your sanity intact.

This article will show you how to remove duplicates in Excel. We’ll also explore how to remove duplicates in Google Spreadsheets and Apple Numbers.

Table of Contents

• How To Remove Duplicates in Excel
• How To Remove Duplicates in Google Sheets
• How To Remove Duplicates in Apple Numbers
• Conclusion

How To Remove Duplicates in Excel

Handling and removing duplicate data in Excel is easy once you get the hang of it. It’s an essential skill for preserving data integrity, enabling efficient data analysis and improving the manageability of your spreadsheet.

Step 1: Select the Data Range
Highlight the range of cells containing the data you want to remove duplicates from. If the data is in a table, click anywhere within it to select it.

Highlight the data range for deduplication in Excel. Source

Step 2: Open the Remove Duplicates Tool
Navigate to the Data tab in Excel and locate the Data Tools group. Click the Remove Duplicates button to access the tool.

Click the Remove Duplicates button.

Step 3: Choose Columns for Duplicate Identification
A dialogue box will appear, displaying a list of columns in your selected range or table. Select the columns you want to use for finding duplicates by checking the boxes next to them. If you’re going to use all columns, click Select All.

Select the data range for deduplication.

Step 4: Remove the Duplicates
Click the OK button in the dialogue box to initiate the duplicate removal process. Excel will search for duplicate rows based on the selected columns and remove them. A pop-up message will display a summary of the removed duplicates.

Click the OK button to initiate deduplication.

Step 5: Verify the Results
After removing the duplicates, inspect your data to ensure the process was successful. Compare the original dataset with the modified version to confirm that only duplicate rows have been eliminated.

Verify the data after duplicate entries have been removed.

How To Remove Duplicates in Google Sheets

Similar to Excel, you can remove those pesky duplicates from Google Sheets too. Here’s how:

Step 1: Select the Data Range
Highlight the range of cells containing the data you want to deduplicate. Make sure to include the headers if your data range has them.

Select the data range for deduplication.

Step 2: Access the Remove Duplicates Tool
With the desired data range selected, select Data from the toolbar’s top menu. From the dropdown menu, select Remove duplicates.

Select Remove Duplicates from the dropdown menu.

Step 3: Configure Duplicate Removal Settings
A dialogue box will appear, providing options to customize the duplicate removal process. If your selection contains headers, tick the box next to Data has header row. In the Columns section, select the columns you want to use for identifying duplicates.

Configure duplicate removal settings on Google Sheets.

Step 4: Initiate Duplicate Removal
Once you configure the settings, click the Remove duplicates button in the dialogue box. Google Sheets will scan the selected columns for duplicate rows and remove them accordingly.

Removing duplicates in Google Sheets.

Step 5: Review Results
After the data duplication removal process, a summary pop-up will appear, detailing the number of duplicates removed and the remaining unique rows. Examine your data to ensure the process succeeded and that only duplicates were removed.

Review results in Google Sheets once the duplicates are removed.

How To Remove Duplicates in Apple Numbers

Getting rid of duplicates in Apple Numbers is no sweat once you’ve got the proper know-how. It can speed up data processing and enhance crucial data management skills.

Step 1: Select the Data Range
First, highlight the range of cells containing the data you want to remove duplicates. Be sure to include headers if your data range has them.

Select the data range in Apple Numbers.

Step 2: Create a New Column for Unique Identifiers
Add a new column adjacent to your data range. In this column, you’ll create a formula that combines the values of each row to create a unique identifier for each record.

Creating a new column for unique identifiers in Apple Numbers.

Step 3: Enter the Unique Identifier Formula
In the new column’s first cell, enter the formula: =CONCATENATE(A2, B2, C2,…), replacing A2, B2, C2, and so on, with the cell references of the data you want to combine. Copy this formula down the entire column to generate unique identifiers for all rows.

Concatenating the data for deduplication in Apple Numbers.

Step 4: Sort the Data by Unique Identifiers
Select your entire data range, including the new column with unique identifiers. Click on the Organize button in the toolbar, then choose Sort Rows and sort by the unique identifier column. Set the sorting order to ascending.

Sort data in ascending order by the unique identifier.

Step 5: Identify and Remove Duplicates
In the row below your sorted data, use the following formula to identify duplicates: =IF(cell_above=cell_below, “Duplicate,” “Unique”), replacing cell_above and cell_below with the appropriate cell references. Copy this formula down the column. Filter the data by the Duplicate label and delete the duplicate rows.

Identify and remove duplicates.

Step 6: Clean Up the Spreadsheet
Once you have removed the duplicates, delete the temporary columns you created for unique identifiers and duplicate identification. Finally, review your data to ensure the process was successful.

Clean up the spreadsheet after removing duplicates.

Conclusion

​​Removing duplicate data entries maintains data quality and enables more accurate processing and analysis. However, it’s an ongoing task as you work with larger datasets and manage spreadsheet applications. Remember to set up a schedule to check and remove duplicate data entries routinely.

Related Articles

How to Merge Cells in Excel
How to Freeze a Row in Excel
How To Lock Cells In Excel
Excel For Business Intelligence: A Comprehensive Guide