Combine Bubble and xy scatter line chart

posted Jul 8, 2014, 8:39 AM by Krisztina Szabó   [ updated Aug 26, 2014, 2:35 AM ]

Freddy: Behind the bookcase. Hand me that robe, would you dear? You were right. It's coming from behind this wall. Where is it, where is it?
Inga: What?
Freddy: There's always a device. If I could just spot the triggering mechanism. Hello. It seems louder over here. Hand me that candle, will you? Put the candle back. Alright, I think i have it figured out now.

by The FrankensTeam

Have you ever wanted to connect the bubbles of a bubble chart with lines?
For example to create kind of a timeline: how the position of a product changes month by month in a Growth/Profit matrix.
You would like to see something like this:

Bubbles are based on this sort of data table:

Well, it’s a fact that bubble chart can not be combined with any other charts, so the usual “change series chart type” way will not work.

Our out-of-the-box idea is to use trendline.
We started to think about moving average trendline, because this is very simple to calculate based on the Period - as you can read on MS office site:
“For example, if Period is set to 2, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, etc.”
The idea is to create a dummy data series which determines a trendline which equals to the connecting line of the original data points. Only need to reverse the calculation logic of 2 period moving average: now the first data and the moving average (=the original data) is given, and need to calculate the second data.
In other words, you will need a help column, where the average of the first two data is equal to the January Proft (-2%), the average of the second and third data is equal to the February Profit (5%), etc.
The formula behind this dummy series column now goes without saying. Obviously we need one data point more than the original series. This additional data point could be a fixed value or you could use the first data of your original table.

Let’s see step by step.

1. Build up the help columns.
The very-first data (yellow cells) starts with a fixed 0.
  • Growth-help: from the second data is equal to the original growth data (this is the x values of the series)
  • Profit-help: from the second data has a simple formula: the original Profit multiplied by 2, minus the data above. So for example the dummy 8% for March is the average of the original March and February data: (11%+5%)/2 = 8%
  • Size-help contains fixed 0 values.

2. Add a dummy series to your bubble chart using data from the help column:
(right click on the chart, Select data / Add)

3. Add a trendline to the dummy series:
  • in Excel 2010: Select the dummy series under Chart tools / Layout, and add a moving average under Trendline
  • in Excel 2013: Select the dummy series under Chart tools / Format, and add a Trendline with moving average under Chart tools / Design / Add chart element / Trendline.

4. Select the trendline and format it according to your needs.

+1. Dynamic model
It is easy to change the model to update automatically when you add new data below the data table. You only need to copy down the dummy series formulas to more rows, add names and use the names in the chart series instead of fix sheet ranges.
In our file you will find the below names:

x =booble_linea!$C$4:INDEX(booble_linea!$C$4:$C$102,COUNTA(booble_linea!$C$4:$C$102))
y =OFFSET(x,,1)
size =OFFSET(x,,1)
x_dummy =OFFSET(booble_linea!$G$3,,,ROWS(x)+1)
y_dummy =OFFSET(x_dummy,,1)
size_dummy =OFFSET(x_dummy,,2)

You can download our example file for better understanding:
In the next post we shared the story of this discovery and a bug we discovered about trendlines. Please read it!

Please share your thoughts with us!

Leave a comment