How to Rank Data in Excel Using Sorting (3 Methods)

In this guide, we’ll work with a dataset containing student scores in an exam, their current CGPA, and corresponding names.

ranking data in excel with sorting


Method 1 – Using SORT and RANK Functions to Rank by Exam Scores:

  • Set Up Your Sheet:
    • Create a new sheet with columns for names, scores, and CGPAs.

  • Sorting with SORT Function:
    • In cell B5, enter the following formula:
=SORT(dataset!B5:D12,2,-1,FALSE)

ranking data in excel with sorting

    • Explanation:
      • We sort the students based on their obtained scores.
      • The array part of the SORT function is B5:D12 from the dataset sheet.
      • Set the sort_index to 2 (since scores are in the 2nd column).
      • Use -1 for descending order (top scores get higher rank).
      • Choose FALSE for an exact match.
    • Press ENTER to see the order of the students based on their scores.

  • Assign Ranks:
    • Create a new column called Rank.
    • In cell E5, use this formula:
=RANK(C5,$C$5:$C$12,0)

ranking data in excel with sorting

    • Explanation:
      • The RANK function checks the value in cell C5 against the range C5:C12.
      • Absolute cell references ensure a fixed range.
      • A descending order 0 means better scores get higher ranks.
  • AutoFill:
    • Press ENTER and use the Fill Handle to AutoFill the remaining cells.

ranking data in excel with sorting

 

Read More: How to Rank with Ties in Excel


Method 2 – Using SORT and RANK Functions to Rank by CGPA:

  • Set Up Your Sheet:
    • Create a new sheet with columns for names, scores, and CGPAs.

  • Sorting with SORT Function:
    • In cell B5, enter the following formula:
=SORT(dataset!B5:D12,3,-1,FALSE)

ranking data in excel with sorting

    • Explanation:
      • We sort the students based on their current CGPA.
      • The array part of the SORT function is B5:D12 from the dataset sheet.
      • Set the sort_index to 3 (since CGPAs are in the 3rd column).
      • Use -1 for descending order (top CGPAs get higher ranks).
      • Choose FALSE for an exact match.
    • Press ENTER button to see the students sorted by their CGPAs.

  • Assign Ranks:
    • Create a new column called Rank.
    • In cell E5, use this formula:
=RANK(D5,$D$5:$D$12,0)

ranking data in excel with sorting

    • Explanation:
      • The RANK function checks the value in cell D5 against the range D5:D12.
      • Absolute cell references ensure a fixed range.
      • A descending order 0 means better CGPAs get higher ranks.
  • AutoFill:
    • Press ENTER and use the Fill Handle to AutoFill the remaining cells.

ranking data in excel with sorting

Read More: Rank IF Formula in Excel


Method 3 – Ranking Data in Excel Using VBA and Sorting:

In this method, we’ll leverage Microsoft Visual Basic for Applications (VBA) to rank data by scores. We won’t need the CGPA column for this ranking. Let’s walk through the procedure step by step:

  • Open Visual Basic:
    • Go to the Developer tab and open Visual Basic.

  • Access the VBA Window:
    • The VBA window will appear.
    • Open the sheet module where you want to run the VBA code.

ranking data in excel with sorting

  • Enter the VBA Code:
    • Type the following code in the module:
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

    • Explanation:
      • We define a private worksheet and declare last_row as a Long variable.
      • Set the range to B2:D based on the Range method.
      • The working sheet is named RankByVBA, so we activate it using ActiveWorkbook.Worksheets(“RankByVBA”).
      • Apply AutoFilter, Sort, and SortFields properties.
      • Use the RANK formula to determine student ranks (column E).
      • Set column E as the destination range for the ranks.
  • Save and Return to Excel:
    • Press CTRL + S to save the VBA code.
    • Go back to your Excel sheet.

  • Observe Automatic Ranking:
    • Switch to another sheet.

ranking data in excel with sorting

    • Return to the RankByVBA sheet to see the students’ ranks automatically updated.

    • Any new entries in the dataset will also receive their ranks automatically.

ranking data in excel with sorting

  • Verify Sorting:
    • Move to another sheet and return to the RankByVBA sheet.
    • Observe how the rank of the new entry is sorted in the dataset.


Practice Section

Herewith the dataset so that you can practice these methods on your own.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Excel RANK Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo