MS Excel - Graph a Spill Range
Introduction to Dynamic Array Formulas and Spill Ranges
Microsoft implemented a major improvement in Excel with the dynamic array formula and the associated spill range. Now, when someone enters a formula such as =SEQUENCE(10), Excel figures out how many cells it needs to show the result of the dynamic array formula - in this case, 10 cells starting with the cell containing the formula going down the column. Microsoft calls this a spill range. In the image below the dynamic array formula is in cell B5 and the associated spill range is B5:B14.
To refer to the spill range in another formula, use the # sign at the end of the reference to the first cell in the range. [In fact, Excel will do the needful if you enter =SUM( and then use the mouse to select the range B5:B14.]
The formula =SUM(B5#) will sum all the values in the spill range starting from B5 as in the image below
The reference to the spill range means that Excel will adjust the range in the formula if the dynamic array formula returns a different range. So, if we change the formula in B5 to =SEQUENCE(20), the above formula would return 210
Graphing a Spill Range - Attempt 1
Given how simple it is to refer to a spill range in a formula, one might expect the same simplicity in a chart. Instead of =SERIES(,,Sheet1!$B$5:$B$14,1), the formula would be =SERIES(,,Sheet1!$B$5#,1). Correct? Nope, life is not that simple.
Nothing obvious and straightforward that I've tried works.
Graphing a Spill Range - Making it work
I found this while searching the 'Net. Maybe, it was a Microsoft website or maybe it was one of the many other websites that share information about Excel.
In the example below, start with 2 spill ranges in a worksheet named Sheet1. One range is for the X values and the other for the Y values as in the image below. The 2 formulas are in cells B5 and C5 respectively.
Next, create 2 names: Formulas tab | Defined Names group | Name Manager button. In the pop-up dialog box, use the New... button to create two names as below.
领英推荐
Now, create a chart and use these names
To ensure all this works as expected, change the formula in B5 to =SEQUENCE(20). The result will reflect the 20 elements in each of the 2 spilled ranges. Excel adjusts the chart on its own - zero work on our part.
Summary
Microsoft has done a good job implementing dynamic array formulas and spill ranges. Unfortunately, there are a few limitations and one of them is the roundabout method to graph a spill range, as we saw in this article.
My previous article: List and count unique values (MS Excel, Python, and SQL)