Overview
If you haven’t yet tinkered with the new(ish) Dynamic Array Functions (aka “DAFs”) in Excel, you’re missing out! They can add a significant amount of automation to your files, saving you time and reducing risk. They can return multiple values and spill over multiple cells down or to the right. To learn more about spilling files, check out a separate in-depth article or grab a copy of our DAF guide covering all 36 DAF functions with examples, how-tos, and gotchas.
While many of the newer functions naturally reflect this spilling behavior, you may want to use it for legacy formulas you’ve built. Good news – you can make virtually any formula spillable!
In this article, we’ll look at a couple of approaches that can make your formulas spillable, and the tools I use to do this in just one step. Make sure to download the example file below to see these options in action!
Add an auto-updating counter
Many people use Excel for basic lists like shopping, to-do tasks, and light databases. To keep track of how many items are on the lists, the first column often has a list of numbers with a basic formula (like “=A2+1”). The problem what that basic formulas is that you have to manually update it. Not with dynamic array functions!
See the screenshot below, where we’ve used the SEQUENCE and COUNTA functions to automatically spill the results over multiple cells, which automatically grow with new records and shrink as you delete records.
The formula in cell A2 is “=SEQUENCE(COUNTA($B$2:$B$1502))” where the COUNTA function finds the number of non-blank cells in column B to use as the number of cells to spill over to in sequence.
I use this approach so much that I ended up making a macro that inserts it in one step, adjusting for the cell address you want to insert it to. See it in action in the video below or learn more in the product manual.
Spill any formula
The example above uses the SEQUENCE function, which will spill by default, assuming you give it parameters that resort in multiple values. But what about formulas that don’t do this by default, such as those that just reference another cell? Those can spill too!
Look at the example in the screenshot below. In column A, we want the formula to fill down for each row where there’s a value in cell C2. Here’s the formula before we apply spilling:
=IF(C2=”Copy and Paste”,”COPY”,D2)
Notice there are references to cells C2 and D2. We want those to spill so that we are returning the results of this formula down to the last cell used in columns C and D. Here’s the formula after we apply spilling:
=IF(OFFSET(C2,0,0,MAX(COUNTA($C$2:$C$5002),1),1)=”Copy and Paste”,”COPY”,OFFSET(D2,0,0,MAX(COUNTA($D$2:$D$5002),1),1))
Notice the creative use of the OFFSET, MAX, and COUNTA functions. OFFSET is starting with C2 and extending the range based on the number of cells to the bottom, which MAX and COUNTA are providing. MAX ensures we have at least one cell, and COUNTA will count how many blank cells are in our range.
So in the screenshot below, instead of just referencing C2 and D2, those functions return C2:C26 and D2:D26. And remember, those will update automatically based on adding or deleting rows to your dataset!
Similarly to the counter formula above, I also use this one a lot, so I made a macro that can apply the changes we see in the before and after formulas above. But it can also toggle it back to non-spilling in case there are issues or to share with someone who has an older version of Excel that doesn’t support the DAFs. See this macro in action in the video below or learn more in the product manual.
Video Walkthrough
Summary
If you’re looking to make your make your Excel files more dynamic, minimize the risk of errors, and increase the wow factor, DAFs are a great tool to know about. The spillable approach is completely changing the way Excel pros model and build advanced files, but you can leverage them even in more moderate ways. The two approaches covered in this article are great ways to apply them.
Have you been tinkering with DAFs and found some neat tricks? Let us know in the comments below!
Recent Comments