How to calculate Top 10 with formulas in Excel

Top 10 lists are a common form for displaying information, especially on dashboards and summarized reports. It is easy to create a top 10 in Excel when working with sorted data; just cell link to the top 10 items in the list… easy! It’s also relatively simple when using AutoFilter, Tables, and PivotTables, as it Read More

Advanced dynamic array formula techniques

In the final part of this dynamic array series, we’ll look at a few advanced formula techniques.  We won’t be covering the individual functions in detail but considering how we can combine them to solve some tricky problems.  Many of these techniques have been covered briefly as examples in previous posts, but now we’ll dig Read More

Using dynamic arrays with other Excel features

So far, in this series, we’ve looked at how dynamic arrays work and the new functions that are available to us.  Most of our examples have focused on calculations that occur on the worksheet.  But we’ve not yet considered how dynamic arrays work with other Excel features, such as charts, data validation, conditional formatting, etc.  Read More

SEQUENCE function in Excel

The SEQUENCE function is one of the new dynamic array functions which Microsoft released as part of introducing dynamic arrays. This function makes use of changes to Excel’s calculation engine, which enables a single formula to display (or “spill” if using the new terminology) results in multiple cells. In the case of SEQUENCE, it will Read More