MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Dynamic Arrays Straight to the Point

September 2018

Excel Dynamic Arrays Straight to the Point

Introduced on September 24 2018, the new SORT, FILTER, and UNIQUE make everything easier in Excel formulas!

Bill Jelen, MrExcel

category: Excel Functions
covers: Excel 365 Insider

Product Details
  • eBook:  57 pages
  • Publisher: Holy Macro! Books
  • PDF ISBN: 978-1-61547-265-9
  • ePub ISBN: 978-1-61547-377-9
  • Mobi (Kindle) ISBN: 978-1-61547-165-2

New for Office 365 customers, one single formula sitting in one single cell can return many results. Those extra results will spill into adjacent cells. This is a major change to the calculation engine in Excel. When you first hear of this feature, you might think it is about SORT, FILTER, UNIQUE, SORTBY, SEQUENCE, RANDARRAY, and SINGLE. But dynamic arrays make every Excel calculation function more powerful. Pass a SEQUENCE to another function and Excel will Lift the function to return many results. In this book, you will learn new terms such as Lifting, Broadcasting, Pairwise Lifting, Array Truncation, and why Implicit Intersection was the culprit in making the old Ctrl+Shift+Enter formulas so hard. Plus, you will never need to press Ctrl+Shift+Enter again.

  • Introduction
    • What will the Headlines Say About Dynamic Array Formulas?
    • This Book Will Not Be the Comprehensive Guide to Dynamic Arrays
    • Dynamic Array Formulas and Their Offspring Are Office 365 Exclusive
    • How This Book is Organized
  1. Formulas can now Spill
    • What happens if it can't spill?
    • If Your Formula Points to a Table, the Array Will Expand
    • What is Really Happening Behind the Scenes?
    • The New Spilled Range Operator: E3#
    • What about Implicit Intersection?
  2. SORT Function
    • Simple Sort with one argument
    • Sort by Two or more columns in the Results
    • Random Sort using SORT and RANDARRAY
  3. SORTBY Function
    • When you need to sort by something not in the results
  4. FILTER Function!
    • FILTER Function
  5. UNIQUE Function
    • UNIQUE Function
  6. Combining Functions
    • Nesting Array Functions - SORT of UNIQUE
  7. SEQUENCE Function
    • Generate A Range of Sequential Numbers
    • Using SEQUENCE Inside Of Another Function
  8. RANDARRAY Function
    • Generating an Array of RAND() with RANDARRAY()
    • Simulating RANDBETWEEN
    • Using RANDARRAY for Modeling and Simulation
  9. Why CSE Array Formulas Were So Hard - it is Implicit Intersection
    • A Quick Glossary For This Chapter
    • Legacy Excel Used Arrays Far More Often Than We Realized
    • Explaining Implicit Intersection
    • Breaking Implicit Intersection
    • Lifting When A Scalar Is Expected, but an Array is Provided
    • Truncating Arrays
    • Using a Wrapper Function in Legacy Excel
    • Preventing Implicit Intersection With Ctrl+Shift+Enter
    • From Lifting to Pairwise Lifting
    • Broadcasting Makes All Arrays the Same Size
    • Here is a simple Broadcasting example
    • How Does Lifting, Broadcasting, Truncate Arrays, and Implicit Intersection Affect Dynamic Arrays?
  10. Other Functions That Are Now Dynamic Array Formulas
    • Not Seven Functions, But Hundreds of Examples
    • NOW and SEQUENCE
    • Generate Sequential Letters with CHAR and SEQUENCE and TEXTJOIN
    • Return N Largest Items Using LARGE
    • Return Smallest N Items Horizontally
    • A Shorter Way to TRANSPOSE
    • Show Formulas for a Range with FORMULATEXT
    • Display Numbers as Binary, Octal, or Hex using BASE
    • Sum the LEN of Many Cells
    • Do Text to Columns with a Formula
    • Sum All VLOOKUPS
    • PROPER Case of All Names With One Formula
    • Replace a What-If Data Table With One Formula
    • Apply Up/Flat/Down Icons Using SIGN Function
    • Using the Spilled Formula Operator To Point To An Array
    • Generate a Series of Months
    • Forecast with an Array
    • You Can Only Use One Level of Pairwise Lifting
    • Forecasting 12 Months by 5 Years
    • Transpose One Array to Prevent Pairwise Lifting
    • Forecast All Five Years In One Formula
    • Combining Array Formulas To Simplify Cube Formulas
    • There are Hundreds More Examples


During the rest of the 2018 Q4, you can download the PDF for free with my compliments. The 60-page e-book will get you up to speed with these amazing formulas.

Excel Dynamic Arrays Straight to the Point Free Download

Related Products