Interactive Chart Example – 2

This chart is a very cool looking chart with very nice spinning effect. There is no macro used for this interactive chart, only formulas and form controls. And some creativity…

For this example, we are going to make a doughnut chart and add some interactivity using a spin button. Idea is, when we click on one of the spin buttons chart show data for previous or next month, depending on the arrow direction you clicked. Finished product will look like the picture above.

For this interactive chart we will use a table representing monthly sales values for our top 10 customers on monthly basis as base data. Here is how our data looks:

For doughnut chart we need a simple series of data that includes one value per customer for a month. I’ve made a series for this purpose on rows 16 and 17. Here is the setup:

Cell A17 – Reserved for form control (set it to 1 manually for now)

Cell B17 – Dynamic month name (formula: =CHOOSE($A$17;C3;D3;E3;F3;G3;H3;I3;J3;K3;L3;M3;N3) )

Now lets generate our (to be dynamic) data series for doughnut chart. Similar to month name in cell B 17, we are going to use a choose function here to dynamically determine sales values by customer depending on selected month. See the picture below for how it is done.

Now it is time to create our doughnut chart. Select cells between C16 and L17 and from ribbon, insert a doughnut chart.  You will get a generic doughnut chart. It will look like this:

Do some formatting at this stage (I have used style 26 from chart styles, removed legend from right side, put a title and set hole size to %75). And resize your chart to make it bigger. Cut your chart and paste it to another sheet (name it chart). Here comes the interactivity part…

1. Select a spin button from form controls. Place spin button anywhere you like (literally). In this example we put it inside the chart area at lower right corner.
2. Set it up. (Target cell Cell A17 in data sheet, current value=1, min value=1, max value=12, incremental change=1)
3. When finished, play with it a little while and observe the spin effect 🙂

Now that we have our interactive chart, it is time for some cream on top of it.

• We are going to add a dynamic chart title
• An indicator for selected month (put a text box near the spin button and address it to Cell A17 in data sheet).

Data table for selected month to be shown inside the doughnut.  For this, copy chart data in data sheet and paste it below as “transpose”. Don’t worry list will keep its formulas correctly.

Now make some formatting. Put small color boxes beside customer names that matches the colors on the chart (this way we won’t have to use labels). When all is ready, copy your table and paste it as picture link to chart sheet. Place it inside the doughnut and you are done.

If you like this tutorial, you can download our example workbook  for this interactive chart tutorial and check the stuff yourself.