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…2023-02-15 20:24:19How To Remove Duplicates In Microsoft Excel
cashify
60060
1230900

How To Remove Duplicates In Microsoft Excel

Share:facebookinstagramwhatsapp
google-follow

You might have discovered multiple duplicate entries in any worksheet on Microsoft Excel. So, in this article, we will teach you how you can remove duplicates in Excel.

 - 
Updated: 15th Feb 2023, 20:24 IST
Table Of Contents
  • 1
    How To Find Duplicate Entries In Microsoft Excel
  • 2
    How To Remove Duplicate Entries With Pivot Table
  • 3
    How To Remove Duplicate Entries Using Power Query
  • 4
    Synopsis

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.


Comments

Featured Videos

  • Best Reel Downloader Apps For Instagram 2023
    The best reel downloader apps let you download reels in a jiffy. Instagram is definitely one of the best platforms to share photos and videos. And with the addition of reels, the social media platform has become even more engaging and exciting. Reels, basically, are short videos with audio and effect. Their short length is…
    14th Mar 2023
  • Living A Life Of Chaos With iPhone 14 Pro
    Since we began using the Apple iPhone 14 Pro, we’ve encountered various issues and most of which are related to iOS 16. Several issues that we’ve faced are related to notifications, Always-on -display, battery life and wallpaper. Until these issues are officially fixed, we will have to live with them. We tried to tackle some of…
    31st Oct 2022
  • OnePlus 9RT Alternatives To Buy In India Under Rs 45,000
    The OnePlus 9RT, a T upgrade, was out the previous year, although it’s only available in price-sensitive areas like China and India. Moreover, the OnePlus 9RT has superior cameras, including a 50MP primary sensor, a little more prominent display, a premium design, good battery/charging and a more effective Snapdragon 888 CPU. Overall, this smartphone looks…
    28th Oct 2022
  • iPhone 14 Pro Vs iPhone 13 Pro Vs iPhone 12 Pro Camera Comparison
    We’ll discuss the overall camera setup & quality of the iPhone 14 Pro Vs iPhone 13 Pro Vs iPhone 12 Pro in the article. All of these phones come with a triple-camera setup. However, you might find some significant upgrades in the iPhone 14 Pro. Let’s find out the details about the camera quality of…
    7th Oct 2022
  • Moto Edge 30 Pro Vs iQOO 9T Comparison: Which One To Buy?
    The year 2022 is known as the year of the launch of many flagship smartphones. However, there is quite a decent competition between the phones in the price segment of Rs 50,000, and even there are too many options to choose from. Here in this article, we’ll compare the Moto Edge 30 Pro Vs IQOO…
    6th Oct 2022

Highlights of the Story

  • Duplicate entries can be troublesome for most data entry editors.
  • In long spreadsheets, finding and removing duplicate entries is also a tedious task.
  • So, we have discussed three easy to use ways through which you can easily remove duplicate entries on Microsoft Excel.

    You May Also Read

    Vivo Mobile Price List
    Vivo Mobile Price List
    Vivo is one of the most active smartphone brands in India. Vivo smartphones are the best in terms of camera quality and design. They perform exceptionally well and have a fantastic user experience. The only problem with Vivo smartphones is that they do not have a fixed time for launching new devices. This has messed…