Dynamic Array Formula Spill Ranges With Charts

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.

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.

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.

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

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

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

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.

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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Add Vertical Lines To Excel Charts Like A Pro! [Guide]

There are a few creative ways to add a vertical line to your chart bouncing around the internet. If you...

Hiding A To-Date Column When Reporting Month or Quarter End Results

The "To-Date" Column I was first introduced to this concept a few years ago when I first started working in...

How To Create Excel Progress Bar Charts (Professional-Looking!)

Creating A Progress Bar In Excel Progress Bars are simple graphics that can be quite visually powerful by instantly providing...

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.