In this guide, we're going to show you how to create dynamic charts in Excel using dynamic arrays.
Even though Excel doesn’t have built-in support for dynamic charts, there are multiple ways to do this. The traditional methods are using Excel Tables or array returning formulas like INDEX and OFFSET. Although the dynamic array approach in this article is another formula-based solution, it is an easier method.
Dynamic arrays and spilling
Dynamic arrays feature allows you to populate multiple cells. You can think them as array formulas, but you don't have to press Ctrl + Shift + Enter. If a formula returns an array, Excel will populate values in array in the adjacent cells, starting with the cell where you entered your formula. This is called spilling.
The idea is to create a dynamic chart using a formula that spills data for the chart.
Creating dynamic charts
Although generating dynamic ranges are easy to use thanks to dynamic arrays, you need to a workaround to use them with charts. You have to create a named range that either includes the dynamic array formula or a range with spill range operator.
Let’s see each option on an example.
We have a data set and an outcome table which displays Name, HP and Attack values based on the Type selection. Each column in the outcome table uses the FILTER function to list the filtered data.
The outcome table will be updated when the type is updated.
After this point, you need to create named ranges for each column of data. If you are OK with keeping the formula in your worksheet, create a named range which refers to the first cell of the dynamic array with a spill operator (#). In this example, we added the Name and HP columns using this approach.
The alternative is entering the formula into the named range like in the Attack column. With this approach, you do not need to keep the results in the worksheet.
The next step is to inject these named ranges into a chart.
- Start creating the chart by selecting your data.
- Right-click on the chart and click Select Data
- Select a Series item on the left box and click Edit
- In the Series Values box, delete the cell references but leave the worksheet name with exclamation mark (!).
- Enter the corresponding named range after the exclamation mark. Tip: Press the F3 button to see the list of named ranges.
- Do the same thing for each Series and Category (Axis Labels)
- Click the OK button to save your changes. Once finished you will have a dynamic chart.
