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/2019/365.

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

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.

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.

Step4: now you can remove helper column.

2. Video: Sort a Column by Occurrence Count in Excel

This video will demonstrate how to sort a column by occurrence count in Excel using COUNTIF formula.

3. 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)…