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.
For a better understanding of viewers, we will also work with a dataset where we will perform sorting column-wise. It is given below.
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 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 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.
📕 Read More: 8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula
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:
- 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 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.
📕 Read More: How to Sort Alphabetically in Excel with Multiple 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.
Related Articles
- 2 Easy Steps to Sort Alphanumeric Data in Excel
- 6 Ways to Auto Sort When Data Changes in Excel
- How to Sort by Last Name in Excel