Dynamic Excel Charts / by Robert Walker

This comes up for me from time to time - A client wants a set of charts on the same template, but showing data for a different location or date-range in Excel (using Python or JavaScript, this is trivial, but Excel has some challenges for client-use).  The brute-force approach for this is to copy and paste the chart template and then select different data ranges.  Here I suggest a better approach using tables and slicers.

The Data

All charts start with some data, so you need to make sure you've organized the data in a way that makes it useful for charting and slicing.  For my example, We'll use a simple time-series data set with two locations ("A" and "B") with some dates and values for each.  I've also converted the range to a table (Ctl+T):

Charts1.JPG

The Chart

Now we can insert a chart of our choice.  I'll use a scatter plot with a line.  I'll choose columns B and C (Datasource: =Sheet1!$B$1:$C$15) to represent the data and insert a slicer on column A (Click a cell in the table > Table Tools > Design > Insert Slicer).  I also make sure that the properties of the chart are set to "Don't resize or move with cells", otherwise the chart will change size and shape as the data are sliced:

Charts2.JPG

For now, the data look jumbled because both locations are selected.  If only "A" is select or "B" is selected though - the magic happens!

Charts3A.JPG
Charts3B.JPG

You could also adjust the data using a time-line (date slicer) or other slicers on additional parameters (columns). 

Customization/Templating

You can now format/decorate your Excel Charts and setup a print area.  Then just change your slicer and print each chart!  This is very useful when combined with linked data from a database or other source that may change frequently.  Furthermore, the slicer selection can be automated with VBA, allowing for automatic selection and print if you have too many charts to manually produce.

Download my example.