Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Create a bar with bubbles chart in Excel

Author Siluvia Last modified

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.

a bar with bubbles chart 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.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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).

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

The chart now is displayed as follows.

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

Now the chart is displayed as the below screenshot shown.

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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;

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

14.7) Now you need to remove the Series Y Error Bars from the chart.
Click the Error Bars Options button steps of creating a bar with bubbles chart in Excel , 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.

steps of creating a bar with bubbles chart in Excel

Now the chart is displayed as the below screenshot shown.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

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:

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

Now the chart is displayed as the below screenshot shown.

steps of creating a bar with bubbles chart in Excel

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.

steps of creating a bar with bubbles chart in Excel

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

sample of creating a bar with bubbles chart in Excel

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
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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

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.