How to Create a Bubble Chart in Google Sheets

Published on

The bubble chart is a data visualization tool in Google Sheets that displays relationships between variables: x-coordinate, y-coordinate, and bubble size.

Each point represents a data entry with values for these three variables.

The bubble chart is similar to a scatter chart in that both display spreadsheet data as a series of points on a graph. The key difference is in how bubble sizes are controlled.

If you format your data properly, creating a bubble chart in Google Sheets is straightforward.

In the following example, each bubble’s position on the chart can visually show how Quantity (x-axis) correlates with Sales Volume (y-axis). Larger bubbles indicate higher sales volumes.

An example of a bubble chart with ID, X-Axis, Y-Axis, Category, and Size

Formatting Data for Bubble Charts in Google Sheets

A bubble chart requires data in five columns, three essential and two optional. The essential data columns are X-axis, Y-axis, and Size. The optional columns are ID (bubble labels) and Series (for grouping data points to add similar colors).

Even if you have only the essential data, include the optional columns and leave them blank. I’ll explain later what to do with the blank columns.

Sample Data:

Product NameQuantity in MTSales VolumeCategoryPercentage Share
White Sand5001875Sand11.63%
Red Sand7002625Sand16.28%
Gravel 10 mm10003750Aggregates23.26%
Marble Chips15005625Aggregates34.88%
Gravel 20 mm6002250Aggregates13.95%

This is my sample data, and I’ll explain what each column does:

  • Product Name: ID (Optional)
  • Quantity in MT: X-axis
  • Sales Volume: Y-axis
  • Category: Series that determines the color (Optional)
  • Percentage Share: Size (use a formula to find the value to get the bubble size of each data point proportionate to the whole)

Bubble Size Variable and Formula

You can copy and paste the above data into cells A1:E6 in a blank sheet. Under the field label “Percentage Share”, i.e., in cell E2, enter the following formula and drag it down as far as needed:

=TO_PERCENT(C2/SUM($C$2:$C$6))
Bubble Size Variable and Formula

These values will determine the sizes of the bubbles in the chart.

Formatting the Optional Columns ID and Category

If you don’t have the IDs (the data in the first column that adds labels to bubbles), remove the values in column A, i.e., A2:A6. Leave a space character in cell A2 by tapping the spacebar.

If you don’t have the categories (the ‘Series’), remove the data in the fourth column, i.e., D2:D6, and leave a space character in cell D2.

Formatting the Optional Columns ID and Category

I hope I have clearly explained how to format data for a bubble chart in Google Sheets. Now let’s proceed to creating the chart.

Steps to Create a Bubble Chart in Google Sheets

Once you have the five-column data formatted as above, you can easily create a bubble chart by following these steps:

  1. Select the data range A1:E6.
  2. Click Insert > Chart.
  3. Within the Chart Editor sidebar panel, under the Setup tab, select Bubble Chart under Chart Type.
    Bubble chart basic settings in Google Sheets
  4. Navigate to the Customize tab within the Chart Editor panel.
    • Under Horizontal Axis, enter 0 in the Min field.
    • Under Vertical Axis, enter 7000 (a value higher than the max value in the third column of the sample data) in the Max field.
    • Check other customization options available.

That’s all. You have created your first bubble chart in Google Sheets.

Sample Sheet

Resources

  1. Creating Dot Plots in Google Sheets (Learn Step by Step)
  2. Conditional Coloring Data Points in the Scatter Plot in Google Sheets
  3. Google Sheets – Add Labels to Data Points in the Scatter Chart
  4. Common Errors That You May Face in Scatter Charts in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.