Meraz Al Nahian
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.
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)
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)
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.
- Use the Fill Handle to AutoFill the lower cells.
Thus you can rank data by sorting it by the scores.
Read More: How to Stack Rank Employees in Excel (3 Methods)
Similar Readings
- How to Calculate Rank Percentile in Excel (7 Suitable Examples)
- Rank IF Formula in Excel (5 Examples)
- How to Calculate the Top 10 Percent in Excel (4 Ways)
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)
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)
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.
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
- Excel Formula to Rank with Duplicates (3 Examples)
- Rank Average in Excel (4 Common Scenarios)
- How to Rank Within Group in Excel (3 Methods)
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
- 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
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.
- 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.
- 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.
