Posts Tagged ‘Custom Chart’


It’s no secret that I’m a big fan of beautiful data visualizations. What may not be so obvious is that I’m huge on the KISS (keep it simple stupid) principle when it comes to getting work done. I believe that any moron can complicate things, but it takes a genius to simplify them. (On a separate note, I often recall a quote on life from one of my all-time favorite movies – “It is very simple to be happy, but it is very difficult to be simple”)

Today I’m sharing a simple tip on how you can highlight a data-point on your chart, based on a selection, without using VBA. And the best part is that it can be used for any chart type.

Chart - Highlight Data PointI have data for average precipitation in Delhi by month (for years 2000-2012). I have placed my data in cells A4:B15. Data validation has been put in cell B2 such that it can only take month names from January-December. Now that we are set up, follow these steps: (more…)

Advertisements

This is a common requirement while creating dashboards. One selects an option and the charts update to show data for that particular selection. It would be very useful if the same chart could show you how that particular selection fares against average or a set benchmark.

Comparison Chart Against AverageLet’s take an example. I have data for a class of students with their subject marks. I make a selection of the student name, his marks for each subject are plotted on a chart. I wish to see how this particular student has performed in each subject as against the class average. This custom chart does just the same, and it can be very very easily created in Excel as below:

(more…)


Although this is not my personal favorite chart, I have seen that many people find it fabulous since it looks good on a dashboard. I think that it takes too much space for the small information that it conveys. But then, that’s just me.

Gauge Chart

Gauge Chart

(more…)


As you can see from the picture, this chart can be used when you want to compare two entities on same parameters. Here, I’m comparing sales at two stores of some items.

Butterfly Chart

Butterfly Chart

To create the chart, follow these steps: (more…)


A Waterfall Chart is a great way to present data when looking at the incremental contribution of individual elements in reaching from one milestone to the other. Let’s take an example below. The left most column shows the total sales for last year, the right most shows the total sales for current year and the segments in between show the incremental contribution of each region in current year over last year. The reds show a negative contribution and the greens show a positive one.

Waterfall Chart

Now let’s learn how to make this chart. (more…)


Tolerance - RAG ChartEarlier we had seen the Thermometer Chart that lets one see where a value is lying with reference to a scale. That works when you are measuring everything on the same scale and when the maximum is defined (hence a 100% stacked chart). At times, e.g. when comparing Actuals vs Targets, you might just want to see a RAG (Red-Amber-Green) picture. Let’s see this using an example. Let’s say that I have set targets for the four regions and I have Actuals data as below:

Tolerance - Raw Data

Raw Data

(more…)


When we need to compare data points from two time periods side by side, we normally use a column chart with two series (for the two time periods) as below:

YOY Comparison

YOY Comparison

But what if we want to see the comparison of categorised data? I have data for two years, let’s take 2012 & 2013 for simplicity. The data points show the number of employees that were hired in each department. I want to see a month by month comparison of each department, and the total as well. It cannot be done using standard charts in Excel. i.e., we can’t have two (or more) stacked columns side by side like below:

Comparison Stack Chart

Comparison Stack Chart

(more…)


Re-creating Custom Charts

Re-creating Custom Charts Can be Frustrating

Excel provides us with many chart types and those of us who work with data representation or create dashboards know that they are not enough. We do need to create custom chart types every now and then to satisfy specific needs. At the same time, some of these custom charts are so frequently used that it becomes frustrating to re-create them each and every time. But don’t worry, Microsoft is considerate enough to provide a solution to this.

(more…)


Thermometer Chart

Thermometer Chart

Continuing the series of custom charts in Excel, today I’m sharing the so-called ‘Thermometer Chart’. This chart lets you see sort of ‘how hot you are’ on a scale. This is what it looks like:

Don’t let the looks of this incredible looking chart scare you. It can be created in 7 easy steps using very basic excel charting skills. But if you don’t want to do all the dirty work, just download the ready template Thermometer Chart.

How to Read the Chart?

As in the thermometer, the inner bar is the actual value of the metric that you are measuring and the outer bar is the reference that you are measuring it against.

How to Create the Chart?
  1. Set up the data –  Continue Reading

This is an age of data, and plenty piles of it (Brad Frost will give you more precise numbers here ;)). What happens when you have this much data is that when creating dashboards, it’s difficult to present it in a manner in which it ‘pops out’ and pulls attention of the viewer. That’s why nowadays, we now have so many types of charts/graphs/visual presentations in place and that’s precisely the reason why ‘effectively communicating information’ has become so important.

I myself work with a lot of dashboards which requires me to create a lots and lots of charts. I’m starting a series of posts where I’ll be sharing some custom charts that I’ve either created or come across. Some of these can really spice up a dull looking report.

First in the series is a Heatmap that looks like this:

Heatmap Final

How to Read the Chart?

Suppose there is a scale on which you are measuring something. In my case, it was the skills of a candidate (for a recruitment analysis dashboard) on a scale of 1 to 10. Here, 9-10 was the acceptable value (Green zone), 4-7 was okay (Amber zone) and anything below it was a reject (Red zone). (I have used Orange in place of Red to tone down the colors. You can select any colors that you like).  Continue Reading