Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Create bubble chart in Excel

A bubble chart is an extension of the scatter chart in Excel, it consists of three data sets, X-axis data series, Y-axis data series, and the bubble size data series to determine the size of the bubble marker as below screenshot shown. The bubble chart is used to show the relationship between different datasets for business, economic or other fields. This article, I will introduce how to create bubble chart in Excel.


Create simple bubble chart in Excel

To create a bubble chart, please do with the following steps:

1. Click a blank cell of your worksheet, and then click Insert > Insert Scatter (X,Y) or Bubble Chart, and then choose one type of bubble chart you like, see screenshot:

2. Then, a blank chart will be inserted into the worksheet, click to select the blank chart, and then click Select Data under the Design tab, see screenshot:

3. In the popped out Select Data Source dialog box, click Add button, see screenshot:

4. In the following Edit Series dialog box, specify the following operation:

  • In Series name text box, select the name cell you want;
  • In Series X values text box, select the column data you want to place in X axis;
  • In Series Y values text box, select the column data you want to place in Y axis;
  • In Series bubble size text box, select the column data you want to be shown as bubble size.

5. After selecting the data, click OK > OK to close the dialogs, and the bubble chart has been displayed as below screenshot shown:

6. As you can see, there are extra spaces before and after the chart, and the bubbles are crowed, so you can adjust the x axis, please right click the x axis, and then choose Format Axis from the context menu, see screenshot:

7. In the opened Format Axis pane, enter the suitable minimum value and maximum value based on your own data to get the chart is displayed as below screenshot shown:

8. Then, if you want the bubbles displayed with different colors, please right click the bubbles, and choose Format Data Series, in the opened Format Data Series pane, under the Fill & Line tab,check Vary colors by point option, and now, the bubbles are filled with different colors, see screenshot:

Tips: You can also format the bubbles with other colors you like one by one.

9. Next, please add data labels for the bubbles, click to select the bubbles, and then click Chart Elements > Data Labels > Center, see screenshot:

10. And then, right click the data labels, choose Format Data Labels option, see screenshot:

11. In the opened Format Data Labels pane, under the Label Options tab, check Value From Cells option, and in the popped out Data Label Range dialog, select the cells of data labels, see screenshots:

12. Then click OK, still in the Format Data Labels pane, uncheck Y Value check box, see screenshot:

13. Now, the bubbler chart is created completely. See screenshot:


Change bubble chart color based on categories dynamically in Excel

This section, I will talk about how to change the color of the bubbles based on the categories dynamically, which means the bubbles of the same category will fill the same color, and when changing the category name to another, the bubble color will be updated automatically as well as below demo shown.

To create this type of bubble chart, please do with the following step by step:

1. Insert a new blank row above the original data range, then type the category names into another range, and keep a blank cell between each category.

And then in the second row, type X, Y, Z ,Y, Z, Y, Z, Y, Z under the category names as below screenshot shown:

2. In the column of X header, please enter this formula: =B3 into cell F3, and then drag the fill handle down to the cells to fill this formula, this step will get the x data series of the chart, see screenshot:

Note: This x data series will be commonly used for each category.

3. In the column of first Y header, please enter the below formula into cell G3, and then drag the fill handle down to fill this formula, see screenshot:

=IF($A3=G$1,$C3,NA())
Note: In this formula, A3 is the cell contains the category in the original data, G1 is the helper cell of category, C3 is the cell contains the Y data series. This formula means if the category names equals the specific category in cell G1, the Y data series will be displayed, otherwise, some error values will be shown.

4. Go on enter the below formula into cell H3 under the Z header column, then drag the fill handle down to fill this formula, see screenshot:

=IF(ISNA(G3),NA(),$D3)
Note: In this formula, G3 is the cell which returns the Y data series by using the above step formula, D3 is the cell contains the Z data series. This formula means getting the Z data series based on the Y data series you have returned.

5. Then, select these two formula columns, and then drag the fill handle to right to apply these formulas to other cells, see screenshot:

6. After creating the helper columns, then click a blank cell, and then click Insert > Insert Scatter (X,Y) or Bubble Chart, and then choose one type of bubble chart you like to insert a blank chart, then right click the blank chart, and choose Select Data from the context menu, see screenshot:

7. Then, in the popped out Select Data Source dialog box, click Add button, see screenshot:

8. In the opened Edit Series dialog box, specify the series name, X value, Y value and bubble size from the helper data column, see screenshot:

9. And then, click OK to return the Select Data Source dialog box, now, you should repeat the above step 7 - step 8 to add the other data series into the chart as below screenshot shown:

10. After adding the data series, click OK button to close the dialog box, and now, the bubble chart with different colors by the category has been created as following screenshot shown:

11. At last, add the legend for the chart, please select the chart, and then click Chart Elements > Legend, and you will get the complete chart as below screenshot shown:


Create simple bubble chart in Excel with a powerful feature

Kutools for Excel provides 50+ special types of charts that Excel does not have, such as Progress Bar Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy tool- Bubble Chart, you can create a bubble or 3D bubble chart as quickly as possible in Excel.. Click to download Kutools for Excel for free trial!


Download Bubble Chart sample file


Video: Create bubble chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.