Motion Chart in Excel

- Contributed by Kamesh M

We all know that using a Bubble Graph one can show three different dimensions of data i.e, X-axis, Y-axis and the Size of the Bubble to represent an additional dimension of information for the given time period. With a small set of data this may not prove beneficial but is pretty much helpful to visually represent a large segment of data. It gives a quick impression of how the large data relate to each other in terms of for example volume, position, placement, etc among many other possibilities. All that saying, it however shows only for a certain defined time such as for a week or a month of data.

The same bubble chart can however be tweaked at large to show how the relation continues over a moving time range – say across the three months in a quarter or across twelve months in a year, which is better known as ‘Motion Chart’. And all that using the very common MS Excel itself. When the data has to be shown for different time periods using the same graph it could become a bit challenging. The idea is to show how the bubbles change with time or say any other dimension. In simple words, this can be done by using a scroll bar that would allow control the progression or the change.

Let’s go about creating such a motion chart. Here I have taken an example of ‘Top ten Sites Ranked by Unique Visitors. My data points – the Unique Visitors on Y-axis, Monthly attention on X-axis, and visits to the site as the size of the bubble. To go ahead with lets plot 1 months’ data in a bubble chart as below. All sites are identified by their abbreviations.

Ok. So here we are with the bubble chart. That’s the first step. Now to bring in some ‘motion’ to it for which, a scroll bar will be the handiest tool. The scroll bar will be used to slide through different time periods. So, insert one by clicking on the Developer tab, and in that ribbon, scroll bar form control will be available under Insert option in the ribbon.

Draw the scroll bar in the sheet below the bubble char. To change control settings, right click and select Format Control option from the context menu.

Now in the Format Control options, the Control tab has settings of most importance. Enter the Minimum Value as 1 and Maximum value for as long as the period there are data. Since I am trying to show 6 months of data, I have set Maximum Value to 6. Set both Incremental Change and Page Change to 1. Finally, Cell Link is to set to a cell in the sheet. The purpose of this sheet is to return the value of current scroll change to the sheet which in turn will allow control over the bubble chart.

Now, the complicated part, write a simple nested if formula in the data table such than when ever there is an increment/decrement in the Scroll Bar Current Value, the data table shows the required data. In my example, taking my data from January to June, values 1 through 6 would represent each month. The logic used here is
IF (cell$A$1=1, show value of Jan, IF ($A$1=2, show value of Feb…… IF ($A$1=6, show value of Jun))))))
Similarly for the rest of the dimensions as well, similar formula is written to update numbers based on scroll bar current position value.
After required beautifying process in the graph, the scrollbar, any accompanying texts and also the data table, if required to show along with the chart, the final output is a neat Motion Chart.

Reference: http://lists.compete.com/