Why use a dynamic chart?
Charts and/or graphs can be very useful in visually displaying information and statistics to tell the users a story. A dynamic chart within your summary makes it more interactive by having several inputs that can drive what the chart shows. The dynamic chart can be tailored to automatically display relevant data to users excluding fields outside a specified time frame.
What is a dynamic chart?
A dynamic chart is a chart that automatically updates the series range, either by contracting or expanding the number of data, without the need to manually go into the chart and change the series range. For the purpose of this article, we will explore creating a dynamic chart that automatically scales the start point and the end point of the chart, based on a set of inputs, without manually adjusting the Minimum and Maximum axes figures.
Chart 1
Chart 2
How to plot a dynamic chart?
The dynamic named range method.
The direct or conventional method to getting a chart to do this is through the use of dynamic named ranges method. Dynamic named ranges involve creating a named range that contains an OFFSET/INDEX function with the width or height parameter being set as a variable. Here’s how to do it.
This method involves creating at least three (3) dynamic named ranges, or the number of series in a chart + 1 for the axis. Each of these dynamic named ranges will require an OFFSET function, which involves the starting reference point, column offset number, and column width number. For this, I’ve created another five (5) named ranges. These will represent the starting reference point for the dates, Portfolio 1 and Portfolio 2, one for the column offset number, and one more for the width. The three (3) named ranges are:
- RangeDate
- =OFFSET(RangeDateStart,0,RangeColOffset,1,RangeWidth)
- This will represent the source data for the axis
- RangeP1
- =OFFSET(RangeP1Start,0,RangeColOffset,1,RangeWidth)
- This will represent the source data for Portfolio 1 (P1)
- RangeP2
- =OFFSET(RangeP2Start,0,RangeColOffset,1,RangeWidth)
- This will represent the source data for the Portfolio 2 (P2)
These named ranges will then be used to plot the charts. Note the special way of typing the named ranges in. Typing in =RangeP2 within the source data formula will produce an error message.
This method of producing a dynamic chart works, but it involves a lot of preparation of dynamic named ranges and making sure that the OFFSET formula is entered in correctly into the Name Manager dialog box.
Even though this method works, it is quite tedious to set up and not very transparent. A better, more transparent method to plot a dynamic chart is to make good use of how Excel treats #N/A when it comes to plotting charts.
The #N/A method
Conclusion
Dynamic charts come in many forms and guises, but they have one common theme. To build a dynamic chart, we only need to manipulate the underlying data and let Excel’s in-built scaling algorithm to work out the most appropriate minimum and maximum values of each axes should be. Keeping that in mind, the #N/A method is the more transparent, user friendly, and easier to set up among the two examples above.
