Excel Dynamic Arrays Straight to the Point 2nd Edition

by Bill Jelen

Overview

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. This book covers the new functions added for Dynamic Arrays: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY. It shows how Dynamic Arrays make the new XLOOKUP even more powerful. 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. Learn how to use the new # and @ operators in your formulas. After fifteen months of preview, the Dynamic Arrays are reaching general availability. Learn how the FastExcel SpeedTools add-in offers much-needed improvements, such as TOTALS, SLICES, VSTACK and UNPIVOT. Join in lobbying the Excel team to incorporate these improvements.

Product Details

ISBN-13:	9781615472659
Publisher:	Holy Macro! Books
Publication date:	01/14/2020
Series:	Straight to the Point
Sold by:	Barnes & Noble
Format:	eBook
Pages:	80
File size:	7 MB

About the Author

Host of MrExcel.com and a Microsoft MVP in Excel for 10 years. Host of MrExcel.com and a Microsoft MVP in Excel for 10 years.

Table of Contents

Part 1 - Preparing Your Data and Adding Subtotals How to Set up Your Data for Subtotals How to Fit a Multiline Heading into One Cell No Tiny Blank Columns Between Columns How to Sort Data Sort Days of the Week Sort a Report into a Custom Sequence Add Subtotals to a Data set Part 2 - Subtotal Tricks & Techniques Use Group & Outline Buttons to Collapse Subtotaled Data Manually Apply Groups Group Report Sections Subtotals Above Each Group Copy Just Totals from Subtotaled Data Sort Largest Customers to the Top Format the Subtotal Rows Add Other Text to the Subtotal Lines Subtotal One Column and Count Another Column Subtotal by Month With Daily Dates Subtotals by Product Within Region Add a Page Break For each Group My Manager Wants a Blank Line After Each Subtotal Add a Calculation Only to the Subtotal Rows Shift Subtotals One Column to the Right Finding G/L Accounts Not in Balance Part 3 - Subtotal Troubleshooting Why Is Final Subtotal Row Appearing After Many Blank Rows? Enter a Grand Total of Data Manually Subtotaled Why Does Subtotal Dialog Sometimes Default to Count? Adding Subtotals to Dozens of Columns Can You Get Medians? Horizontal Subtotals Subtotals Don't Work with Tables Part 4 - Using the SUBTOTAL function without Invoking the Command Adding a Total Visible Below a Filtered Data Set Using 109 For Rows Manually Hidden The 9 Basic Arguments The 9 New Arguments SUMIF or COUNTIF of Filtered Data Part 5 - Introducing AGGREGATE New Arguments for What to Ignore New Arguments for Aggregation Function Some Functions Require a Fourth Argument in AGGREGATE How is SMALL or LARGE Different than MIN or MAX? Doing MINIFS or MAXIFS Before They Existed Part 6 - Using VBA with Subtotals Creating Subtotals Using VBA VBA & TEXTJOIN to Subtotal Dozens of Columns Part 7 - Replacing Subtotals With Power Query Using Power Query to Get #2 View of Subtotals