Rank data Based on the Number of Occurrences

This post will guide you how to rank data by the number of occurrences in excel. How do I rand cells by the number of occurrences with formula in excel.

Rand Data by Occurrences

Assuming that you have a list of data in your worksheet, and you want to rank those data based on the number of occurrence of those value in the specified range of cells. You need to get the number of occurrences for the selected range of cells, then rank those number using the RANK function. Just do the following steps:

#1 you need to copy the data list firstly and then paste the data into another new column (C1:C8).

rank data based on number1

#2 go to DATA tab, click Remove Duplicates command under Data Tools group.

rank data based on number2

#3 click OK button. All duplicate values are removed

rank data based on number3

#4 type this formula into the formula box of cell D2 to get the occurrence number in the range of cells A1:A8, then press enter key, and drag the AutoFill Handler over other cells to apply this formula.


rank data based on number4

#5 type the RANK formula into the formula box of cell E1 to rank the data based on the occurrence number, then press enter key, and drag AutoFill Handler over other cell.


rank data based on number5

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)…
  • Excel Rank function
    The Excel RANK function returns the rank of a given number in a supplied range of cells.  If there are duplicated values in a list of values, it will be set the same rank.The syntax of the RANK function is as below:= RANK (number,ref,[order])….
Related Posts

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

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Repeating Character n Times in Excel

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so ...

Calculate Cumulative Loan Interest in Excel

What is Cumulative Loan Interest? When comparing different types of loans, many people want to know what is Cumulative Loan Interest. The sum of all interest payments you've made on a loan is referred to as cumulative interest. Different lenders ...


So empty here ... leave a comment!

Leave a Reply