Tuesday, July 27, 2021
Home Formulas & Functions Dynamic Array Formulas

Dynamic Array Formulas

Introduced in 2019, dynamic array formulas automatically “spill” to the number of cells needed to display the entire range of results. In contrast, earlier versions of Excel require that we enter array formulas using Control + Shift + Enter.

I used the FILTER function to give me IFS-like power for PERCENTILE.INC. But then I realized the same pattern brings IFS-like power to many functions—including MEDIAN, STDEV.S, GEOMEAN, and perhaps even the FORECAST functions!

Using the Hidden Power of Excel’s FILTER Function

When you take the time to explore a new worksheet function, it's amazing what you can discover! And I won't tell you about the really...
Using Excel's LET, SORTBY, and SEQUENCE functions, and dynamic arrays, you easily can list any number of top and bottom results from a Table.

How to Report Top and Bottom Results Using Dynamic Arrays in Excel

In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...
Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.

How to Stack and Shelve Dynamic Arrays

While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of...
In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.

How to Use Array Formulas to Report Groups of Accounts

Often, in accounting, marketing, and other departments, we'd like to report and analyze groups of items rather than one item at a time. In Part...

Latest Articles

Excel users made such a mess of nested IF functions that Microsoft created the IFS function to help. Here's how to tame both functions.

How to Tame Nested IF Functions in Excel

When I review my Excel formulas from many years ago, one of the types of formulas that I'm most embarrassed about today is the...
Excel's STOCKHISTORY function can return decades of history about the prices of stocks for thousands of public companies from many countries. Here's an introduction to that function.

Introducing Excel’s STOCKHISTORY Function

The STOCKHISTORY function recently showed up in my non-beta version of Excel 365. Its appearance was a surprise because it wasn't announced as a What's...

A Lesson in Excel Analysis Learned from Rising US Inflation

Looking at the trends in the annual rate of change (ROC) of business and economic measures is an excellent way to reveal hidden information...
Advertisement
Advertisement