Concept
1. UNIQUE
=UNIQUE(tblSales[Salesperson])
→ Amit, Neha, Ravi, Sonal (order of first appearance).
Options: UNIQUE(array, [by_col], [exactly_once])
- Unique combinations:
=UNIQUE(tblSales[[Region]:[Category]])→ 6 Region–Category pairs. - exactly_once = TRUE returns values that appear only once — handy for finding one-time customers.
- Count of distinct items:
=COUNTA(UNIQUE(tblSales[Product]))→ 4.
2. SORT
=SORT(array, [sort_index], [sort_order], [by_col])
=SORT(UNIQUE(tblSales[Product]))→ Chair, Desk, Laptop, Mobile.- Sort a table by its 7th column (Amount), largest first:
=SORT(tblSales, 7, -1). - Several keys:
=SORT(tblSales, {2,7}, {1,-1})→ by Region A–Z, then Amount high–low.
3. SORTBY — sort by something not in the output
=SORTBY(array, by_array1, [order1], ...)
Ranked salesperson report — names in F2, totals in G2:
F2: =UNIQUE(tblSales[Salesperson])
G2: =SUMIFS(tblSales[Amount], tblSales[Salesperson], F2#)
Ranked list in I2:
=SORTBY(F2#, G2#, -1)
→ Amit (4,19,000), Ravi (2,36,000), Sonal (2,10,000), Neha (2,05,000).
Both columns together, sorted:
=SORTBY(HSTACK(F2#, G2#), G2#, -1)
(HSTACK joins arrays side by side — Microsoft 365 / 2024.)
4. Top N
=TAKE(SORTBY(tblSales[[Date]:[Amount]], tblSales[Amount], -1), 3)
Top 3 orders: 1,65,000 (Laptop, Amit, 02-Jun), 1,10,000 (Laptop, Amit, 05-Apr), 1,10,000 (Laptop, Ravi, 14-May). TAKE(array, -3) gives the bottom 3. Without TAKE: =INDEX(SORT(...), SEQUENCE(3), {1,2,3,4,5,6,7}).
5. A formula-only summary table
| F | G | H | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | =SORT(UNIQUE(tblSales[Region])) |
=SUMIFS(tblSales[Amount],tblSales[Region],F2#) |
=G2#/SUM(G2#) |
North 5,09,000 47.6%, South 2,59,000 24.2%, West 3,02,000 28.2%. Updates instantly — no refresh — but no drag-and-drop either. Pivots for exploration, formulas for fixed live reports.
6. Dropdown lists that grow
Data Validation → List → Source: =$F$2#. New regions appear in the dropdown automatically.
Common mistakes
Sorting by column number that shifts when the table changes (prefer SORTBY with a named column). Mixing UNIQUE with blank cells (a blank appears as 0 — filter blanks first: UNIQUE(FILTER(x, x<>""))).