ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to create and configure a bubble chart template in Excel 2007 and Excel 2010

Updated on October 20, 2013

Guide to creating a bubble chart template in Excel 2007 and Excel 2010

Hi, and welcome to my latest hub on Excel. Today, I will be looking at creating and configuring a beautiful bubble chart in Excel. They are a unique type of chart that allows you to visually represent three dimensions rather than the two dimensions (shown on the x and y axes) that other charts can illustrate.

  • In a bubble chart, the third dimension is represented by the size of the bubbles.
  • A bubble chart is similar to a scatter chart in its design but with the scatter points replaced by bubbles.
  • They are able to instantly convey a large amount of information using an attractive and yet deceptively simple format.

Once we have the chart fully completed and configured exactly how we want it, we will then save this as a template so that further bubble charts can be created from that template saving significant time and effort.

Example of a Bubble chart created using Excel 2007 or Excel 2010.
Example of a Bubble chart created using Excel 2007 or Excel 2010. | Source
A beautiful thermometer chart created using Excel 2007 or Excel 2010.
A beautiful thermometer chart created using Excel 2007 or Excel 2010. | Source

Alongside the bubble chart, I have hubs that cover other chart types:

The thermometer chart is based on a stacked chart and allows you to illustrate simply and effectively how close you are to hitting a target (often a fund raising target). My hub on the subject is a detailed step by step guide to creating a beautiful thermometer chart:

http://robbiecwilson.hubpages.com/hub/Creating-a-thermometer-graph-or-chart-template-in-Excel-2007


A Gantt chart is used to map a projects schedule. It allows you at a glance to show how each of the tasks within an overall project are progressing. Microsoft Project is often used to create Gantt charts, but the license is expensive and beyond the reach of many, so creating this type of chart in Excel is a popular alternative:

http://robbiecwilson.hubpages.com/hub/How-to-create-a-Gantt-chart-using-Excel-2007-and-Excel-2010


You can see an example of a Gantt chart below:

Example of a Gantt chart created using Excel 2007 or Excel 2010.
Example of a Gantt chart created using Excel 2007 or Excel 2010. | Source

Thematic maps allow you to show themes or variations across a geographical region. For example, they allow you to show how rainfall varies across the United States of America. Each state, county or province that makes up the over all map is represented by a shape. Visual Basic is then used to link the reference data to the shapes, so that each state displays the reference data for it. My hub on thematic charts can be found here:

http://robbiecwilson.hubpages.com/hub/How-to-create-a-thematic-or-Choropleth-map-in-Excel-2007-and-Excel-2010

Example of a Thematic chart created using Excel 2007 or Excel 2010.
Example of a Thematic chart created using Excel 2007 or Excel 2010. | Source

Creating a Bubble chart in Excel 2007 and Excel 2010

To begin, enter the data you want to chart into a table in Excel.

Note: The order of columns that the data is stored in is important, the first column stores the data for the x-axis, the second column the y-axis and the third column represents the bubble sizes.

Data to be used in the Bubble chart, correctly ordered in columns, using Excel 2007 or Excel 2010.
Data to be used in the Bubble chart, correctly ordered in columns, using Excel 2007 or Excel 2010. | Source

Select a cell where you want your chart to sit and click the Other Charts button in the Charts group on the Insert tab

  • Choose Bubble with a 3-D effect

Note: Normally with a chart in Excel, you select the data you want to chart and then insert a chart with the data selected. If you do this with a bubble chart, it will not look how we want it to look, so make sure you click away from the data table.

Note: You can see how a chart would look if you selected the cells before creating the chart below. Notice that there is only one series and all the bubbles are the same colour. They cannot be given different name labels, nor can they be each given a separate colour!

Initial Bubble chart created using Excel 2007 or Excel 2010.
Initial Bubble chart created using Excel 2007 or Excel 2010. | Source
  • Right click on the currently blank chart and pick Select Data

Configuring the data source for our Bubble chart in Excel 2007 or Excel 2010.
Configuring the data source for our Bubble chart in Excel 2007 or Excel 2010. | Source
  • Click Add

We are about to add the series that represents the first row. So, I point Excel to the cells in the table from row 6 (you can see the first series representing the United States below):

Configuring the series for the Bubble chart created in Excel 2007 or Excel 2010.
Configuring the series for the Bubble chart created in Excel 2007 or Excel 2010. | Source
  • Add a Series for each row of data that you have. Once you have completed this, you will end up with something similar to the figure below:

