did-you-know? rent-now

Rent More, Save More! Use code: ECRENTAL

did-you-know? rent-now

Rent More, Save More! Use code: ECRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

9781615470822

The New Frontier of Excel Analysis and Reporting with Excel Array Formulas and LET & LAMBDA Functions Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight

by
  • ISBN13:

    9781615470822

  • ISBN10:

    1615470824

  • Format: Paperback
  • Copyright: 2025-04-01
  • Publisher: Holy Macro! Books
  • Purchase Benefits
List Price: $24.48 Save up to $0.02
  • Buy New
    $24.46

    NOT YET PRINTED. PLACE AN ORDER AND WE WILL SHIP IT AS SOON AS IT ARRIVES.

Summary

Array Formulas have been around since the beginning of Excel. The author wrote a whole book about them back in 2012. Array Formulas were powerful, but complicated. Then Microsoft invented the new Worksheet Calculation Engine in Microsoft 365 Excel and just like that, 40 years of Excel worksheet formula methods changed overnight. Dynamic Spilled Array Formulas, born inside this new calc engine, can now spill results from a single cell and dynamically update when new source data changes. Now we don’ t have to lock most cell references; no need to manually copy most formulas, editing formulas is now a simple task, and insane old school filtering, sorting and unique list formulas are a thing of the past with new Array Functions like FILTER, SORT and UNIQUE. Microsoft also gave us the LET function which eliminates duplicate formula elements by allowing us to define variables within the formula itself. In addition, Microsoft gave us the LAMBDA function which allows us to define re-usable functions without using VBA code. There are also LAMBDA Helper Functions like BYROW which solves the problem of “ how do I spill an aggregate total?” Does all this sound too good to be TRUE? Too good is no

Author Biography

Mike "excelisfun channel" Girvin is a business instructor at Highline Community College since 2002 and a Microsoft Excel MVP since 2013. He has created over 3,200 Excel videos at YouTube. He lives in Seattle, Washington.

Table of Contents

1. Old School Vs. New School 2. Define Array Formulas 3. Types of Array Operations: Direct, Function Argument, Array Function 4. Dynamic Spiled Array Formulas vs. Scalar Array Formulas 5. Array Operation Configurations 6. Array Functions: FILTER, UNIQUE, SORT, VSTACK, HSTACK, DROP, TAKE and more 7. Conditional Calculations and Logical Tests 8. LET Function 9. Array Formula Budgets and Reports 10. Defined Names 11. LAMBDA function 12. LAMBDA Helper Functions: BYROW, BYCOL, MAP, SCAN, REDUCE and more 13. Single Cell Reports

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Rewards Program