Highlighting and even deleting duplicate information in Excel is straightforward however itemizing them is not as straightforward. Luckily, you should use Energy Question to simply show an inventory of duplicate values or information.


Picture: PixieMe/Shutterstock

Whether or not duplicate information are good or dangerous depends upon particular conventions that you simply decide, not Excel. For probably the most half, duplicate information is frequent as a result of many information repeat the identical values throughout the identical column. 

SEE: 83 Excel ideas each consumer ought to grasp (eBioPic)

Alternatively, a reproduction document—the place all the values are repeated—can spell hassle when reporting and analyzing the information set at giant. It is simple to seek out duplicates; you possibly can filter them out of a knowledge set or format them utilizing a conditional format rule. What you possibly can’t simply do in Excel is show solely the duplicate information. The excellent news is that doing so is simple utilizing Energy Question, so on this article, I am going to present you the way to use Energy Question to show an inventory of duplicates.

I am utilizing Microsoft 365, however Energy Question is accessible via 2010, as an add-in. Excel On-line would not absolutely help Energy Question, however you possibly can run queries. You may obtain the demonstration .xlsx file or work with your individual information. This text assumes you’ve primary Excel abilities, however even a newbie ought to have the ability to comply with the directions to success.

What’s Energy Question?

Energy Question enables you to hook up with overseas and native information after which enables you to remodel that information that so you should use it in Excel with out altering the supply information. It is simple to make use of however sadly, most customers are unfamiliar with it. Though we’re utilizing a easy function inside Energy Question, this text is not a primary introduction to Energy Question. Now, let’s truly use Energy Question to show duplicates.

Outline duplicate

To work effectively with duplicates, it’s good to outline what a reproduction is throughout the context of your information and the way you employ it. Any worth that happens greater than as soon as throughout the identical column is a reproduction. As an example, many information in a monitoring information set may need the identical supply date or buyer. These are duplicate values, and they’re frequent. 

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (eBioPic)

The time period can even outline a document the place each worth within the document is repeated in one other document. In different phrases, the complete document is a reproduction. As an example, two information that include the identical supply date, buyer and bill quantity may trigger an issue; you would not need to bill a shopper twice for a similar order. These are duplicate information, and you may normally need to delete certainly one of them. 

For our functions on this article, we’ll use Energy Question to show an inventory of each duplicate sorts: values repeated in the identical column and values repeated throughout all columns.

As you possibly can see in Determine A, the demonstration sheet comprises duplicate values. In such a small information set, duplicates aren’t troublesome to identify. Working with them is a distinct matter, particularly if the information set is giant. We even have one duplicate document.

Determine A


  This straightforward information set comprises duplicate values and not less than one duplicate document. 

The right way to listing duplicate values with Energy Question

Let’s use Energy Question to see values repeated within the columns. To take action, click on anyplace inside the information set, click on the Information tab, after which do the next:

  1. Within the Get & Remodel Information group, click on From Sheet. The ensuing window reveals the information in Energy Question (Determine B).
  2. Choose the column that you simply need to verify for duplicates. On this case, the date column is already chosen, so let’s use it.
  3. On the Residence tab (in Energy Question, not Excel), click on the Preserve Rows dropdown within the Scale back Rows group.
  4. Within the ensuing dropdown listing, select Preserve Duplicates.

Determine B


  Now the information is in Energy Question.

As you possibly can see in Determine C, the information set repeats two dates not less than as soon as. To see duplicates within the different columns, choose a column and repeat step 3. For instance, Determine D reveals the duplicate worth within the personnel column.

Determine C


  Two dates have duplicates. 

Determine D


  One worker is duplicated. 

Now you understand that not less than two columns repeat not less than one worth. For those who like, verify for duplicates in every column; you may discover that each column repeats a worth not less than as soon as.

What if you wish to see if there is a duplicate document? Let’s deal with that subsequent.

The right way to listing duplicate information with Energy Question in Excel

To rapidly recap, a reproduction document repeats values throughout all columns. To verify the information set for duplicate information, choose all the columns in Energy Question. To take action, maintain down the Shift key whilst you click on every column. Then, select Preserve Duplicates from the Preserve Rows dropdown. Determine E reveals the outcome. As you may need guessed already, the outcome is similar because the personnel question within the final part.

Determine E


  One document has a reproduction. 

Granted, it is a easy instance, and the outcomes had been straightforward to foretell. That will not at all times be true, particularly in a big information set.

The right way to use the outcomes of your Energy Question search

Seeing the information won’t be sufficient. Luckily, you possibly can return the outcomes to Excel as a sheet. Merely click on Shut & Load within the Shut group. Doing so will create a brand new sheet and save the ensuing information set, as proven in Determine F. As soon as the information is in Excel, you should use it as you’ll every other information set.

Determine F


  Save the outcomes to Excel.

This can be a easy use for Energy Question. Take a while to develop into aware of the completely different choices so you possibly can apply it to extra complicated duties. 

Additionally see

Leave a Reply