9. Dynamic Arrays
Chala mitrano, aaj aapan navin Excel chi "superpower" shikuya – dynamic arrays (ॲरे – एका सूत्रातून मिळणाऱ्या अनेक मूल्यांची मालिका). Ekach formula ne poori list, filter, sort, unique values – aani te pan automatic update! FILTER, SORT, UNIQUE, SEQUENCE, LET, LAMBDA, VSTACK… He functions fakt Microsoft 365 / navin Excel madhe aahet, he lakshat theva.
What you will learn in this module
- Spill ranges, the
#spill reference and #SPILL! errors - FILTER, SORT, SORTBY and UNIQUE
- SEQUENCE and RANDARRAY
- LET for readable formulas and LAMBDA (brief) for your own functions
- VSTACK/HSTACK, CHOOSECOLS, TAKE and DROP
Version availability
FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY and LET: Microsoft 365 / Excel 2021+. LAMBDA, VSTACK, HSTACK, CHOOSECOLS, TAKE and DROP: Microsoft 365 / Excel 2024. In Excel 2019 or older these return #NAME?.
Examples use the Module 3 mini dataset converted to a Table named tblMini (columns Order ID, Date, City, Platform, Category, Qty, Amount, Mins, Status).
Concepts in this chapter
- 9.1Spill Ranges and the # Operator
- 9.2FILTER
- 9.3SORT and SORTBY
- 9.4UNIQUE
- 9.5SEQUENCE and RANDARRAY
- 9.6LET
- 9.7LAMBDA (Brief)
- 9.8VSTACK and HSTACK
- 9.9CHOOSECOLS, TAKE and DROP
The chapter recap is at the end of the last concept page.