Array and Dynamic Array Functions
An array formula works on a whole range at once. Before 2021 it needed Ctrl + Shift + Enter, which shows the formula in curly braces {=SUM(IF(B2:B9="Jammu",C2:C9))} . In Excel 2021 and Microsoft 365, dynamic arrays do this by default, and the result spills into the neighbouring cells.
| Function | Syntax | Example | Result |
|---|---|---|---|
| FILTER | =FILTER(array, include, [if_empty]) | =FILTER(A2:C9,B2:B9="Jammu") | Rows for Bilal, Charu, Gurpreet |
| SORT | =SORT(array, [sort_index], [order], [by_col]) | =SORT(A2:C9,3,-1) | Table sorted by Marks, largest first (order 1 = ascending, -1 = descending) |
| SORTBY | =SORTBY(array, by_array, [order]) | =SORTBY(A2:A9,C2:C9,-1) | Names ordered by marks |
| UNIQUE | =UNIQUE(array) | =UNIQUE(B2:B9) | Srinagar, Jammu, Anantnag, Baramulla |
| SEQUENCE | =SEQUENCE(rows, [cols], [start], [step]) | =SEQUENCE(5) | 1, 2, 3, 4, 5 down a column |
| RANDARRAY | =RANDARRAY(rows, cols) | A block of random numbers | |
| XLOOKUP | see the lookup topic | ||
| LET | =LET(name, value, calculation) | =LET(x,C2:C9,SUM(x)/COUNT(x)) | 71.5 (names a value once, uses it again) |
| VSTACK / HSTACK | =VSTACK(A2:A3,A8:A9) | Joins ranges vertically / horizontally | |
| TAKE / DROP | =TAKE(A2:A9,3) | First 3 / all except those rows |
- Spill range: the area the result fills. Refer to all of it with the # sign, for example
=SUM(F2#). - #SPILL! appears when something blocks the spill area.
- Count of distinct districts:
=COUNTA(UNIQUE(B2:B9))→ 4. - Old-style array examples:
=SUM(IF(B2:B9="Jammu",C2:C9))→ 228;=MAX(IF(B2:B9="Jammu",C2:C9))→ 92. - SUMPRODUCT handles many array tasks without Ctrl + Shift + Enter.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.