Using dynamic arrays with other Excel features

0038 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.  So, we’re going to cover that in this post.

We won’t look at every Excel feature, but focus on the most common that we are likely to use dynamic arrays for.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0038 Dynamic arrays with other Excel features.xlsx

Overview

When thinking about dynamic arrays, it’s useful to make a clear distinction between ranges and arrays.  We often use these terms interchangeably, but they are not the same, and Excel certainly applies them differently.  If we have a clear grasp of this, it will be easier to understand how dynamic arrays behave the way they do.

Arrays vs. ranges

Excel converts data between arrays and ranges without the user even being aware.  Let’s think about the journey our data goes through by using the SORT function as an example.  We have some source data in cells B3-C5.

From Range to Array to Range

The formula in cell E3 is:

=SORT(B3:C5)

When we use the SORT function, what is actually happening:

  1. The range B3:C5 is converted to an array: {“Banana”,10;”Pear”,15;”Apple”,5}
  2. The SORT calculation is performed on the array, turning it into {“Apple,5;”Banana”,10;”Pear”,15}
  3. The result of the array is returned into cell E3
  4. Once the result exists on the worksheet, it becomes a range which we can refer to as E3#
  5. Even though # references are on the face of the worksheet, when called, Excel still needs to recalculate to understand the size of the range as E3:F5.

This means that generally speaking, the following statements hold true:

  • Features which can handle arrays and perform calculations can contain dynamic array formulas
  • Features which can handle calculations, but not arrays, can use the # referencing system
  • Features that cannot perform calculations must use other methods to handle this for them.

Tables

Dynamic arrays have a love/hate relationship with Excel tables.

If we use Excel tables as the source for a dynamic array formula, everything works brilliantly.  If new data is added to the table, the formula updates automatically to include the new data.  That’s the love part.

However, try to do the inverse and put a dynamic array formula inside an Excel table, and we get the #SPILL! error.  That is the hate part. Arrays are about values, and tables hold values, so why is there a problem?

Dynamic Arrays and Tables v2

In the screenshot above:

  1. Cell G3 contains the following formula:
    =SORT(Table1)

    This uses the full range of values from Table1 and outputs on the face of the worksheet.

  2. The formula in cell L3 is:
    =G3#

    This cell reference is the spill range starting in cell G3, but it always results in a #SPILL! error.

Why does (1)  work and (2) does not?

  1. When using a dynamic array function with a table as its source, it is converting the table into an array and then returning that result somewhere else on the worksheet.  Therefore the source and output do not cross over.
  2. When using a dynamic array inside a table, it causes issues.  Both items are containers for auto-expanding data.  If you try to put something which auto expands into something else that also auto expands, what should happen?  Which should expand first? What if one grows outside the bounds of the other?  There are just too many questions without obvious answers.  Therefore the #SPILL! error is probably the best we can expect.

Name manager

A reader once commented to me that they thought named ranges were misnamed, that they should be called named formulas.  I have come to agree, as the name manager can hold a host of other things such as constants, arrays, Excel 4 macros, and formulas.

In this section, we’ll look at just two items (1) arrays and (2) spill ranges.

Arrays

As the name manager can hold arrays and formulas, it is more than happy to hold dynamic array functions directly too.  The screenshot below shows a named range containing the SORT function.

Named range with array

We can output the named range on the face of the worksheet (as shown in the screenshot below, cell G3 and its spill range), or use it in features that accept arrays.

