Spilling is Fulfilling in Excel!

Overview

Have you used any of Excel’s dynamic array functions (DAFs) yet?  If not, you’re missing out on possibly the biggest update to Excel, possibly ever.  Even though they’ve been around for a few years (launching in 2018), it seems like only advanced users have worked them into their routine toolsets.  The biggest benefit of DAFs is the ability to return multiple cell values that spill across multiple cells (hence the “dynamic” in the name).

DAFs can completely change your Excel approach – in a positive way.  In this article, we’ll discuss what DAFs are, why you should use them, some tips and gotchas, some DAF combination formula examples, and a few macros that make them even quicker and easier to use.

What are DAFs?

Why should you use DAFs?

There are several key reasons to use DAFs:

• They make it easy to work with arrays, a concept that most users seem to be scared of.  Working with arrays is possible without DAFs, but it’s quite a bit more difficult and not nearly as flexible.
• They bring together complex calculations in a single formula rather than splitting them into separate formulas or columns.  When defining them with a custom LAMBDA formula, you can then use them all throughout your workbook and just tweak the LAMBDA formula in one place.
• They reduce error risk by consolidating several steps into one formula.  The more formulas you have, the greater the chance of errors from typing or copying and pasting them.
• They automatically expand and contract based on your source data.  This is huge because it puts the dynamic in DAFs!

Tips & tricks

Here are several tips to help you use DAFs more effectively:

• CHOOSE, INDEX, and SEQUENCE are extremely useful functions to use within spilling formulas.
• UNIQUE, FILTER, and SORT are useful for manipulating the data within your spilling formulas.
• Use the #hashtag character to refer to an entire spilled range instead of just one cell (i.e. reference B2# instead of just B2).
• Combine the #hashtag with the : (colon – which means union in Excel) to include other cells like headers (i.e. A2#:A1).
• When you select a cell within a spilled range, you’ll see a blue border around that spilled range. If you’ve selected any cell other than the top-left cell in the spilled range, the formula will be grey (the top-left one will still be black).
• Many non-DAF formulas can also now spill when you supply a range of cells as an argument (i.e. LEN(A2:A20)).

Gotchas

Be aware of several gotchas related to DAFs:

• If your data is spilling down and/or right and there is a cell value within the spill path, Excel will return the #SPILL error.  Update the spilling formula or clear the cell values in the spill path to fix it.
• If spilling is not working as intended, check your version of Excel. If it’s older than 2018, it probably doesn’t support DAFs and spilling.
• DAFs don’t work within tables.  Tables are often the source references for extensive DAF formulas.
• DAFs don’t play nicely with traditional sorting and filtering (leave those to the source data your DAFs refer to!).

DAF combination examples

Macros that help

The XLEV8 Excel Add-in contains several macros that help you work more efficiently with DAFs:

• Toggle Spilled Formula – this lets you convert a variety of formulas into dynamic, spilling formulas and back to non-spilling again.
• Insert Counter Formula – this lets you insert a dynamic counter formula that expands with data to the right..
• Toggle Lookup – this lets you toggle between VLOOKUP, INDEX/MATCH, and XLOOKUP (generally the best option for spilling!).

Summary

If you want to make your files extremely dynamic with the most modern approach in Excel, DAFs are a great option.  They work well together and with other Excel tools like Power Query and VBA.  Use the tips, gotchas, and examples above to get started with them and take your Excel skills to a whole new level!

Have you used DAFs extensively in Excel?  Any additional tips or tricks to share?  Let us know in the comments below!