Posts Tagged ‘dynamic ranges’

Dynamic Bubble Chart in Excel

October 20th, 2009

Dynamic Bubble Chart
One of handy features of Excel is it’s ability to create dynamic ranges which can change their size and position depending on criteria we define. Today I would like to show you how, using this feature, you can add some life to a bubble chart.

The general idea behind bubble charts is, that for each bubble you need to define it’s position (X and Y coordinates) and size. Simple! But, since a picture is worth a thousand words, below you can find a very simple data table and a bubble chart prepared using this data. I hope it explains any questions you might have.

BubbleChart and it's source data table

The first step to add some life to our bubble chart is to prepare a table with source data. In my case, the data table looks like that:

Dynamic Bubble Chart: Step 1 - data table

All data is prepared for years 1999-2005 and for 6 countries. As you might notice (after a quick table analysis) what we have prepared for each case is (mentioned earlier)  X, Y coordinates and size.

I have also decided that my “tail” with historical data will follow the chosen year data for a span of 4 years. Consequently, you will see a maximum of 5 bubbles for each listed country. You can change this number if you like to suit your needs.

Dynamic Bubble Chart - historical data

Through the years we will move using a scrollbar inserted using the Form controls toolbar. In Excel 2007, you can find the Scroll Bar control under the Insert dropdown on the Developer tab.

Form controls - scrollbar

The scrollbar is linked to one of the worksheet cells.  I named this cell “PRZESUNIECIE” (sorry for foreign and probably meaningless names but the text was originally prepared in polish. And of course it’s not the names that matter, what matters is how we use them.)

Scrollbar linked to a worksheet cell

Because I have decided that the maximum number of bubbles is going to be 5, I am going to define 5 data series for my bubble chart.

Dynamic Bubble Chart - Data Source

The main series („Series1” on the picture above) which refers to the “active”, chosen year is defined as follows.

Dynamic Bubble Chart - Edit SeriesToShow_X, ToShow_Y and ToShow_Bubble are properly defined dynamic ranges, so that every time we choose the year, the data used for the chart will automatically slide through the data table.  Always pointing to the position and size of the bubble which should be displayed first.

Dynamic Bubble Chart - sliding through the data table

Dynamic ranges are defined as follows:

=OFFSET (Sheet1!$D$7;Przesuniecie*3;0;1;7)



As I have mentioned earlier „Przesuniecie” is a defined name referring to the cell (A1) linked to the scrollbar.

The cell $D$7 (used in the above formulas) is the first cell of my data table. Please have a look at the picture below for clarity.

Dynamic Bubble Chart

The remaining series (2-5) for the historical data (tail) are defined in a very similar way.

Dynamic Bubble Chart

All dynamic ranges we refer to from the “Edit Series” window have the same structure. Let’s have a look at ranges used by “Series2” (the first bubble of our “tail”)

Using the range $D$2:$I$2, we are trying to cheat Excel a little and persuade it to hide bubbles we don’t want (yet) to see on a screen. Picture below should unveil the mystery of our $D$2:$I$2 range :)

Dynamic Bubble Chart



For all other ranges the formula structure is the same, with the only difference shown on the picture below.

Dynamic Bubble Chart

And that’s pretty much it. With all dynamic ranges defined and linked to the graph series what we need to do is to format the graph a little, choose nice bubble colors for each series and …. Enjoy effect of our work.