Our Bubble chart now with all the correctly configured series in Excel 2007 or Excel 2010.
Our Bubble chart now with all the correctly configured series in Excel 2007 or Excel 2010. | Source

Configuring a bubble chart in Excel 2007 and Excel 2010

Now that we have the basis, of our chart, we need to tidy it up and add labels and a title to it.

First, we get rid of the gridlines.

  • Select the Gridlines and press Delete

Next add Axis labels and a Title:

  • First navigate to the Layout tab and select Axis Titles and choose Primary Horizontal Axis Title then Title Below Axis
  • Then, choose Primary Vertical Axis Title and pick Horizontal Title
  • Name the axes appropriately
  • Again on the Layout tab, click Chart Title followed by Above Chart and add a title for your chart

Now we need to change our bubbles to 3-D bubbles from 2-D bubbles and add Data labels:

  • Select a bubble and Right click on it
  • Select Change Chart Type and click on Bubble with a 3-D effect in the Bubble section
  • Next Right click again and hit Add Data Labels. You will notice it added the bubble size which is not what we want
  • Right click the Data Label and select Format Data labels
  • Change Label Contains from Y-Value to Series Name

Note: If the label is obscured by another bubble, Right click the label and Select Format Data labels again and adjust the Label Position to one that doesn’t conflict with another bubble.

Repeat this process for all the bubbles

Our virtually complete Bubble chart created using Excel 2007 or Excel 2010.
Our virtually complete Bubble chart created using Excel 2007 or Excel 2010. | Source

The final step is to hide the negative values of both axes. We need there to be negative numbers, or bubbles that straddle zero on either axis (India straddles the x axis in our example) will be cut off at zero if we simply start the axes at zero which will not look good (as you can see in the picture below).

Bubble chart with the bubbles straddling the x-axis cut off in Excel 2007 or Excel 2010.
Bubble chart with the bubbles straddling the x-axis cut off in Excel 2007 or Excel 2010. | Source

To avoid potentially cropping our bubbles and also to hide the negative numbers we need to:

  • Select either axis
  • Right click and choose Format Axis
  • Navigate to the Number tab
  • Choose Custom under Category
  • Enter ###; into the Format Code box and click Add

A completed Bubble chart created using Excel 2007 or Excel 2010.
A completed Bubble chart created using Excel 2007 or Excel 2010. | Source

Saving a chart as a chart template in Excel 2007 and Excel 2010

Now that we have our chart configured exactly how we want it, we will save it as a template so that future bubble charts can be created from it that will look exactly as our current one does. This will save you from having to repeat a large amount of repetitive manual work. To do this:

  • Click on the chart
  • Choose the Design tab and in the Type Group, select Save As Template
  • Give it a memorable name and click Save

To open our newly created bubble chart template:

  • Choose the Other Charts button in the Charts group on the Insert tab
  • Select All Chart Types
  • Pick Templates on the left and your new template on the right

You will notice immediately, that rather than a blank chart; the chart already has a title, the axes labels and series added:

Example of a Bubble chart created from the template using Excel 2007 or Excel 2010.
Example of a Bubble chart created from the template using Excel 2007 or Excel 2010. | Source

If we now go to add data to the chart, you will see that Excel has already added some series, we need to simply add data to those series:

To use the Bubble chart template, simply add data to each of the already configured series in Excel 2007 or Excel 2010.
To use the Bubble chart template, simply add data to each of the already configured series in Excel 2007 or Excel 2010. | Source

We will add one series to this new chart to illustrate just how useful a template can be:

Note: Notice that the values are now all blank rather than containing ={1} as they did when we created the chart that became our template!

Bubble chart template configured with one series of data in Excel 2007 or Excel 2010.
Bubble chart template configured with one series of data in Excel 2007 or Excel 2010. | Source

You can see that the data labels are automatically configured how we want them and the bubbles are correctly displayed in 3D. The axes are set up with the negative numbers hidden as well.

Conclusion

Bubble charts allow you to display the normal x and y axes that other charts such as a scatter chart as well as allowing you to display a third dimension represented by the size of the bubbles. In my example, I illustrated GDP, Average Monthly Sales and the Population for 10 countries.

In today’s hub:

  • We created and configured a visually stunning 3-D bubble chart
  • This chart was then saved as a template to allow us to create new bubble charts easily and quickly from the template
  • Along the way, we also learned how to hide negative values in a chart axis

I hope that you have enjoyed reading this hub as much as I have enjoyed writing it. Please feel free to leave any comments you may have below.

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article