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).
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.
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.
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
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
as the Y values as you would normally do when referring to a named range, you will get an error message.
What you need to do is use the sheetname ! rangename notation as shown in the screenshot below.
Excel in its infinite wisdom will then automatically change this notation to filename ! rangename notation, but at least it works.
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.
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.
Have any questions? Post them in the comments or email me at firstname.lastname@example.org. We’ll also take suggestions for any future topics you’d like this blog to cover.