Bubble Timeline Chart

A LinkedIn colleague posted an update in a project management group about the following infographic.  It was extremely well received, and inspired me to look into the effort required to replicate it using the tools at hand, namely MS Office 2010.

WebSiteDesigned

This infographic was developed by John Furness of Simple Square; 5663 Cornwallis St.; Halifax NS B3K 1B6; ph. +1 902 452 3417; john@simplesquare.com.

I find this infographic inspirational.  It is easy to read and intuitive, and visually stunning.  It conveys a lot of data, very efficiently to me.  I can tell by the size of the circles and fonts the level of involvement and importance of each task (bubble) on the timeline.  The location of the bubble’s perimeters on the timeline indicate start and finish dates, overlap and size of bubbles is an indication of resources needed, etc.  I didn’t feel like I needed the legend, but it is nice to have.  In my opinion, this is a great first presentation of a project and an excellent sales tool.

I started building this with the data required to make a “normal” Gantt Chart:  Task, Start Date, Work Completed, Finish Date, Number of Days, and Current Date.  (my definition of a normal Gantt Chart)

The results were unreadable, even after working on the formatting… a lot!

firsttry

I obviously had too much data, so I pared it down to Task, Start Date, and Number of Days.

Secondtry

This was better, but the bubbles are lined up on the Y axis… So, I watched a few YouTube videos on Bubble Charts.  It was faster for me to see what was possible, than just exploring them by myself.

After a lot of watching and experimenting, I was able to write a macro to create this:

firstsuccess

The trick is that EACH task in the spreadsheet is a data series in the Chart.  This gives you access to the individually configurable components, color, transparency, label position, font size, etc.

Secondsuccess

At this point, I can either work in Excel’s chart object or copy the chart into PowerPoint and simply add the legend and other labels as desired.

The Finished Product:

Bubbles1

(The parts of the legend that include bubbles were created in Excel.)

 

I have used this to introduce several project in the past couple of weeks.  It has been very well received by my clients.

For a workbook example, in Excel 2010, including automation macros, download it from

https://drive.google.com/file/d/0B7-KTTJhKkb4a1JZVHpIWkV4RXM/edit?usp=sharing

For the PowerPoint example, download it from

https://drive.google.com/file/d/0B7-KTTJhKkb4UjFvZElWYmlmcW8/edit?usp=sharing

For a PDF version of the bubble chart used in an actual presentation [redacted], click here:

Bubble Timeline Chart Presentation
Bubble Timeline Chart Presentation
Redacted 7 Phases.pdf
678.4 KiB
2038 Downloads
Details

14 thoughts on “Bubble Timeline Chart

  1. Hey Joe, this is very neat. I’ve downloaded the xlsm to have a try but can’t get the macro to run without error. I’m getting a run-time error ‘1004’ message.
    “Method ‘Location’ of object ‘_Chart’ failed on the BubbleGantt macro.
    (this is when running the macro from the TaskList worksheet)

    I’m using excel 2010, is there anything I’m missing/doing wrong?

  2. Hi Simon, Sorry about the lack of instruction. You will need to delete or rename the existing Bubble chart page first.

  3. Hi Joe,
    First of all, I think this is a very interesting way of presenting timelines. In the opening portion of your post though, you mentioned that the original infographic that inspired this had the perimeter of the bubbles aligning with the start and finish dates of the tasks. In your implementation the bubbles are centered around the task start date. When one looks at the completed chart, one kind of naturally assumes that the diameter of the bubble represents the start and finish dates but it is somewhat misleading. Any thoughts on trying to get the bubble sizes adjusted to replicate that original concept? I tried setting the “size” of the bubbles to the task duration in days and setting the date to one in between the start and finish dates but things really don’t line up. Part of the problem appears to be Excel’s propensity for “auto sizing” the bubbles so I have been unsuccessful so far in doing this.

    Oh, by the way I added some code to the macro to warn the user that the BUBBLE chart already exists and ask if it should be deleted, if they say NO, then the chart is left on the same sheet as the data.

  4. Hi Kevin,
    I agree, Excel’s auto-sizing is quite frustrating. I have tried many times in Excel to gain some level of control over that to no avail. I have started deleting the X axis and replacing it with a long narrow label that I have much better control over.

    I will be reprogramming this in PowerPoint because PPT has a true pixelated pallet to work in, and I can mathematically align the bubbles based on start and finish dates. Of course, now that I think about it, I may be able to about the same in a blank chart object.

    Great idea to notify the user of the BUBBLE chart!! Thanks!!

  5. Hi Joe,
    OK. I’m going to try a few more things here myself staying in Excel. Let me know if you need any help.

  6. Hi Kevin,
    I figured out by drawing circles on the Chart Sheet. The Chart Sheet pallet is 650 wide by 460 deep. The left edge of the circle is the Start Date, and right edge of the circle is the End Date, for each task. I need to work on the “X” axis label. Then I will upload a new version for collaboration. My email is joe.smith@mentalrobotics.com

  7. Thanks for the extra info Joe. I’ve got it working now 🙂
    Does the transparency macro need to be run? It seems ok without and fails when running.

  8. Thanks to very valuable feedback from Kevin and Simon, I have developed a new version to download:
    https://drive.google.com/file/d/0B7-KTTJhKkb4YWw5NFg3dFRtN1U/edit?usp=sharing

    In this one, the bubbles are not exaggerated by Excel. In a normal bubble chart, the start dates are the centers of the bubbles.

    In my new version, the start date is the left edge of the bubble, and the end date is the right edge of the bubble. This gives us a lot more control of the overlap. This one also creates a new chart every time, regardless of how many are already in the workbook.

    The original one, with the “normal” bubble chart is great for very high overview, because the bubbles tend to be exaggerated. The second one is more exact, for a more precise overview. The choice depends on what the audience prefers. :o)

Comments are closed.