Friday, July 1, 2022
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

How to Smooth Data by Using the TREND Function

Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.

Consumer Sentiment Suggests a 2022 Recession

In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...
Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.

Simple Transformations Can Reveal Hidden Patterns in Your Data

Excel users have at least two significant advantages over business professionals who rely on other analytical and reporting tools. First advantage: We Excel users can...