Ranking Data in Excel with Sorting (3 Quick Methods)

The article will show you some easy ways of ranking data in Excel with sorting. Sorting data can help you find which item gets the first or last rank in a dataset which may be useful to you later. We do have to sort data in our daily life for academic and official purposes. In this article, we have a dataset containing the score and of some students in an exam, their current CGPA and their corresponding names. The name of the data sheet is dataset.

ranking data in excel with sorting


Download Practice Workbook


3 Ways of Ranking Data in Excel with Sorting

1. Ranking Data with Sorting by Using Excel SORT and RANK Functions

Suppose you want to rank the students by their scores in the exam. We are going to show you how to do that with the SORT and RANK functions.

Steps:

  • First, open a new sheet and make the columns for names, scores, and CGPAs.

  • Type the following formula in cell B5.
=SORT(dataset!B5:D12,2,-1,FALSE)

ranking data in excel with sorting

Here, we want to sort the students by their obtained scores, so we put B5:D12 of the dataset sheet in the array part of the SORT Function and set the sort_index as 2 because the scores are in the 2nd column of B5:D12. We want top scores as higher rank, so we want the result in a descending way. And that’s why we put -1 in the sort_order part. We want an exact match, so we choose FALSE.

  • Hit the ENTER button and you will see the ranks of the students sorted by their scores.

  • Now to give these pupils rank, make a column named Rank for it and type the following formula in cell E5.
=RANK(C5,$C$5:$C$12,0)

ranking data in excel with sorting

Here, the RANK Function checks the value of cell C5 for it’s rank in the range C5:C12. We want the range fixed, so we used Absolute Cell Reference. Whoever gets a better score is going to come up in the higher rank, so we choose descending order (0).

  • Press the ENTER button and you will see the rank of the value in C5.

ranking data in excel with sorting

Thus you can rank data by sorting it by the scores.

Read More: How to Stack Rank Employees in Excel (3 Methods)


Similar Readings


2. Using Excel SORT and RANK Functions to Sort by CGPA

Say you want to rank the students by their current CGPA. We are going to show you how to do that with the SORT and RANK Functions.

Steps:

  • First, open a new sheet and make the columns for names, scores and CGPAs.

  • Type the following formula in cell B5.
=SORT(dataset!B5:D12,3,-1,FALSE)

ranking data in excel with sorting

Here, we want to sort the students by their current CGPA, so we put B5:D12 of the dataset sheet in the array part of the SORT Function and set the sort_index as 3 because the CGPAs are in the 3rd column of B5:D12. We want top CGPAs as higher ranks, so we want the result in a descending way. And that’s why we put -1 in the sort_order part. We want an exact match, so we choose FALSE.

  • Hit the ENTER button and you will see the ranks of the students sorted by their CGPAs.

  • Now to give these pupils rank, make a column which I named Rank then type the following formula in cell E5.
=RANK(D5,$D$5:$D$12,0)

ranking data in excel with sorting

Here, the RANK Function checks the value of cell D5 for it’s rank in the range D5:D12. We want the range fixed, so we used Absolute Cell Reference. Whoever gets a better score is going to come up in the higher rank, so we choose descending order (0).

  • Press the ENTER button and you will see the rank of the value in D5.

  • Use the Fill Handle to AutoFill the lower cells.

ranking data in excel with sorting

Thus you can rank data by sorting it by the CGPAs.

Read More: How to Rank with Ties in Excel (5 Simple Ways)


Similar Readings


3. Applying VBA to Rank Data in Excel with Sorting

We can also apply Microsoft Visual Basic for Application (VBA) to rank data with sorting. We are going to rank the data by the scores, so we won’t need the CGPA column. And also, I sort the data by the largest score to lowest initially. Let’s go through the procedure below.

Steps:

  • First, open Visual Basic from the Developer Tab.

  • The VBA Window will open. Open the Sheet you want to run the VBA

ranking data in excel with sorting

  • Type the following code in the Worksheet..
Private Sub Worksheet_Activate()
Dim last_row As Long
last_row = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
Range("B2:D" & last_row).Select
    Selection.AutoFilter
ActiveWorkbook.Worksheets("RankByVBA").AutoFilter.Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("RankByVBA").AutoFilter.Sort.SortFields.Add Key:=Range("C2"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("RankByVBA").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
     Selection.AutoFilter
    Range("E2").Select
    ActiveCell.Formula = "=RANK(C2,C:C,0)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & last_row), Type:=xlFillnormal
    Range("E2:E" & last_row).Select
     Range("E2").Select
End Sub

ranking data in excel with sorting

Here, we used a Private Worksheet, declared last_row as a Long variable, and set a range B2:D by Range Method. The name of our working sheet is RankByVBA so we used the VBA ActiveWorkBook method to make it active. We also enter AutoFilter, Sort, and SortFields properties. We put a formula to return the ranks of these students and declare it by VBA ActiveCell.Formula property. Finally, we set column E as our Destination Range so that we can make this code to determine the rank of the data in column E.

  • Press CTRL + S to save it and go back to your Excel sheet.

  • Now move to another sheet.

ranking data in excel with sorting

  • Then return to the RankByVBA sheet and you will see the ranks of the students automatically.

  • If you put a new entry in the dataset, you will see that entry will automatically get it’s rank.

ranking data in excel with sorting

  • Now go to another sheet and return again. You will see the rank of that new entry will sort in the dataset.

Thus you can rank data with sorting by using VBA.


Practice Section

Here I’m giving you the dataset so that you can practice these methods on your own.


Conclusion

In this article, I tried to show you some ways of ranking data in excel with sorting. I hope this will give you some clear concepts about this topic. If you have any better ideas or feedback, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo