Scatter Plots and Bubble Diagrams

Creating bubble charts in excel is the subject of this page.  A simple process of creating the charts by eliminating the name of the x-axis is demonstrated.  Then, techniques to put in the names of the bubbles into the graph and change the colours of the bubbles are explained.  To put in the bubble names and change the colours, you will need to add a bit of VBA code.

Quickly Making Scatter Charts and Bubble Charts that are a Cousin of Scatter Charts

The file below demonstrates how to create flexible bubble charts from scatter plots. It also shows you how to use macros to make labels on the bubbles and to make various groups of bubbles have alternative colours.

To make a bubble chart very quickly you can set up your data without an x-axis title and then select the area and press the F11 sheet. Then you select the scatter plot and the bubble chart option. The file below demonstrates how to set up data with an extra row or column for the bubble size and then use F11 key to create scatter plot and then make it into a bubble chart. You can do this in minutes if you eliminate the x-axis title before you make the selection.

Putting labels on bubbles and making the bubbles have different colours is more difficult. The file below is associated with three videos that show you how to make macros to automatically add labels to each bubble and how to create colour codes to make each bubble a separate colour. The videos are listed in the table at the bottom of this page that are named bubble charts.

Excel File that Demonstrates How to Use VBA Code to Easily Create Titles for a Bubble Chart

You can make effective scatter plots in Power BI. You can also add labels easily in Power BI. It is not obvious how to add trend lines to the scatter plots in power BI.



Example bubble chart.xlsm

Comprehensive Graphing Examples with Associated Videos

The two files below include a comprehensive set of elements that can be used to make flexible graphs. The first file shows how to make a flexible graph including varying time, flexible dates, selection of alternative items, multiple series options, alternative styles etc. It can be used together with the set of videos listed in the table at the bottom of the page that walk through different flexible graphing techniques in a step-by-step manner. Choosing a single series from a lot of data is easy — it can be accomplished with the INDEX function. Making the x-scale flexible and including alternative numbers of series on charts is more difficult and is demonstrated in the file. Making the graphs even more flexible through allowing a different number of series to be presented on the graph with a list box is even more difficult.

The second file listed below illustrates how to include confidence intervals in graphs and how to make flexible bubble charts. This second file is also associated with a video in the table at the bottom of the page. In this file macros are included to set labels and colours for the bubbles. I think it may be a good idea just to open the file and get some ideas about how to make really dynamic graphs.

You can do a whole lot of this in Power BI — especially the flexible bubble charge if you use a slicer. I have included confidence interval graphs in this file which can be very effective. I copied these from my friend and I do not know how to do this in Power BI.

  File with Bubble Charts and Charts with Confidence Intervals that Demonstrate Flexible Alternatives

Examples of Flexible Charts in Emission Reduction Analysis

Other examples of using bubble charts are shown in the two files below. These files were created when I was working on alternatives to reduce the methane and carbon dioxide that is generated from oil wells in the Gulf of Mexico.  Needless to say, these files were created before Trump.  But anyway, you can look at a lot of database techniques and graphing techniques in the files.

Oil and Gas Production and Flaring and Venting for Flexible Graphs.xlsm

Flexible Charts with Confidence Intervals and Bubbles, Listboxes.xlsm