Say goodbye to duplicate data in Excel! Here you will find everything you need to know
Excel is one of the most useful office tools that you can use to work, especially if you handle a large amount of data and information. However, loading so much information may mean your spreadsheet needs cleaning, and duplicate data is one of the the main headaches in these cases.
Fortunately, this time we bring you one of the best Excel tricks, and it is none other than the way in which you can find and remove duplicate data within your spreadsheet, without dying trying.
How to find duplicate values in your excel sheet
Find the duplicate data in your excel sheet before removing it
The most recommended is identify duplicate data in an excel sheet before deleting it. This gives you an opportunity to review the duplicate values and make sure you don’t really need them. That being said, the easiest way to identify duplicate values is to use conditional formatting by following these steps:
- Select the cells that you want to check for duplicate data.
- On the Home tab, click on the option Conditional format inside the Styles section.
- Then select Rules for highlighting cells.
- click on Duplicate values.
- You will see a pop-up box appear that allows you to choose a style format for duplicate cells.
- Once finished, click the button Accept.
- This method will highlight duplicate data in each column or row of the document.
How to remove duplicate data in an Excel spreadsheet
You can remove duplicate data in your excel sheet with just a couple of clicks
Remove duplicate data in Excel too easy. On the Data tab, you’ll find an option to clear values that are repeated in the document, which is very useful because it allows you to select the columns you want to check. Let’s review in detail the steps you will have to follow:
- Select cells or data that you want to delete
- Go to the Data tab, and inside the section Data Tools click the option Remove duplicates.
- When you do so, you’ll see a pop-up window appear allowing you to choose which columns to check for duplicates.
- If you select all columns, only duplicate rows are removed.
- However, you don’t always have to choose all columns. For example, you can select a specific column to find duplicate data.
You can also filter duplicate values in Excel
Filters allow you to manipulate the amount of data displayed in your spreadsheet, without removing any of the values. You can find the filter options on the Data tab in the Sort & Filter group by completing the following steps:
- Choose the data What do you want to filter?
- Then, on the Data tab, click Advanced inside a section Sort and filter.
- In the popup window, enable the option to show Unique records only.
- to finish, click Accept.
- Filtering this data makes Excel hide any duplicate rows. But the duplicate data isn’t gone, it’s just hidden from view.
Don’t forget that the first step in cleaning your data is to identify any duplicate values, before proceeding to delete them forever.