How to Sort Duplicates in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sorting is one of the most common tasks in the daily use of Excel. You need to perform various sorts; sort multiple columns, sort dates, sort data chronologically, and many more. Today we are going to show you how to sort duplicates in Excel. For this session, we are using Excel 2019, you can use your preferred version.


How to Sort Duplicates in Excel (2 Easy Ways)

First things first, let’s get to know about the workbook which is the base of today’s examples.

Here we have a dataset of several items along with their quantity. There are duplicate items on purpose. Using this dataset we will see how to sort duplicates in Excel.

Dataset how to sort duplicates in excel

Note that this is simply dummy data to keep things straightforward. In real-life scenarios, you may encounter a much more complex and bigger dataset.


1. Sort Duplicates in Columns

Let’s first see how to sort duplicates in columns.

Steps:

  • To sort duplicates in columns we first need to introduce a new column: Counter (we have named it Counter, you can name it as you wish).
  • In this column, we will count the number of times each item occurs in the Items We are going to use the COUNTIF function to find the occurrence numbers.

  • The COUNTIF function counts cells in a range that meets a single condition.
  • Here is the formula that will be
=COUNTIF($B$5:$B$22,B5)

Sort Duplicate Columns

  • Here, $B$5:$B$22 is the range to count, and B5 is the criteria. This will provide the total number of Apples within the Items.

  • To find the instances for the rest of the items, we will exercise the Excel AutoFill feature.

Sort Duplicate Columns

  • Now we can sort the data using this column.

1.1 Sort Ascending Order

Here, I will discuss how to sort duplicates in ascending order.

Steps:

  • To sort in ascending order select the Counter column and click A to Z in the Sort & Filter section from the Data tab.

  • Since only one column is selected from an entire table Excel will trigger a warning through the Sort Warning dialog box.
  • Unless you explicitly need to sort only the selected column, select Expand the selection and click Sort.

Sort columns in ascending order

  • You will find the sorted dataset. Notice the image below, the duplicate items are in ascending order.

Read More: How to Arrange Numbers in Ascending Order in Excel Using Formula


1.2 Sort Descending Order

Now, I will show how to sort duplicates in descending order.

Steps:

  • This time, to sort in descending order again select the Counter column and click Z to A from the Sort & Filter

Sort columns in descending order

  • Again, the selection of a single column from an entire table will lead to the Sort Warning dialog box. Select Expand the selection and click Sort.

  • The dataset will be sorted in descending order. See the image below

how to sort duplicates in excel

Read More: How to Sort Unique List in Excel 


2. Sort Duplicates in Rows

Now I will show how to sort duplicates in rows. The approach will be similar to the previous method.

Steps:

  • We can sort duplicate values present within rows. To show you examples we have rearranged the data within rows. Our approach will be similar to the column sort, we will introduce a Counter row here.

  • We will fill up the Counter row using the COUNTIF function and the formula will be the following one
=COUNTIF($C$4:$L$4,C4)

how to sort duplicates in excel

  • Here $C$4:$L$4 is the range to count and C4 is the criteria. We have used absolute reference for the range to keep this unchanged. This will provide the occurrence for the value in cell C4.

  • Exercise the AutoFill feature to complete the row.

Sort Rows how to sort duplicates in excel

  • Now using this row we will perform our sort operation.

2.1 Sort Ascending Order

First, I will show the method of sorting duplicates in ascending order.

Steps:

  • To sort rows perfectly we need to select the rows together and click Sort in the Sort & Filter section from the Data tab.

  • Now a Sort dialog box will pop up in front of you. From there select Options.

Sort Rows how to sort duplicates in excel

  • You will find another dialog box having two options; Sort top to bottom and Sort left to right.
  • Select Sort left to right and click OK.

  • Now you will find the rows listed in the Sort by drop-down box.
  • Here our Counter row is row number 6. Select Row 6 and click OK.

Sort Rows how to sort duplicates in excel

  • You will find the data sorted in ascending order.


2.2 Sort Descending Order

This time, I will sort the duplicates in descending order.

Steps:

  • From the Sort box, set the order from Largest to Smallest.
  • Then, click OK.

  • You will find the duplicate data in the row sorted in descending order.

Sort Rows in Descending Order

Read More: How to Sort Numbers with Letter Suffix in Excel


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Conclusion

That’s all for today. I have elucidated 2 ways to sort duplicates in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other ways to sort duplicates that we might have missed here.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo