Although Microsoft Excel is a mathematical tool, its sheets are frequently used as databases to monitor inventory, generate sales reports, and compile long numeric data. But, a typical issue with so many data entries is the appearance of many duplicate rows because of a large set of data. It’s a bigger problem if you share this data with somebody and they have to remove all the entries one by one. Hence, it’s essential to learn how you can remove duplicates in Microsoft Excel.
Finding and eliminating duplicates in a spreadsheet with a low number of entries is as simple as choosing and deleting each new entry. However, manually removing all the duplicate entries one-by-one, can be a tedious task. But don’t worry! You can efficiently delete all the duplicate entries by multiple formulas or methods from any spreadsheet by following some easy steps and guides. So, we would like you to take a look at it now.
Also Read: Download Instagram For PC In 2 Minutes: 5 Apps For It
How To Find Duplicate Entries In Microsoft Excel
Duplicate data in a long spreadsheet can be hard to find. Finding, highlighting, and examining duplicates before eliminating them is the preferable option. That way, you can also find all the duplicate entries and choose to keep the ones you want to.
- First, choose the cell or range from which you wish to delete duplicates.
- You may then delete duplicate records by going to Data > Remove Duplicates and then checking or unchecking the columns you want to remove.
- A new box will appear. Check or uncheck the boxes you want to remove the data from.
You can select the data you want to eliminate. The dialogue box will display the number of duplicates to be deleted from the datasheet. It will also display the number of unique values. You can then select Ok to confirm your choice.
Also Read: How to Uninstall Microsoft Edge
How To Remove Duplicate Entries With Pivot Table
Pivot tables are intended for more than just data analysis. They can also be used to eliminate duplicate data. Instead of deleting duplicate values from your data using this technique, we will use a pivot table to present just the data set’s unique values. So let us take a look at all the steps, through which you can easily delete duplicates, using this method.
- You can create a pivot table depending on your data first. First, choose a single cell from your data or a custom set of data by dragging the mouse cursor.
- Then, under the Insert tab, choose PivotTable.
- Now, in the new Pivot table sheet, select all the data of the rows and columns you want to see.
- You must then modify the layout of the resultant pivot table to a tabular format. Go to the Design tab and pick Report Layout while the pivot table is chosen.
- The pivot table can be chosen in a tabular form. Then select Repeat All Item Labels.
- You must also remove the subtotals from all your entries. Select the Subtotals section and then select Do not show Subtotals.
All the entries and data will now be shown in tabular data, without any duplicates. You can also view all your data in a different style which will be much more detailed.
Also Read: How To Delete A Page In Microsoft Word
How To Remove Duplicate Entries Using Power Query
Power Query in Microsoft Excel is a data transformation capability tool. It enables you to import data from a variety of sources, as well as clean and alter your data. It is one of the simplest methods for removing duplicates from an excel sheet. You need to follow the instructions below to successfully perform this action.
- At first, select any cell from the datasheet and then go to the Data tab from the upper menu.
- From there select From Table/Range, which could be found in the top left corner.
- A dialogue window will open, requesting that you create a power query table. Set the value range and click OK.
- You can also choose which columns to delete duplicates from. You may pick several columns by holding down the Ctrl key.
- Select Remove Duplicates from the menu by right-clicking on the chosen column header.
- To eliminate duplicate records from the whole table, click the Remove Rows option in the upper corner of the data preview. Select the Remove Duplicates option.
After that, you can select Close and Load and the changed data will now be reflected on your worksheet.
Synopsis
Removing duplicates in Excel is a common chore for those who work with large datasets. When you merge separate tables, or when many persons have access to the same document, your spreadsheet may include duplicate entries. As a result, the data becomes a big trouble to use. The greater the dataset, the more likely it is to have duplicate records. It can be troublesome if they are not adequately discovered and treated. So, you can follow any of the above methods to delete duplicate entries in any type of Microsoft Excel spreadsheet.
Cashify helps you Sell phone online or Recycle old phone from your doorsteps. You can head to Cashify and know more.
