Ravindra BagaleCourses & study guides

9. Dynamic Arrays

By Last updated:

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

  1. 9.1Spill Ranges and the # Operator
  2. 9.2FILTER
  3. 9.3SORT and SORTBY
  4. 9.4UNIQUE
  5. 9.5SEQUENCE and RANDARRAY
  6. 9.6LET
  7. 9.7LAMBDA (Brief)
  8. 9.8VSTACK and HSTACK
  9. 9.9CHOOSECOLS, TAKE and DROP

The chapter recap is at the end of the last concept page.