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.


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


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.

Sort Duplicates in Excel

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.

Sort Duplicates in Row

  • 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)

Sorting Duplicates in Rows

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

Autofill

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.

How to Sort Duplicates in Excel stepwise in row

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

How to Sort Duplicates in Excel in row descending order

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

How to Sort Duplicates in Excel in row

📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula

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.

Sort Duplicates in Columns

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

📕 Read More: Sort Multiple Columns Independently of Each Other in Excel

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.

Sort Duplicates in Columns Descending

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

Final Sort Duplicates in Columns

📕 Read More: How to Sort Alphabetically in Excel with Multiple Columns

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.

How to Sort Duplicates in cloumn stepwise

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

How to Sort Duplicates in Excel column-wise

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


Related Articles

(Visited 130 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo