Dynamic Charts

No comments

Formulas discussed: Charts, Offset, Ranges
The workbook used throughout this post is available here.

Charts can be a wonderful way of presenting data to an audience – just look at the popularity of infographics. Sometimes, particularly with line graphs, you might want to change the start or end point of the graph, either so you can zoom in on a subset of the data, or to remove some dodgy-looking bits. An example of this would be where you have a model that includes an upfront construction period, with certain things not being calculated until construction completion, and the completion time might not be fixed. In this circumstance, you don’t really want to display all those blank or zero cells (see workbook example 1).
screenshot - chart 1

Moving the chart data around

By using named ranges and the offset function, you can dynamically change the data that a chart uses. In example 2, we have calculated three new cells to determine when the chart starts and stops in cells I50:I52. In a real situation, these could be years, dates or just about anything else.
screenshot - range calculations
We then create two named ranges, “chart_years” and “chart_profits” but instead of them just referring to a static range of cells, they are an offset function. You also need to ensure that any cell references contained within the offset formula, such as H46 in this example, are fixed references (with the “$” in place), as Excel may change these otherwise. You can refer to named ranges within the formula, so that H46 could be “chart_years_start” or something similar.
screenshot - define range name
The final step is to create the chart. For this example we’re using a line graph. The easiest way is to select the data you want in the chart, and then use Insert -> Chart -> Select the Line graph. Right click on the created chart and choose “Select Data.” When the dialogue box first opens, it will have a static range for the Y values such as

=Tutorials!$I$47:$U$47

but we want to make this refer to our named ranges (“chart_profits” for the Y values and “chart_years” for the X values). Unfortunately, at least in Excel for Mac 2011, if you just enter

=chart_profits

as the Y values as you would normally do when referring to a named range, you will get an error message.
screenshot - error message
What you need to do is use the sheetname ! rangename notation as shown in the screenshot below.
screenshot - data source 1
Excel in its infinite wisdom will then automatically change this notation to filename ! rangename notation, but at least it works.
screenshot - data source 2
This can of course potentially cause problems if you change the filename. So if you do, go back into the range names list and check them – just in case.
But in the end, you can produce a chart that doesn’t have nonsense cells at the start or end of it.
screenshot - chart 2

Dashboards

Using dynamic charts for line graphs and time-series data is a pretty obvious application, but dynamic charts can also be useful for any dashboards you built.
You might want to give the user the ability to change which data appears in the graph. In example 3 the user can control how many products appear in the chart, and which data is shown. The only limit is your imagination.
screenshot - dashboard inputs

screenshot - dashboard outputs

Good luck.

Have any questions? Post them in the comments or email me at brendan@optimisedfm.com.au. We’ll also take suggestions for any future topics you’d like this blog to cover.

email
Brendan WalpoleDynamic Charts

Leave a Reply

Your email address will not be published. Required fields are marked *