How to Sort Duplicates in Excel – 3 Methods

The following dataset contains  duplicate items and quantities in kg. Duplicates were sorted in alphabetical order from A to Z. Quantities were sorted in ascending order.

Sort Duplicates in Excel

 


Method 1 – Using the Sort Feature

Case 1: Sorting Duplicates in a Column

  • Select the cell range.
  • Go to the Data tab > Sort & Filter > Sort A to Z.selecting sort a to z
  • In the Sort Warning box:
    • Check Expand the selection.
    • Click Sort.

    sort warning

This is the output.

single column sort output

To sort all the columns:

  • Select the entire range.
  • Go to the Data tab > Sort & Filter > Sort.selecting sort
  • In the Sort dialog box, select:
    • Sort by: Items in the Column field > Sort On: Cell Values > Order: A to Z.
    • Click Add Level to insert a new level.

    sorting items

  • Select Quantity (kg) in Then by > Cell Values in Sort On > Smallest to Largest in Order.
  • Click OK.sorting quantity

This is the output.

Case 2: Sorting Duplicates in a Row

  • Select the range.
  • Go to the Data tab > Sort & Filter  > Sort.selecting sort for rows
  • In the Sort dialog box, select Options.
  • In the Sort Options dialog box, choose Sort left to right and click OK.sort options box
  • In the Sort dialog box, select:
    • Sort by: Row 6 in the Row field > Sort On: Cell Values > Order: A to Z.
    • Click Add Level.
    • Select Row 7 in Then by > Cell Values in Sort On > Smallest to Largest in Order.
    • Click OK.

    sorting rows

This is the output.

sorted output for rows

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

Using the SORTBY Function

The SORTBY function sorts a range or array of data based on the values in another range or array. You can also specify the sorting order.

Case 1: Sorting Duplicates in a Column

  • Select a blank cell.
  • Enter the formula: =SORTBY(B7:C23,B7:B23,1,C7:C23,1)


B7:B23 (Items) and C7:C23 (Quantity) are the cell ranges based on which the cell range B7:C23 is sorted.

1 implies ascending order. The formula sorts based on items, as B7:B23 is  before C7:C23.

  • Replace the cell ranges and insert 1 for ascending order or -1 for descending order.sortby function for column

Case 2: Sorting Duplicates in a Row

  • Select a blank cell.
  • Enter the formula: =SORTBY(C6:N7, C6:N6, 1, C7:N7, 1)
    C6:N6 (Items) and C7:N7 (Quantity) are the cell ranges based on which range C6:N7 is sorted. 1 implies ascending order. The formula sorts based on items as C6:N6 is before C7:N7. Quantities are sorted for the given items.

 

  • Replace the cell ranges and insert 1 for ascending order or -1 for descending order.sortby function for row

Read More: How to Sort Unique List in Excel 

Using the SORT Function

The SORT function sorts the contents of a range or array based on the order you specify. This function can only sort based on a single column.

  • Select a blank cell.
  • Use the formula: =SORT(B7:C23,1,1)
    B7:C23 is the cell range to be sorted in ascending order as the second argument is 1. The range is sorted based on the first column.
  • Replace B7:C23 with your cell range and specify the order and the column based.sort functionThis is the output.

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

Download Practice Workbook

Download the practice workbook here.

Frequently Asked Questions

How do I sort duplicates in reverse order in Excel?

Follow the same steps. Select cell range> Data tab > Sort & Filter group > Sort. In the Sort dialog box, select the column with duplicates, choose the order (descending), and add levels if necessary. Click OK to apply the reverse order.

How do I count duplicates in Excel?

Use the COUNTIF function to count duplicate values in a specific range. The formula is  =COUNTIF(Range, Criteria). Criteria refers to the value you want to count as a duplicate.

How do I find duplicates in Excel without deleting them?

  1. Select the cell range you want to check for duplicates.
  2. Go to the Home tab > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. The Duplicate Values dialog box will open with the Light Red Fill and Dark Red Text format selected by default.
  4. Click OK.

Related Articles


<< Go Back to Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo