Excel sheets are one of the most powerful tools that the Microsoft suite offers us, in them we can do really interesting things, thanks to its hundreds of tools that facilitate the work of calculating data.
However, the more complicated the sheet we’re working on becomes, it’s also easier to confuse the cells we need to select or create duplicates the wrong way . This time we are going to explain how you can do to eliminate these duplicate values quickly and easily , since doing it manually would take us too much time.
Index( )
- How do I find the values that are duplicates in my Excel quickly?
- Does merging and removing duplicates in Excel give the same result?
- How can I remove duplicates from my Excel table?
- for a single column
- For two or more columns
- In 1 or more rows
- With the help of a pivot table
How do I find the values that are duplicates in my Excel quickly?
If you want to find which are the duplicates present in your Excel sheet and then proceed to eliminate them, you only have to follow the following steps :
- Open the spreadsheetand specifically the page you need to fix.
- Press the Ctrl + Akeys , in this way you will be selecting all the content.
- Click on the ‘ Conditional Formatting‘ option
- Select the option ‘ Duplicate Values’, here you must establish a style with which you can highlight the duplicates that are found.
- Click ‘ OK‘.
Once you have followed all these steps, you will see how all the duplicates will be highlighted with the text format you chose. In this way you will not need to search for them manually, this option is especially useful when you handle large amounts of information in a sheet.
Does merging and removing duplicates in Excel give the same result?
Both removing duplicates and unifying duplicates give us exactly the same result , since what we want is to get rid of those double records that we have in our sheet and that can affect the result we are looking for.
How can I remove duplicates from my Excel table?
There are several ways to achieve this result , depending on whether the duplicates you want to remove are in a column or a row. Next, we will show you the easiest ways to do it.
for a single column
- Open your Excel sheetwhere you want to remove the duplicates.
- Select the data groupwithin the column that you want to filter.
- Click on the ‘ Data‘ tab in Excel’s tools menu.
- Click the ‘Data Tools ‘ button.
- Select the ‘ Remove Duplicates‘ option.
For two or more columns
- Open the spreadsheet you want to workon .
- Select the range of cellsyou want to analyze to remove duplicates.
- To select multiple columns, you’ll need to click on the top left of the column and then on the bottom right cell, all while holding down the ‘Shift’ key.
- Now click on the ‘ Data‘ button, then on ‘ Data Tools ‘.
- You will see a window with the ‘ Remove Duplicates‘ option , then you will see a popup window that will show you the option to select or deselect columns, there make sure you have the columns you want to debug selected.
- Click ‘ OK‘ this action will remove all existing duplicates in the columns you have selected.
In 1 or more rows
- Highlight the rows you want to process and press the ‘ Data‘ button, ‘ Data Tools ‘ and finally the remove duplicates option.
- A pop-up window will openwhere you must select which columns you want to include from each row you have chosen, select them and press the ‘Ok’ button.
- When you do, you will have successfully removed duplicatesfrom your selected rows.
With the help of a pivot table
Pivot tables are one of the tools that allow us to carry out long and repetitive processes within Excel, one of them is to detect and eliminate existing duplicates . We are going to explain how to use them for this purpose.
.Select the cells you want to process for duplicates, then press the ‘ Insert ‘ button, ‘ PivotTable ‘. When you do, you will see a dialog called ‘ Create PivotTable ‘.
You must press the ‘ OK ‘ button, at this point you only have to drag the field where the data you want to process is to the ‘ Row Labels ‘ section.
When you do this, the pivot table will automatically remove all duplicate data and only show you values that do not contain any other duplicates.
To finish, you just have to copy the results that are shown inside the pivot table in the ‘ Row Labels ‘ section and copy them to any section of your spreadsheet where you want to view them to continue working with them.
