Up Up and Array! : Dynamic Array Formulas for Excel 365 and Beyond

Understand the power of dynamic arrays: a single formula can generate new and vastly more efficient spreadsheet possibilities. This book introduces spreadsheet users to dynamic array functions in Microsoft Excel 365, defines and details the distinctive ways in which they work, and shows how they can be applied to a wide swath of data-analytic tasks. While array formulas and functions have long held a place in the spreadsheet toolbox (although, for many of us, shunted to an obscure corner), the dynamic array engine offers a more user-friendly and intelligible set of means for manipulating spreadsheet data in the array mode. The single-formula, multi-cell capability of dynamic arrays has been extended to nearly all existing spreadsheet functions, offering a new, default way of working. As a result, many tasks can now be executed with dynamic arrays without having to resort to the new functions at all. After defining arrays and dynamic array formulas, this book helps you examine the dynamic array property of lifting and how it impacts the formulas, including those written with existing functions. Plenty of illustrations and formulas along the way help you get comfortable using them. From there, you will learn Excel 365’s new dynamic functions, including the 14 currently in rollout, each accompanied by instructive examples. In many cases, the examples demonstrate how the new functions can work with long-available functions, such as MID, IF, COUNTIF, etc., which now also boast dynamic array functionality. What You Will Learn Unlock the dynamic array potential in Microsoft Excel Apply dynamic array functions and confidently direct them to real-world spreadsheet tasks Know the distinctive ways in which arrays work and can be applied to numerous data-analytic tasks Who This Book Is For Users of Excel 365 and beyond who are comfortable with, but not necessarily expert in, formula writing, as well as those who are unaware of or not fluent with dynamic arrays. It is also valuable to data journalists and other professionals in need of spreadsheet skills who may unaware of dynamic arrays, and the time they could save by applying them to their work. Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: What’s an Array, Anyway? A Working Definition Now for Something Different What We Mean – and What We Don’t Mean Introducing...Dynamic Arrays So What’s New? Some Points to Bear in Mind Chapter 2: Array Formula Basics The Old and the New An Important Reminder Remembrance of Keystrokes Past Back to the Basics The One-Formula Grader The Return of the Brackets Getting Re-oriented More of the Same But a Workaround Is Available Summing Up Chapter 3: Dynamic Arrays: Time for Some Heavy Lifting The Spill Is Gone You Can’t Spill on This Table The # Sign: Weighing In on Its Virtues Raising Some More Points About Lifting Now for a New Angle PMT Permutations More Lifting – but This Time, Inside a Cell Field Notes: Field and Dataset Names Next Up Chapter 4: The SEQUENCE Function: Made to Order What’s New About Sequence The Possibilities Proliferate Spates of Dates PMT, Again: The Sequel, with Sequence The One-Formula Multiplication Table Rewriting the CODE Looking Ahead Chapter 5: UNIQUE: A Singular Function What’s Unique About It? How It Works UNIQUE Stretching Across Multiple Fields Distant Fields It’s Starting to Add Up Up Next Chapter 6: SORT and SORTBY: The ABCs (and the CBAs) How SORT Works Sorting by Multiple Fields Sorting Digits: All in the Same Cell SORTBY: SORT’s Field Correspondent Coming Next Chapter 7: The FILTER Function: Cutting the Data Down to Size The Formula Now About That [If Empty] Argument Multiple Filter Criteria: Getting More (or Less) Out of the Data OR Consider This Alternative Mix and Match: AND and OR Together How About Filtering Part of a Cell? There’s More FILTER’s Number Wizardry Adds Values to Your Values No More Blank Looks About Blank Rows Coming Next Chapter 8: RANDARRAY: Unpredictably Useful How It’s Written Some Uses, Please Rolls of the Dice, Virtually Coming Next Chapter 9: The Implicit Intersection Operator: The Function You’ll Probably Never Use Where You Might Use the Intersect Operator – Maybe They’re Here, Probably: The Newest Dynamic Array Functions Chapter 10: TEXTSPLIT, TEXTBEFORE, and TEXTAFTER: Putting Words in Their Places Coming to a Hard Drive Near You TEXTSPLIT: Piecing It Together Choose Your Delimiters You Won’t Always Ignore “Ignore Empty” Launching “Pad With”: A New Kind of Argument TEXTSPLIT and the One-Celled Dataset TEXTSPLIT Can’t Do Everything – but with Good Reason There Is a Plan B TEXTBEFORE and TEXTAFTER: Usefully Limiting the Delimiters How They’re Written – and How They Work TEXTAFTER: The Flip Side of TEXTBEFORE Front and Center: Extracting Middle Names Sorting Last Names: Sort of Tricky Some Concluding Words Chapter 11: TOCOL and TOROW: Straightening Out the Data How They’re Written – and What They’re About Drawing a Bead on the Blanks TOROW Is Slightly Different Lining Up the Conclusions Coming Attractions Chapter 12: WRAPCOLS and WRAPROWS: Giving Some Direction to the Data How They’re Written Some Formulaic Teamwork: TOCOL and WRAPCOLS Resorting to a Re-sort Multi-tasking: A Two-Column Sort by Last Names Coming Up Next Chapter 13: VSTACK and HSTACK: What They’re About How VSTACK Is Written When the Data Aren’t There Another Problem, and a Workaround HSTACK: Not Just VSTACK at a Right Angle Using HSTACK to Rearrange the Whole Dataset HSTACK and the One-Formula Name Split Coming Up Chapter 14: CHOOSECOLS and CHOOSEROWS: Less Is More How CHOOSECOLS Is Written Teaming with Other Functions CHOOSECOLS and Too Much Information CHOOSEROWS: Record-Braking Where the Row Count Starts Unique Records in Non-adjacent Fields: Take 2 Using CHOOSEROWS to Fill in the Blanks Coming Next: Subjecting Rows and Columns to a Different TAKE Chapter 15: TAKE and DROP: Selecting and Rejecting the Data Similar – but Different How They’re Written DROP: Leaving the Data Behind Some Real-World Uses A Unique Exercise More Ranking – but by Percentages Letting It DROP: Removing Lowest Grades Up Next Chapter 16: EXPAND: Bulking Up the Data How It’s Written – and Why That Question – Again Now About Those Zeroes... So Do We Even Need EXPAND? Summing Up EXPAND and Unpivoting Data EXPANDing Its Horizons We’re Almost There For Extra Credit... Index