Creating a Bubble Chart of text columns

Question

I have two text columns which look like this:

and now I need to make a bubble chart that looks like this:

Any idea how I can achieve this using excel 2016?

Answer 1

There is a way to do this by using Javascript. This langage has a lot of powerful libraries for data visualization and data processing. And there is a way to use it in Excel by using an Excel Add-in called funfun.

I have written a working code for you:

https://www.funfun.io/1/#/edit/5a7c4d5db8b2864030f9de15

I used an online editor with an embedded spreadsheet to build this chart. I use a Json file(short/full underneath Settings) to get the data from the spreadsheet to my javascript code:

{
    "data": "=A1:B18"
}

I then store it in local variables in the script.js so I can use them correctly in the chart I will create:

var Approaches = []; // list of Approaches
var Contribution = []; // list of contribution
var tmpC = [];

/*
 * Parse your spreadsheet to count how much approaches and contribution there are
 */
for (var x = 1; x < $internal.data.length; x++) {
     if (Approaches.indexOf($internal.data[x][0]) <= -1)
       Approaches.push($internal.data[x][0]);
     if (tmpC.indexOf($internal.data[x][1]) <= -1)
       tmpC.push($internal.data[x][1]);
}

/*
 * sort the array so that other is at the end
 * (remove if you want you don't care about the order, replace 'tmpC' by 'Contribution' above)
 */

for (var t = tmpC.length - 1; t >= 0; t--) 
  Contribution.push(tmpC[t]);

var techniquesIndex = new Array(Contribution.length); // how much of one contribution is made per approach
var total = 0; // total of contribution

var totalPerApproaches = new Array(Approaches.length); //total of contributions for one Approach
for (var z = 0; z < totalPerApproaches.length; z++) {
    totalPerApproaches[z] = 0;
}


var data = []; // data for the chart

  /*
   * Parse your every approach
   */
for (var x = 0; x < Approaches.length; x++) {
    for (var z = 0; z < techniquesIndex.length; z++) {
      techniquesIndex[z] = 0;
    }

  /*
   * Parse your spreadsheet to count the number of contribution in this approach
   */
  for (var y = 0; y < $internal.data.length; y++) {
    if (Approaches.indexOf($internal.data[y][0]) == x) {
      total += 1;
      techniquesIndex[Contribution.indexOf($internal.data[y][1])] += 1;
    }
  }

  for (var c = 0; c < Contribution.length; c++) {
    /*
     * calculate the total of contribution on this approach
    */
    totalPerApproaches[x] += techniquesIndex[c];

    /*
     * removes the values equals to zero off the chart
     * (remove this condition if you want to show the zeros)
    */
    if (techniquesIndex[c] == 0)
      continue;

  /*
   * adds a bubble to the charts with the number of Contribution per Approach
   */
    data.push(
        {
            x: x, // -> index of array Approach[x]
            y: c, // -> index of array Contribution[c]
            z: techniquesIndex[c], // number of contribution[c] in Approach[x]
            name: techniquesIndex[c] // ..
        });
  }
}

The $Internal.data is the data from the spreadsheet accessible thanks to the Json file. The array data (at the end) will be used to create all the bubbles of the charts.

Once I have my data stored in the right format I create the chart in index.html using a data visualization library called Highcharts, it has lots of examples and good documentation for beginners. You can choose to add many options for your chart and at the end you pass your data to the chart as such:

series: [{
        data: data // use the data from script.js
    }]

Once you've build your chart you can open it in Excel by pasting the URL in the Funfun excel add-in. Here is how it looks like with my example:

You can as much lines as you want you just need to make sure that the range of data in the Json file is what you need.

you can then save the chart in many formats:

Hope this helps !

Disclosure : I’m a developer of funfun