Skip to main content

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
Easily create a bar with bubbles chart with an amazing tool
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.

Easily create a bar with bubbles chart in Excel

The Bar with Bubbles Chart utility of Kutools for Excel can help you quickly create a bar with bubbles chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description

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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations