Bubble and Area chart

Excel 2016
Combination charts are a great opportunity to create a really creative and memorable data presentation. Unfortunately, you can't choose the Bubble chart as an alternative chart type for line, area, etc. charts in the Change Chart Type dialog box (see Using two axes in the one chart). This tip shows how to combine two different chart types into one chart.

For example, to merge a Bubble chart with an Area chart:

Bubble and area charts in Excel 365

An area chart or area graph is a variant of a standard line chart in which the area below the line of the chart is shaded or filled.

The area graph above represents the sales volume for a specified time period.

A bubble chart is a variant of the common scatter plot where the dots can represent third and even fourth dimensions - the bubbles have their own sizes and can be filled with different colors or textures (see more about bubble charts).

The bubble chart above represents two data series - the number of visits by men and women, where purchases were made, reflecting the sales volume in the area chart.

To create a chart like the one above, do the following:


Create an area chart

   1.   Select the data range for the area chart.

In this example, B3:C12:

Data for area chart in Excel 365

   2.   On the Insert tab, in the Charts group, choose the Insert Line or Area Chart button:

Line charts in Excel 365

From the Insert Line or Area Chart dropdown list, select the Area 3D Pie in Excel 365 chart and then modify this chart:

An area chart in Excel 365

   3.   To add vertical lines, on the Chart Design tab, in the Chart Layouts group, click the Add Chart Element dropdown list:

Add Chart Element button in Excel 365

In the Add Chart Element dropdown list, select the Lines list and then Drop Lines:

Add Drop Lines button in Excel 365

To customize drop lines: do the following:

   1)   Right-click on any of these lines and choose Format Drop Lines... in the popup menu:

Format Drop Lines in popup menu Excel 365

   2)   On the Format Drop Lines pane, on the Fill & Line tab, in the Line section, select the Solid line option and choose the color and width in the appropriate fields:

Format Drop Lines in Excel 365

Create a bubble chart

   1.   Select the data range for a bubble chart.

The data range for a bubble chart contains the following:

  • The first column - the data for the horizontal axis (Series X values),
  • The second column - the data for the vertical axis (Series Y values),
  • The third column - the data for the bubble size (Series bubble size).

See more about bubble charts.

In this example, B3:B12, D3:E12.

   2.   On the Insert tab, in the Charts group, choose the Insert Scatter (X, Y) or Bubble Chart button:

The Insert Scatter (X, Y) or Bubble Chart button in Excel 365

From the Insert Scatter (X, Y) or Bubble Chart dropdown list, choose Bubble or 3-D Bubble:

Bubble and 3D Bubble Charts in Excel 365

Excel creates a simple bubble chart. For this example:

Simple Bubble chart in Excel 365

If necessary, add another data series (see how to add a new data series to the bubble chart) and format the bubble chart:

Bubble chart in Excel 365

   3.   Hide axes.

Note: It is necessary to remove the gridlines, but we recommend doing that later after combining two charts. Gridlines are very helpful when you place one chart on another.

   4.   Right-click on the chart area and select Format Chart Area... in the popup menu:

Format Chart Area in popup menu Excel 365

   5.   On the Format Chart Area pane:

  • In the Fill section, select the No fill checkbox.
  • In the Border section, select the No line checkbox.
Format Chart Area in Excel 365

   6.   Right-click in the chart area and select Format Plot Area... in the popup menu.

   7.   The last and most challenging step is to place the second chart over the first one.

   8.   Remove gridlines on the bubble chart and make any other adjustments you prefer.

See also this tip in French: Comment créer le graphique à bulles et à aires.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.