Charting a Spill Range

The past several posts have illustrated various ways to apply dynamic array formulas and their spill ranges. Kent asked if we could use spill ranges to create a dynamic chart to visualize the future value of an investment portfolio. For example, start with someone’s current portfolio value and then apply an assumed growth rate and annual investment amount. This post demonstrates how to compute the annual future value and send the resulting spill range into a bar chart. Plus, when any assumptions are changed, including the number of years, the chart is automatically updated. Thanks for this great idea Kent!

Objective

Let’s take a step back and confirm our goal here. We’d like the user to be able to enter a few key assumptions, namely, the number of years in the analysis, the current portfolio value, the annual investment amount (addition to portfolio), and growth rate. We would enter these into some cells like this:

Then, we want the future value for each year to be displayed in a chart, like this:

Then, if the user changes some values, perhaps the number of years to 20, we want the chart to update accordingly:

We’ll build this using the following steps:

  • Compute the future values
  • Create a defined name
  • Build the chart

Let’s get to it.

Note: dynamic arrays and spill ranges are available on a limited basis at the time I’m writing this post. So, your version of Excel may not have these capabilities. At the time I’m writing this, they are available to O365 subscribers with the Insiders Fast channel. If you are an O365 subscriber, you may be able to switch to the Insiders Fast update channel. See the Microsoft website for more info.

Compute the future values

First up is computing the future values of the portfolio, given the assumed growth rate and additional annual investment. We can use Excel’s future value function (FV) with the help of the SEQUENCE function.

Let’s take another look at the assumptions so we can see their cell references:

Now, the FV function computes the future value, given the interest rate (C7), number of periods (C4), periodic payment amount (C6), and present value (C5). So, we start by translating our assumptions into the FV arguments. For example, we write the following formula into B12:

=FV(C7, C4, C6, C5)

But, that formula returns only a single value, the future value at the end of 10 years. What we want to see instead are all 10 future values … one row for each year. How can we do this?

Well, we can use the SEQUENCE function. This function returns a given number of rows (and/or columns) based on its arguments. So, we just need to make a minor adjustment to our formula so that the SEQUENCE function returns 10 values … 1 through the number of years in C4:

=FV(C7,SEQUENCE(C4),C6,C5)

Since the FV function operates on a cash flow model, it returns a negative value given the assumptions above. So, we’ll need to flip the sign, which we can do a number of ways including adding a dash in front, like this:

=-FV(C7,SEQUENCE(C4),C6,C5)

We update the formula in B12 and hit enter, bam:

So far, so good. Now we just need to send these results into the chart. But, we’ll make a quick detour first and set up a defined name.

Create a defined name

At the time I’m writing this post, some Excel features (for example, charts) don’t fully support spill range references, such as B12#. Often, we can work around this limitation by using a defined name instead. We just need to create a new defined name that references the spill range, and then use the defined name instead of the spill reference.

To create the defined name, open the Name Manager (Formulas > Name Manager). Then, click New to reveal the New Name dialog. We enter the desired name, such as ChartData, and then select the formula cell and type # at the end to reference the spill range, like this:

Click OK and close. With our name set up, we are ready to create the chart.

Build the chart

We get our starter chart going by selecting any cell in the FV column and then inserting the desired chart (Insert > Clustered Column Chart).

Excel inserts a basic chart, and it looks like we are done:

But … as soon as we change the number of years, we see that our chart does not update. Don’t worry … we just need to make a minor update to the chart so that it uses our defined name.

To do so, click the Chart Tools > Select Data command. The Select Data Source dialog appears, like this:

Click the Edit button on the Legend Entries (Series) side to reveal the Edit Series dialog:

We just need to change the A1-style range reference ($B$12:$B$21) to our defined name ChartData, like this:

Now, we are good to go. We can change any assumption, including the number of years, and the chart updates accordingly.

If you have any other spill tips, please share by posting a comment below … thanks!

Sample file: ChartSpill.xlsx

 

 

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

7 Comments

  1. Richard Jones on March 20, 2019 at 7:25 am

    These dynamic array functions are brilliant and will provide users with more flexibility and clarity. However, Microsoft are really messing up the insider versions, where I’ve had 2 major issues on 2 different upgrades, the latest one when I couldn’t even open any Office apps (19th March) and had to downgrade back to targeted monthly channel which unfortunately does not have these functions yet

  2. Tom Van Dam on March 20, 2019 at 7:48 am

    Very interesting formula. I am using Excel 2016 and it doesn’t seem to recognize the Sequence function that you entered. Also, when I opened your sample spreadsheet, you had changed the formula in your instructions to include _xlfn.Sequence. What is the _xlfn doing?

    • Jeff Lenning on March 20, 2019 at 8:53 am

      Hi Tom,
      Excel 2016 perpetual licenses aren’t getting these new features, including SEQUENCE or spill ranges. Sorry about that! It is available to subscription licenses (O365) because subscriptions receive periodic updates including enhancements. The reason the files has strange functions and doesn’t work quite right is just because that version of Excel doesn’t support SEQUENCE or spill ranges. Hope this helps!
      Thanks
      Jeff

  3. Wayne Edmondson on March 20, 2019 at 4:28 pm

    Hi Jeff.. thanks for the excellent tutorial on using DAFs to expand on traditional formula results. In the past, I would have created 10 separate FV() formulas in a table to do the same. As you have shown, DAFs will make it a one stop affair with a spilled range. I hope MS sees fit to allow the chart engine to eventually recognize spilled ranges with the # mark. This will remove one step and make it pretty easy to create dynamic charts against dynamic spilled formula results. As you pointed out, until then, a quick named range will suffice. I’ve got DAFs on a laptop set for the Insider Channel. As Richard Jones pointed out above, I’m reluctant to enable Insider on my main work machine until MS does an official release.. can’t afford to be offline when it comes to making a living. Until then, I play with them on the laptop. Thanks for all of your great videos and tutorials which I find to be both informative and entertaining. Thumbs up!

    • Jeff Lenning on March 20, 2019 at 4:30 pm

      Thanks Wayne 🙂

  4. Lee Hibbert on February 15, 2022 at 4:24 pm

    Just found this and it is great. It even worked on Waterfall charts, where as dynamic range names failed, so many thanks.

  5. Michelle on September 2, 2022 at 12:59 pm

    The suggested solution above doesn’t seem to work when using a stacked bar chart. The data set is by a fixed number of years, which should appear on the X axis, the stacked bars include a dynamic number of data sets (e.g., in making a change to the data appearing on the chart, the bars may have as few as 2 offices, or as many as 5), each appearing as their own respective portion of the bar, with the legend changing accordingly. Any suggestions?

Leave a Comment