Modern Excel and Power ToolsModule 1: Dynamic Arrays & Modern Formula Spilling

SORT & SORTBY: Dynamic Ranked Leaderboards Without Formulas

Build auto-updating top-performer rankings and multi-level leaderboards using SORT and SORTBY combined with FILTER.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

Interactive Study Modes:Switch between stacked, split-screen workbench, or cinematic theater mode.

SORT & SORTBY: Dynamic Ranked Leaderboards Without Formulas

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Open VBA EditorLaunches the Visual Basic Editor to inspect, debug, and run macro code.

⌥AltF11

Launch Power Query EditorOpens Power Query window to transform, clean, and reshape incoming data.

⌥AltAPT

Insert PivotTableCreates an instant summary PivotTable from any structured data table.

⌥AltNV

Reference Spill RangeAppends the hash symbol to dynamically capture the entire spilled array range.

#

Evaluate Formula Step-by-StepSteps through complex nested formula logic to debug errors and intermediate results.

⌥AltMV

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

⚡ Power Tools & Macros Note: In-browser sandbox simulates Excel formula syntax and math logic. For compiled VBA macros and native Power Query M-code transforms, please open the practice workbooks provided above in desktop Microsoft Excel (365 / 2021).

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Hint:Use formula =SUM(D2:D5)

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Lesson notes & syllabus guide

SORT & SORTBY: Dynamic Ranked Leaderboards

Corporate reporting me static sorting tab break ho jati hai jab naya data enter hota hai. SORT aur SORTBY functions se aap live leaderboards create karte hain jo source data change hote hi automatically re-order ho jate hain.

1. SORT Function vs SORTBY Function

SORT: Data ko usi range ke kisi column index ke basis par sort karta hai.
SORTBY: Data ko kisi external column ya range ke basis par sort karta hai (aur multi-level sorting allow karta hai).

=SORT(array, [sort_index], [sort_order], [by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

sort_order: 1 for Ascending (A-Z, low to high), -1 for Descending (Z-A, high to low).

2. Multi-Level Ranking (Flipkart Fulfilment Centers)

Agar hume Flipkart FCs ko pehle Region (Ascending) aur fir On-Time Delivery Rate (Descending) par sort karna hai:

=SORTBY(A2:E60, B2:B60, 1, E2:E60, -1)

3. Creating Top 5 Dynamic Leaderboard

FILTER aur SORT ko nest karke hum top performing hubs extract kar sakte hain:

=CHOOSEROWS(SORT(FILTER(A2:E60, B2:B60="South"), 5, -1), 1, 2, 3, 4, 5)

Or simply using TAKE:

=TAKE(SORT(FILTER(A2:E60, B2:B60="South"), 5, -1), 5)

[!WARNING] Agar sort_order mention nahi kiya jaye toh Excel default 1 (Ascending) assume karta hai. Leaderboards me revenue ya performance hamesha highest first dikhane ke liye -1 pass karna mandatory hai.

Lesson resources

Download the workbooks and cheat sheets used in this lesson.

Flipkart National Fulfilment Center Leaderboard — Practice Workbookflipkart_fulfilment_center_leaderboard_before.xlsx

SORT & SORTBY Dynamic Ranking Quiz

Pass mark 70%

5 questions on sort ordering, SORT vs SORTBY syntax, and multi-key leaderboard logic.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Flipkart National FC Top 5 Leaderboard Build

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

SORT & SORTBY: Dynamic Ranked Leaderboards

1. SORT Function vs SORTBY Function

SORT: Data ko usi range ke kisi column index ke basis par sort karta hai.

SORTBY: Data ko kisi external column ya range ke basis par sort karta hai (aur multi-level sorting allow karta hai).

=SORT(array, [sort_index], [sort_order], [by_col]) =SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

sort_order: 1 for Ascending (A-Z, low to high), -1 for Descending (Z-A, high to low).

2. Multi-Level Ranking (Flipkart Fulfilment Centers)

Agar hume Flipkart FCs ko pehle Region (Ascending) aur fir On-Time Delivery Rate (Descending) par sort karna hai:

=SORTBY(A2:E60, B2:B60, 1, E2:E60, -1)

3. Creating Top 5 Dynamic Leaderboard

FILTER aur SORT ko nest karke hum top performing hubs extract kar sakte hain:

=CHOOSEROWS(SORT(FILTER(A2:E60, B2:B60="South"), 5, -1), 1, 2, 3, 4, 5)

Or simply using TAKE:

=TAKE(SORT(FILTER(A2:E60, B2:B60="South"), 5, -1), 5)

[!WARNING] Agar sort_order mention nahi kiya jaye toh Excel default 1 (Ascending) assume karta hai. Leaderboards me revenue ya performance hamesha highest first dikhane ke liye -1 pass karna mandatory hai.