How to Use Excel Bubble Charts
Excel bubble charts are my favorite way to present data in Excel, yet I rarely see them in use in reports or dashboards. A typical scenario to choose a bubble chart is in a set of data with three values for a category. With bubble charts you have the ability to add another dimension of data in the size of the bubble. Another thing to have in mind when choosing a bubble chart is that these graphs use only values, both axes represent a value, unlike other charts which use the category for one of the axes. Therefore in a bubble chart the category can be added as a data label.
Visualize Website Conversions with an Excel Bubble Chart
In this video I am using an eCommerce example to show you how you can represent your conversion data in an Excel bubble chart. In this example scenario I have used random numbers for several eCommerce categories on a beauty website that sells makeup and skincare. The table shows the number of sessions, the number of sales and actual revenue for each category of products. *Note, to have this type of data you’d need Google Analytics content grouping set up, it will make it possible to break all these metrics by all of the categories on the website. Here are some instructions on how to set up content groupings.* The bubble chart will present the number of sales on the vertical axis, the number of sessions on the horizontal axis, the size of the bubble is the actual revenue and the color represents the category. I have also used data labels to mark the revenue and the name of the category.
Steps to Create an Excel Bubble Chart
- Start by selecting the three right columns (since bubble charts do not use categories for the axes, you only need to select the values in your table)
- Then insert a bubble chart
- Change the chart name to reflect the data you are reporting
- To make the graph look cleaner delete the gridlines
- Then edit the axes to start from 0
- Then add data labels to include the actual revenue number and the category name
- To add data label for the revenue data choose data label to be the size of the bubble
- Then to add the category name select value from cells and select the column with the category data
- Aligh the data labels above the bubble
- Click on bubble and select “Vary by color” from the “Fill” options to add a different color for each category
- Then add axes titles and align horizontally
- Rename the axes to reflect the name of the metric (sales and sessions)
- Finally adjust chart position to fit it within your dashboard
How to Analyze the Data
Ideally you’d want all bubbles to be as big as possible and at the most far top right corner of your chart. When looking at the data find opportunities in categories where you receive a lot of traffic, high number of sessions, but not that many conversions. Or there might be a category that is reporting good revenue and low number of sessions. This section of your website might have a big potential to increase traffic and further increase your revenue.
Have fun creating your excel bubble chart. You might find it useful for other sets of data you are reporting on.