# 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.

## 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.
• In the Sort Warning box:
• Check Expand the selection.
• Click Sort.

This is the output.

To sort all the columns:

• Select the entire range.
• Go to the Data tab > Sort & Filter > 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.

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

This is the output.

### Case 2: Sorting Duplicates in a Row

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

This is the output.

## Using the SORTBY Function

### 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.

### 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.

Read More: How to Sort Unique List in Excel

## Using the SORT Function

• 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.This is the output.

### 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 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

Advanced Excel Exercises with Solutions PDF