Gauge charts, also known as dial charts or
speedometer charts, use needles to show information
as a reading on a dial.
On a gauge chart, the value for the needle
is read against the coloured data range or
chart axis.
This chart type is often used in executive
dashboard reports to show key business indicators.
Gauges are a familiar metaphor, everyone can
understand them, you can see them everywhere
– near your stove, ac, car, gaming console,
pc – you name it.
So, when you are preparing a chart to tell
a point, gauge chart like the one on your
screen right now can be very effective.
Unfortunately Excel doesn’t have a gauge
chart as a default chart type.
They of course have a 3d line chart, but let
us save it for your last day at work.
Meanwhile we can cook a little gauge chart
in excel using a donut and pie; sadly not
the ones to eat but the ones you find in the
excel charts.
Frist of all get your data ready.
So, what do you need for a gauge chart.
You need the gauge and the needle.
Let’s get the gauge ready.
For preparing the gauge, you first of all
need the gauge size.
Let’s say the gauge size is 100.
Then you need the size of the various zones
in it.
So, here we are taking the zones to be green,
yellow and red.
Let’s say the size of the green zone is
45, the yellow zone is 10 and the remaining
45 will be the red zone.
Now we need to create the gauge using this
data.
To hack a gauge in excel, we will use the
donut graph.
If you see the donut graph carefully , you
will realise that if it is horizontally sliced,
it will look like a gauge.
Therefore what we will do is that we will
double the data size we discussed previously.
So, if the green, yellow and red zones combined
make 100, we will add a white zone which equals
100.
Using this data we will add a donut graph.
We will go to Charts, click on others and
select donut; make sure not to use the exploded
donut chart.
Now, we have a few issues in the graph; firstly
the colours are not as per the requirement,
secondly the rotation of the graph is not
proper and third, it has got shadows which
we may not want.
Let’s solve these issues one by one.
But first of all we remove the legend as it
is useless information; so we just click on
the legend and press delete.
We now select every part of the donut and
add the required colour.
Like this large portion is needed in white,
to look like it doesn’t exist; so, we will
click on it.
See when you click on the chart the entire
chart is selected, so you click on it again;
make sure it is not a double click and you
will see that this portion is only selected.
You can now go to HOME ribbon and select the
white colour.
Then you go to the first section and in the
same fashion select green colour, then yellow
for the next part and finally red for the
last portion.
Now if you see, white is not camouflaging
with the chart background because of the shadow
which comes by default in most excel versions.
So, we will select the white part again; go
to chart format from the ribbon; then select
effects, shadow and click on ‘No Shadow’
which is the second from the last option.
This will remove the shadow and your donut
is now sliced.
Great!
Now our gauge chart is ready but the orientation
is not correct, looking from a clockwise rotation
point of view, it needs a 270 degrees rotation.
So, we will select the entire chart again,
and yes you go to the chart and select the
donut only so that all parts of it show their
borders; that is the selection that you need
to make.
You right click now and in the pop-up window
select Format Data Series.
A new window opens up which has got many options
on the left hand side.
You select the first one which reads ‘Options’
and on the right had side where it says ‘Angle
of First Slice’, you can see the default
number is zero degrees.
Change it to 270 degrees and click okay.
If you delete the degree symbol, do not worry;
excel will take this on its own.
So, now you see the magic!
The gauge is ready.
Let’s keep it aside and work on the needle
In order to create a needle, we will use a
pie chart and hack it.
Note that the total value for the pie chart
should be the same as the donut.
So here in this case our total value is 200.
As you can see from the table here, I have
taken the indicator value which is the value
that you need to show.
Then there is the indicator needle which can
be kept to as low as possible and stacks exactly
after the indicator value.
Then you have the remainder of the pie which
is 200 minus the sum of indicator value and
indicator needle.
Now, using this data, let’s create a pie
chart.
So, select the data and , from the ribbon
select charts.
Do not directly click on the pie icon because
it will insert a 3D pie which we do not want.
You see a small dropdown at the corner of
this selection and select the first basic
pie from the 2D pie chart options.
So, Here it is!
First thing to do now is to remove the legend
as we did with the donut.
Now in order to hack it and show the needle
only, we will need to make the colour of the
other two parts of the pie chart as white.
So, repeat the process that we did with the
donut.
Select each portion reach out for the home
ribbon and fill colour white.
Well actually we want this to be transparent
so we use no fill here.
You can use a dark…. say a black colour
to denote the needle.
Now we remove the shadows from the portions
of the pie chart similar to the donut white.
So, select each portion go to chart Format,
effects, shadow and select ‘No Shadow’
Now we know that the rotation of this chart
is also not correct so we rotate it by 270
degrees.
Select the chart area, right click; select
format Data Series and from the Options tab
change ‘Angle of first Slice’ to 270 degrees.
We have to now put the needle on the gauge
but the problem is that the background of
the needle is white.
We have to make it transparent.
So, we will just select the entire chart,
and from the home ribbon in the fill options
select No fill.
Now, move the needle chart on top of the gauge
and you can see this beautiful speedometer
gauge.
Let’s test it now by changing the indicator
value
We have used a data range of 100 so for a
value of 50, the needle should be a vertical
line.
Lets see what we get here…Great!
See the graph, this is actually a perpendicular
line.
Test it will values of 0 and 100 to see the
min and max values.
Yes, it is working!!
There will be obviously a slight difference
because the needle is your value plus one;
but we can live with this small compromise
here.
Now, I would also want to have the value of
the indicator shown with the needle.
Let’s do a small hack here.
Let’s take this needle chart aside and select
the entire pie.
Right click on it and then click ‘Add Data
Labels’ option.
You will see the labels appearing on the needle
chart.
Then we click on the pie Again and select
format data labels.
The new window that shows up, here instead
of the values we will select ‘Category Name’
and the label position can be kept as outside
end.
Then click okay
Now, we will in the table select the indicator
needle category name and make it equal to
the the value of the indicator value data.
So, you can see that the legend for the needle
shows in the value in front of the needle.
Now you click the other two legends one by
one and delete the labels so that no other
label is visible.
We have this indicator needle with value ready
and we just superimpose it on the gauge graph.
Now, make sure that the size of the donut
graph and the needle graph is the same so
that the needle positioning is correct.
So, finally you have your own gauge chart
ready.
You can link the indicator value cell to any
data that is needed and also possibly pimp
up the graphs with some logos or images or
display information.
Believe me it is a simple 10 minute process
to create this chart.
I have also added a link in the description
to download the excel sheet which has the
charts ready.
If you still need clarification on any of
the steps or are stuck somewhere, please do
not hesitate to drop your queries in the comments
section below.
