Dynamic Array Formula Spill Ranges With Charts

By Chris Newman •  Updated: 09/10/23 •  4 min read
How to create multiline text in ribbon tooltips with Excel.

Linking Dynamic Array Formulas into Chart Series

With the introduction of Dynamic Array functions in Excel, your spreadsheets can now provide data outputs that expand and shrink through Spill Ranges based on the data you are working with.

Imagine having the ability to generate charts that automatically adapt as your data evolves, all without the need to constantly adjust your formulas or chart ranges. This is precisely what Dynamic Array functions offer. They empower Excel users to perform complex calculations and return multiple results in a single cell or cell range, often referred to as a Spill Range.

Screentip and Supertip example in Excel.

In this article, we will explore how to leverage the power of dynamic array functions in Microsoft Excel graphs. I’ll walk you through the process of referencing dynamic array function outputs directly within your charts, enabling you to create dynamic and interactive visualizations that adapt in real time to changes in your data. Whether you’re a data analyst, financial professional, or Excel enthusiast, you’ll find these techniques invaluable for presenting data-driven insights with precision and ease.

So, let’s dive into the world of dynamic array functions and discover how they can elevate your Excel charting game to new heights.

Step 1: Create A Named Range For Spill Range

Unfortunately, Excel’s Chart data series cannot reference a Dynamic Array function’s output (Spill Range). However, there is a workaround to this annoyance. You can point a Named Range to the Spill Range and then reference that within your Chart series.

To create a named range select the first cell in the Spill Range (the cell the formula resides in).

Navigate to the Formulas Ribbon Tab and click the Define Name button within the Defined Names button group.

A super tip text example in Excel's Ribbon.

You should see the New Name dialog box appear. To create a new Named Range, provide a reference name (no spaces allowed in the name). You will also need to populate the Refers To range reference input.

The Refers To input should reference the first cell in your Spill Range and also have a hash symbol (#) after the cell reference.

A super tip text example in Excel's Ribbon.

For our graph example, we will also want to create an additional Named Range to reference the x-axis labels in our bar chart.

A super tip text example in Excel's Ribbon.

Now that we have created both the Named Ranges needed to create our Bar Chart, let’s delve into how to link them into Excel Chart objects.

Step 2: Reference Spill Range In Chart Series

Chart – Edit Series

To reference a Named Range within a Chart series, you need to include both the Filename and the Named Range name.

For our example, the Series Values formula was written as:

=’Spill Range To Chart.xlsx’!AvgSalary

A super tip text example in Excel's Ribbon.

Chart – Axis Labels

To reference a Named Range within the Axis Labels, you need to include both the Filename and the Named Range name.

For our example, the Axis Label Range formula was written as:

=’Spill Range To Chart.xlsx’!Departments

A super tip text example in Excel's Ribbon.

After you have input range references for both your Chart Series and Axis Labels, your chart should populate with the data associated with your Dynamic Array formula’s Spill Range.

A super tip text example in Excel's Ribbon.

Your chart will now dynamically change in accordance with the data that is output from your dynamic array functions.

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can link your Dynamic Array formulas to Excel Chart objects through the use of Named Ranges. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!