How to Sort Duplicates in Excel Easily

Excel is a useful tool for you when sorting duplicate values. Sorting is one of the most common things that people do with Excel every day. You need to sort in many different ways, like by multiple columns, by dates, by time, and so on. We’ll show you how to sort duplicates today with examples. I’m making examples and workbooks with Microsoft 365. You can use the version you prefer.

Contents hide

📁 Download Excel File

Learn to Sort Duplicates in Excel with These 2 Easy Methods

1. Sort Duplicates in Rows

1.1 Sort Descending Order

1.2 Sort Ascending Order

2. Sort Duplicates in Columns

2.1 Sort Descending Order

2.2 Sort Ascending Order

📄 Important Notes

📝 Takeaway from This Article

Conclusion

📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

How to Sort Duplicates in Excel.xlsx

Learn to Sort Duplicates in Excel with These 2 Easy Methods

First of all, let’s talk about the workbook, which is where all of the examples for today come from. Here is a dataset of some items with their quantities. On purpose, there are duplicates. We will sort the duplicate values in this dataset.

For a better understanding of viewers, we will also work with a dataset where we will perform sorting column-wise. It is given below.

Method 1

1. Sort Duplicates in Rows

Now I’ll show you how to sort rows with duplicates. We will use the COUNTIF function too. We will use the statistical function COUNTIF to count the number of cells that meet a certain condition.

⬇️⬇️ STEPS ⬇️⬇️

Let’s enter a row for the counter here.

We will use the COUNTIF function to fill the C7 cell in the counter row. The formula will look like this:

=COUNTIF($C$5:$L$5,C5)

Then we use AutoFill to fill in the rest of the row.

1.1 Sort Duplicates in Rows

1.1 Sort Descending Order

First, we will perform sorting in descending order. It means higher to lower order.

⬇️⬇️ STEPS ⬇️⬇️

To perfectly sort rows, we need to select them all and select Sort in the Sort & Filter section of the Data.

Now, a dialog box called “Sort” will appear in front of you. Then put the order Largest to smallest.

There is another dialog box with two options: Sort top to bottom or Sort left to right. Click OK after choosing Sort left to right.

Now, you can see the rows in the drop-down box that says “Sort by.” Here, row 7 of our Counter is shown. Choose Row 7 and press OK.

The duplicate data is in the row, and it is in order from largest to smallest. Also, a practice sheet is given for you to practice the whole procedure.

📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel

1.2 Sort Duplicates in Rows

1.2 Sort Ascending Order

Now we will perform sorting in ascending order. It means lower to a higher order.

⬇️⬇️ STEPS ⬇️⬇️

We set the order from smallest to largest using the Sort box.

Then press OK.
The duplicate data is in the row, and it is in order from smallest to largest. A practice sheet is given for you to practice the whole procedure.

Method 2

2. Sort Duplicates in Columns

Now I’ll show you how to sort columns with duplicates. We will use the COUNTIF function too. We will use the statistical function COUNTIF to count the number of cells that meet a certain condition.

⬇️⬇️ STEPS ⬇️⬇️

Let’s enter a column for the counter here.

We will use the COUNTIF function to fill in the Counter row. So the formula will look like this:

=COUNTIF($B$6:$B$23,B6)

Then we use AutoFill to fill in the rest of the column.

2.1 Sort Duplicates in Columns

2.1 Sort Descending Order

Now we will perform sorting in descending order. It means higher to lower order.

⬇️⬇️ STEPS ⬇️⬇️

To perfectly sort rows, we need to select them all and select Z to A in the Sort & Filter section of the Data.

If you choose just one column from a whole table, the Sort Warning dialog box will appear. Choose Expand the selection, and then click Sort.

The duplicate data is in the column, and it is in order from largest to smallest. Also, a practice sheet is given for you to practice the whole procedure.

2.2 Sort Duplicates in Columns

2.2 Sort Ascending Order

Now we will perform sorting in ascending order. It means lower to a higher order.

⬇️⬇️ STEPS ⬇️⬇️

To perfectly sort rows, we need to select them all and select A to Z in the Sort & Filter section of the Data.

If you choose just one column from a whole table, the Sort Warning dialog box will appear. Choose Expand the selection, and then click Sort.

The duplicate data is in the column, and it is in order from lowest to highest. Also, a practice sheet is given for you to practice the whole procedure.

📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel

📄 Important Notes

🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️ A practice workbook is given in each sheet so that you can practice yourself.

🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️ At the end of the Excel file, there is a sheet where they can practice.

📝 Takeaway from This Article

📌 Reader will be able to sort duplicates in Excel easily with various methods.

📌 Readers can sort values both in ascending and descending orders.

📌 Readers can sort values both column and row-wise.

📌 One will be able to find the quickest way to sort duplicates in Excel.

Conclusion

That concludes today’s session. These are the methods for sorting duplicate values in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.

(Visited 124 times, 1 visits today)