How to Sort a Column by Occurrence Count in Excel

This post will guide you how to sort a column by occurrence count in Excel. How do I sort data in one column by the most frequent value in Excel 2013/2016.

Sort A Column by Occurrence Count

Assuming that you have a list of data in one column, and you want to sort this column descending by these occurrence count numbers. How to do it. And you need to count the occurrences of the values in that column, and then use the sort command to sort that column. You can use CountIF function to count the frequency of occurrence of your list in one column, and then you can sort this column descending by these count numbers. Just do the following steps:

Step1: select one newly column beside your original column as helper column. And then you need to type the following column in the helper column to count the occurrence number.

=COUNTIF($B$1:$B$7, B1)

You need to press Enter key to apply this formula, and then drag the AutoFill handle down to other cells to apply this formula.

sort column by occurrence count1

Note: B1:B7 is a range of cells that you want to sort by occurrence count.  And the B1 is the first cell of your original column.

Step2: keep to select the helper column, and go to Data tab, click Sort A to Z command under Sort & Filter group. And the Sort Warning dialog will open.

sort column by occurrence count2

Step3: select Expand the selection option in the Sort Warning dialog box, and click Sort button. The helper column should be sorted by count numbers, and your original columns should also be sorted by count numbers.

sort column by occurrence count3

Step4: now you can remove helper column.

sort column by occurrence count4

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Count Attendance and Absence with COUNTIF function

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...


So empty here ... leave a comment!

Leave a Reply