RANK function in Excel - Dashboard Guide

Dashboard Mode - RANK function

Goal

Find the rank of each student's score in a test to see who scored highest and who scored lowest.

Sample Data

Student	Score
Alice	88
Bob	95
Charlie	70
Diana	95
Eva	60
Frank	75
Grace	88

Dashboard Components

• Rank Column: Shows the rank of each student's score using the formula =RANK(B2,$B$2:$B$8,0) in cell C2 and copied down to C8. This ranks scores from highest (1) to lowest.
• Top Scorer Card: Displays the highest score using =MAX(B2:B8) and the student(s) who achieved it with =TEXTJOIN(", ",TRUE,IF(B2:B8=MAX(B2:B8),A2:A8,"")) entered as an array formula (Ctrl+Shift+Enter in older Excel versions).
• Lowest Scorer Card: Displays the lowest score using =MIN(B2:B8) and the student(s) who achieved it with =TEXTJOIN(", ",TRUE,IF(B2:B8=MIN(B2:B8),A2:A8,"")) as an array formula (Ctrl+Shift+Enter in older Excel versions).
• Sorted Table: A table sorted by rank ascending to show students from highest to lowest score. This can be done by sorting the data by the Rank column.

Dashboard Layout

+----------------------+----------------------+
| Top Scorer Card      | Lowest Scorer Card    |
+----------------------+----------------------+
|                      Sorted Table             |
| +------------------------------------------+ |
| | Student | Score | Rank                    | |
| |---------|-------|-------------------------| |
| | Bob     | 95    | 1                       | |
| | Diana   | 95    | 1                       | |
| | Alice   | 88    | 3                       | |
| | Grace   | 88    | 3                       | |
| | Frank   | 75    | 5                       | |
| | Charlie | 70    | 6                       | |
| | Eva     | 60    | 7                       | |
| +------------------------------------------+ |
+----------------------------------------------+

Interactivity

Add a filter to select specific students or score ranges. When you filter, the Rank column recalculates ranks only for visible scores, and the Top and Lowest Scorer cards update to show results for the filtered data.

Self Check

If you add a filter to show only students with scores above 70, which students appear in the Sorted Table and what are their ranks? What do the Top Scorer and Lowest Scorer cards show?

Key Result

Dashboard ranks student scores, highlights top and lowest scorers, and shows sorted results.