Duplicate data on Excel is a common error when you use accounting functions, how to delete duplicate data on Excel . Please refer to the article on how to delete duplicate data on Excel presented in detail by the chief accountant at Le Anh Accounting Center here.
1. 5 ways to delete duplicate data on Excel
Method 1: Use the Remove Duplicate function to delete duplicate Excel data
On Excel you can use the pre-installed feature that quickly detects and deletes cases of duplicate data.
Step 1 : Then you need to highlight the area where you want to remove duplicate content, go to the Data tab and select the Remove Duplicates function.
Step 2 : After completing step 1, a new dialog box appears on the toolbar. On the interface of the new dialog box, select the filter feature to find duplicate columns. Select the filter feature and detect duplicate content.
Then select the column STT because the rows have different numbers, then select the full name title in the address column, select the Full name column or address you want to change, delete duplicate names and Address column. After completing these operations, click OK.
Step 3 : After completing the above steps, you will receive a notification of the number of duplicate content on Excel. Immediately after that, you will receive a notification of how many duplicate content values are found on Excel, and how many duplicate contents will be deleted from the Excel table.
How to delete duplicate content in Excel
After detecting 2 duplicate rows on Excel, it was reported that they were deleted from the table and left 2 blank rows at the end of the table. If you want to delete them, just right-click and select Delete.
Method 2: Select duplicate Excel data to delete manually
If you want to check duplicate content more carefully, you can use the Highlight operation. This will show the lines with changed content in a different color on Excel, helping you check all duplicate lines more carefully before deciding to delete or edit.
Step 1 : Highlight the content in the table to delete duplicate content ⇒ select the Home tab then ⇒ select Conditional Formatting ⇒ go to Highlight Cell Rules ⇒ Duplicate values…
Click Highlight Cell Rules ⇒ Duplicate values…
Step 2 : A dialog box appears on the toolbar. Duplicate content is marked with different colors to delete. Next, select the Highlight color to distinguish different colors in the table after clicking OK.
Step 3 : After editing the duplicate on Excel, you will see the final result displayed separately. The duplicate contents from the table will be highlighted with the selected color, then check the custom contents in the section if desired. Or in other cases, the duplicate rows will be deleted.
When practicing 2 ways to delete duplicate data on Excel, you will then detect rows with duplicate content to decide whether to delete or keep.
Method 3 : Use the UNIQUE function (Applies to Excel 365 and Excel 2019 and above)
The UNIQUE function allows filtering out unique values from an array of data, effectively eliminating duplicate entries.
Syntax: =UNIQUE(range; [by_column]; [only_once])
In there:
- Range: The data range to filter.
- [by_column] (optional): FALSE to filter by rows, TRUE to filter by columns.
- [only_once] (optional): FALSE to get all unique values, TRUE to get only values that appear once.
For example : If you have a list of employees in the range A3:A13 and want to get unique values, enter the formula =UNIQUE(A3:A13) in the desired cell (B3)
Image after using UNIQUE function »»
Method 4: Using Power Query
Power Query is a powerful tool in Excel for manipulating and transforming data, including the ability to remove duplicate rows.
Steps to follow:
- Select the data area to be processed and press Ctrl + T to create a table.
- Go to the Data tab on the toolbar, select From Table/Range in the Get & Transform Data group to open the Power Query Editor .
- In Power Query Editor , select the columns to check for duplicates.
- On the Home tab, select Remove Rows ⇒ Remove Duplicates .
- Once finished, select Close & Load to load the processed data back into Excel.
Method 5 : Use Advanced Filter
Advanced Filter allows you to filter and copy unique values to another location in your spreadsheet.
Steps to follow:
- Select the data range to filter.
- On the Data tab , select Advanced in the Sort & Filter group .
- In the Advanced Filter dialog box , select Copy to another location .
- Specify the data range at List range and the destination cell at Copy to .
- Check Unique records only .
- Click OK to finish.
2. NOTES when removing duplicate data on Excel
When deleting duplicate data in Excel, you need to note the following:
– Backup data before deleting: Before deleting duplicate data, you need to backup the original Excel file to avoid losing important data if an incident occurs (It may be due to your incorrect operation leading to data loss).
– Identify the column containing duplicate data : You need to identify the column to delete duplicate data on Excel. If you choose the wrong one without paying attention, it can lead to data loss.
– Double check before deleting : Before performing duplicate data deletion, double check to make sure that you are not deleting important data by mistake.
– Check the results after deleting : After removing duplicate data in excel, check the results to make sure that the deletion process is going as expected.
The above notes will help you delete duplicate data on Excel effectively and safely.
See more: 4 Ways to set password, lock Excel file to prevent editing, copying