Named range array output

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Spill ranges (# references)

The name manager is also happy holding # spill references, but we need to be careful about their creation.  While named ranges can be used with absolute or relative cell references, to reference a spill range correctly, we must ensure the $ symbols are used to create an absolute reference.

Named range with spill range # reference

The named range created in the screenshot above has been used in the screenshot below (Cell L3 and its spill range)

Named range spill range output

Using named ranges with other features

As we go through the remainder of this post, you will see that named ranges are key to using dynamic arrays with many other features.

Even though a spill range is recognized as a range for formulas, it appears that Excel needs to perform some background processing to work out how big the range is.  The name manager can perform calculations, so it can trigger this processing.  Features that don’t contain any calculations can use named ranges as their source to handle this part for them.  We’ll look at this further in the sections below.

Charts

Charts can be a little picky about dynamic ranges already.  I believe this is because the charting engine does not try to calculate any ranges, but just wants to use the range it has been given.  This hasn’t changed with the introduction of dynamic arrays; it has always been the case.  Even before dynamic arrays, if we wanted to use formulas such as INDEX or OFFSET to create a dynamic range, we needed to place it in a named range.

The screenshots below demonstrate that using a spill range directly in a chart source will result in an error.

Chart Error when using # in the source

Instead, we need to use a named range containing a spill reference.

Named range within a chart source

It should be noted that the output of the named ranges must be of the right type of data for the chart element.  By which, I mean that named ranges used for chart values must contain numbers and named ranges used for axis labels must contain text values.

Charts can be created using arrays; therefore, we can also use a named range containing a dynamic array formula as the chart source.

Linked pictures

Linked pictures are similar to charts; they don’t try to calculate anything; they just want to display the range they are given.  Therefore, using a # reference directly inside a linked picture won’t work, we’ll turn to the name manager once again.

The screenshot below shows a picture linked to a named range called NamedRange_LinkedPicture.

Linked Picture with Spill Named Range

When the results change, so does the picture; it grows or shrinks with the size of the spill range.

For the purposes of demonstration of another technique, I’ve pulled a bit of Excel trickery in the named range used in the linked picture above.  I have joined two spill ranges together into a single named range (as shown in the screenshot below).

Named range with two spill ranges

The formula in the name manager is:

='Linked Picture'!$G$3#:'Linked Picture'!$H$3#

These are two separate spill ranges, which have been combined as a single range using a colon in between.  This creates a range that goes from G3 down to the end of the spill range for H3#.

Pivot Tables

Initially, Pivot Tables appear to be happy using the # reference as a source, as shown by the screenshot below.

PivotTable with a # reference

But don’t be fooled, as soon as we click OK, Excel converts the # reference to a standard static range and then creates the Pivot Table.  The screenshot below shows that ‘Pivot Table’!$G$3# has been converted to a static range ‘Pivot Table’!$G$3:$J$10

Pivot Table automatically changes back to standard reference

Instead, we can turn to named ranges once again.

Pivot Tables make an assumption about our data, which is that the first row is the header row.  Which gives us an issue as # references would generally exclude the header.  Instead, we can pull some more Excel trickery.

If our data is as follows:

Spill References used for Pivot Tables

To include the header row, we can use the following formula in a named range; it creates a range from G2 to the bottom of the G3# spill range.

='Pivot Table'!$G$3#:'Pivot Table'!$G$2

Here is the formula used within a named range.

Pivot Table with named range needs header

We can now use the named range as the source for the Pivot Table.

Pivot Table works with named range

This now correctly includes the header row so that we can use it for the Pivot Table (as shown below).

Pivot Table based on named range

Conditional formatting

Conditional formatting is not compatible with dynamic arrays.  If you use # references inside a conditional format, Excel will convert it to a static range and it will no longer be dynamic.

The screenshot below demonstrates that when using a spill range ($B$3#), clicking Apply will convert the range to a static range ($B$3:$E$10).

Conditional Formatting - Looses the # Reference

To use conditional formatting, we have to select a static range that is bigger than our likely output.  That will give the appearance of being dynamic, but may need maintenance from time to time when the spill range expands further than the conditional formatted cells.

Data validation

Data validation lists perform calculations; therefore, they work exceptionally well with the # referencing system by themselves.  But they don’t use arrays, so can’t hold a dynamic array formula.

The screenshot below shows a data validation list containing the spill range starting in cell G3.

Data Validation with # reference

The data must still be in the right shape to work with data valuation, so it must be a single row or column.  If we want to use a named range, that will work too.

Conclusion

We’ve seen that many Excel features are directly compatible with dynamic arrays.  And for most features that aren’t, we can use the name manager to bridge the gap.  By looking at the general rules it has provided a good understanding of how dynamic arrays will operate when used with other Excel features.

Want to learn more?

There is a lot to learn about dynamic arrays and the new functions.  Check out my other posts here to learn more:

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

3 thoughts on “Using dynamic arrays with other Excel features

  1. Peter says:

    Hi; After reading about the Spill error that can appear within a Table, you noted above “Therefore the #SPILL! error is probably the best we can expect.”

    I found a solution; I ran my dynamic array formula outside of my table in a “helper” column; then from the column inside my table where I want the result to show up, I point to this helper column. The table continues to expand as more data is added, as does the helper column and the dynamic array continues to work as intended. Hope this info is of some help. Cheers

    • Excel Off The Grid says:

      Hi Peter – what a great idea. I’d not thought of that, and I’ve not seen anybody else do that either. You may have just discovered a new technique. I wonder what new possibilities this will lead to. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *