Create a bar with bubbles chart in Excel

Sometimes, you may need to display two sets of data for different categories with chart in Excel. Combining bubbles and bars in a chart can help to solve the problem. This tutorial provides a step-by-step guide for you to create a chart that combines bars with bubbles in Excel.

Create a bar with bubbles chart in Excel
Download the sample file


Create a bar with bubbles chart in Excel

Supposing you have two sets of data for multiple series as the below screenshot shown, please do as follows to create a bar with bubbles chart to visually display the data.

1. Firstly, you need to create 4 helper columns as follows.

1.1) In the first helper column, enter the number 0 in each cell as the below screenshot shown.

1.2) In the second helper column, enter the serial number from 1 to 6 (the ending number depends on the total numbers of series in your original data range).

1.3) The third helper column data is used for the bubble size. In this column, please enter the number 10 in each cell. See screenshot:

1.4) In the fourth helper column, select the first blank cell (excluding the header cell), enter the below formula into it and press the Enter key. Then drag the formula cell all the way down to apply it to other cells.
=MAX($B$2:$B$7)*1.2

2. Select the original data range (A1:C7), click Insert > Insert Scatter (X, Y) or Bubble Chart > Bubble.

3. Right click the bubble chart and then click Select Data in the right-clicking menu.

4. In the Select Data Source dialog box, remove the existing series by selecting it (the A series in this case) and clicking the Remove button.

5. Click the Add button in the Select Data Source to add new series.

6. In the Edit Series dialog box, please configure as follows.

6.1) In the Series name box, enter a series name as you need;
6.2) In the Series X values box, select the first helper column range (D2:D7);
6.3) In the Series Y values box, select the second helper column range (E2:E7);
6.4) In the Series bubble size box, select the third helper column range (F2:F7);
6.5) Click the OK button. See screenshot:

The chart now is displayed as follows.

7. When it returns to the Select Data Source dialog box, click the Add button to add another series.

8. In the Edit Series dialog box, you need to configure as follows.

8.1) In the Series name box, enter a series name as you need;
8.2) In the Series X values box, select the first series data of the original data range (B2:B7);
8.3) In the Series Y values box, select the second helper column range (E2:E7);
8.4) In the Series bubble size box, select the third helper column range (F2:F7);
8.5) Click the OK button. See screenshot:

Now the chart is displayed as the below screenshot shown.

9. In the Select Data Source dialog box, click the Add button again to add the last series.

10. In the Edit Series dialog box, please do as follows.

10.1) In the Series name box, enter a series name as you need;
10.2) In the Series X values box, select the fourth helper column range (G2:G7);
10.3) In the Series Y values box, select the second helper column range (E2:E7);
10.4) In the Series bubble size box, select the second series data of the original data range (C2:C7);
10.5) Click the OK button. See screenshot:

11. Click OK when it returns to the Select Data Source dialog box to save the changes.

You can see the chart is displayed as the below screenshot shown.

12. Select the left bubbles (the blue bubbles in this case). Click the Chart Elements button, and then check the Error Bars box.

13. Right click the error bars, and then click Format Error Bars in the context menu.

14. In the Format Error Bars pane, please configure as follows.

14.1) Click the Error Bar Options arrow, and then select Series “Left” X Error Bars (the “Left” here is the name you specified for the first added series) from the menu;

14.2) Select the Plus option in the Direction section;
14.3) Select the No Cap option in the End Style section;
14.4) In the Error Amount section, choose the Custom option and then click the Specify Value button.

14.5) In the popping up Custom Error Bars dialog box, select the first series data of the original data range (B2:B7), and then click the OK button.

14.6) Click the Fill & Line icon, in the Line section, select the Solid line option, specify a color for it and then enlarge the line width.
After finishing the settings, the X error bars will be displayed as wide colored bars as the below screenshot shown.

14.7) Now you need to remove the Series Y Error Bars from the chart.
Click the Error Bars Options button , click the Error Bar Options arrow, select Series “Left” Y Error Bars in the menu to select all Y error bars in the chart, and then press the Delete key to remove them.

Now the chart is displayed as the below screenshot shown.

15. Select the left bubbles (the blue bubbles in this case), click the Fill & Line button to enable the Format Data Series pane, and then select the No fill option in the Fill section.

16. Now the selected bubbles are hidden. Keep them selecting, click the Chart Elements button and then check the Data Labels box. See screenshot:

17. Select the data labels you added just now, right click and select Format Data Labels from the context menu.

18. In the Format Data Labels pane, please configure as follows.

18.1) In the Label Options section, check the Value From Cells box. In the opening Data Label Range dialog box, select the series values in the original data range and click the OK button. Then uncheck both the Y value and the Show Leader Lines boxes.
18.2) In the Label Position section, select the Left option.

19. Select the middle bubbles (the orange bubbles in this case), click the Fill & Line button and then select No fill in the Fill section.

20. Keep the bubbles selected, click the Chart Elements and then check the Data Labels box.

21. Select the added data labels, in the Format Data Labels pane, only check the X Value box in the Label Contains section, and then select the Left option in the Label Position section. See screenshot:

22. Select the right bubbles (the gray bubbles in the chart), and then go to the Format Data Series pane to configure as follows.

22.1) Adjust the size of bubbles in the Scale bubble size to box. Here I enter 40 into the textbox;
22.2) Check the Show negative bubbles box;
22.3) Click the File & Line button;
22.4) Specify a new color for the bubbles. See screenshot:

23. Keep the bubbles selected, click the Chart Elements button, and then check the Data Labels box.

24. Select the data labels you added just now, go to the Format Data Labels pane, and then only check the Bubble Size box in the Label Contains section.

25. Select the x-axis in the chart, go to the Format Axis pane, and then specify the Minimum value as 0.

26. Select the y-axis in the chart. In the Format Axis pane, do as follows.

26.1) Select the Maximum axis value option;
26.2) Check the Values in reverse order box;
26.3) In the Labels section, select None in the label Position drop-down list.

Now the chart is displayed as the below screenshot shown.

27. Adjust the inner border to fully display the series values, and then remove all gridlines.

The bar with bubbles chart is complete as the below screenshot shown.


Download the sample file


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 ( Sign Up? )
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.