ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)