Dynamic arrays
SORT function: build a live sorted leaderboard without editing source rows
Spill a sorted copy of a multi-column table ordered ascending or descending by any column, updating automatically whenever source numbers change.
Keep raw market-stall entries in entry order
Four market stalls record Saturday revenue in B2:B5. Rather than manually re-sorting the entry table after every update, place a dynamic SORT formula in a separate summary block.
| Stall (A) | Revenue (B) |
|---|---|
| Pear Orchard | 140 |
| Berry Farm | 310 |
| Herb Stand | 95 |
| Stone Bakery | 225 |
Sort by column 2 in descending order (-1)
In SORT(array, [sort_index], [sort_order]), sort_index = 2 selects the second column (Revenue) and sort_order = -1 sorts highest to lowest (1 is ascending). On a worksheet =SORT(A2:B5, 2, -1) spills all four rows starting with Berry Farm (310); wrapping with INDEX reads any specific cell—such as row 1, column 1—in a single-cell card.
=INDEX(SORT(A2:B5,2,-1),1,1)Verify automatic re-ranking when a number changes
In the practice grid, raise Herb Stand (B4) from 95 to 350 and recalculate: Herb Stand immediately becomes the #1 ranked stall without touching any menu command.
=INDEX(SORT(A2:B5,2,1),1,1)Try the example
Use the original values or change them to test your understanding.