CODEWITHSUNDEEP
excelcharts
Jennifer Zou
Jennifer Zou

Reputation: 197

How can I create a column chart with different Axis value in Excel

I want to create the following chart in Excel. The Y-value is different: 97%, 51h, 1292 tickets. How can I do this without making it look ugly? enter image description here

Upvotes: 1

Views: 66

Answers (1)

nicolas dejean
nicolas dejean

Reputation: 431

It is possible to do this in Excel by using Javascript, this langage is very useful for data visualization and data processing.

I have written a working code for you:

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

I used an online editor with an embedded spreadsheet to build this example, the data in the spreadsheet can be used in javascript thanks to a JSON file (underneath Settings) :

{
    "data": "=A1:C4"
}

For this example I needed to create false values to display the bars, so that there wouldn't be a huge difference of length between the different performance. So I calculated three different percentage like so.

    var falseArrayZendesk = [];
    var falseArrayIndustry = [];
    var falseValues =[];

    var totalPerPerformance = [100, 60, 1500] // custom average so that the bars all does the same length

    ...

    falseArrayZendesk.push((falseZendesk * 100) / totalPerPerformance[i - 1]);
    falseArrayIndustry.push((falseIndustry * 100) / totalPerPerformance[i - 1]);

I then added each Array to the chart and changed the labels so that the real values where displayed at the end of each bars as such:

  /*
   *  enter false values in series to display a correct chart
   */
    var series = [];
  for(var i = legend.length - 1; i >= 0; i--) {
    series.push({
        name: legend[i],
        type: 'column',
        color: Highcharts.getOptions().colors[i+7],
        data: falseValues[i]
    });
    }

      ...

  plotOptions: {
        series: {
            dataLabels: {
                enabled: true,
              formatter: function() {
              /*
               * Format the the number at the end of the bars (put the real value).
               * this.x is the false average value.
               * this.series.name is the legend of both column.
               */
                switch (this.x) {
                  case "Satisfaction Rating": {
                    if (this.series.name == "Zendesk")
                      return Zendesk[0] + '%';
                    if (this.series.name == "Industry Average")
                      return Industry[0] + '%';
                    return;
                  }
                  case "Average First Reply Time": {
                    if (this.series.name == "Zendesk")
                      return Zendesk[1] + " hrs";
                    if (this.series.name == "Industry Average")
                      return Industry[1] + " hrs";
                    return;
                  }
                  case "New Tickets": {
                    if (this.series.name == "Zendesk")
                      return Zendesk[2];
                    if (this.series.name == "Industry Average")
                      return Industry[2]; 
                  } 
                  default:
                    return "undefined";
                }
              }
            }
        }
    },

See the example for more precision like the features I used for this chart. If you find this ugly (as QHart said "ugly" is subjective) you can change as many options as you wish, there are a lot of features available see Highcharts' documentation.

Once you have built your chart You can upload it in Excel by pasting the URL in the Funfun Add-in. Here is how it looks like with my example:

final

Hope this helps !

Disclosure : I’m a developer of funfun

Upvotes: 3

Related Questions