Let’s start by creating a workable speedometer
or gauge graph.
The speedometer consists of two charts and
two tables, which are our data the speedometer use.
The first data table, called Speedometer data,
has a start and a blank area value and has
also the values of the three basic areas:
red, yellow, and green.
It also has a Series Level column, useful
for the labels of speedometer.
All these components will be used by the first
chart.
The second data table will be used for creating
the needle of the speedometer.
It consists of three components, the value
of the needle, the size of the needle, and
the end, which is a formula – a calculation
that we need to have for the needle to be
in balance with the whole chart.
The calculation is the total of B3 to B11
cells minus the total of B16 and B17 cells.
Of course, we can change any of these cells,
except the formula cell.
To create the first chart, click on an empty
cell, go to insert tab, pie charts category,
and doughnut chart.
Now, from design tab, choose Select data.
Click on Add Series.
The series name will be the A2 cell, the Speedometer
Data title.
The series values will be all these cells
plus blank area, from B3 to B12.
Click OK.
Now edit the horizontal (category) axis labels
and choose all series level cells.
Click OK and OK again.
To make it impressive, click any slice and
from format tab, add a shape effect like cool slant bevel.
We want to change the orientation of the doughnut
chart.
Right-click any of these parts and choose
Format data series.
The option we need is the first: angle of
first slice.
Put 270.
Also, decrease the hole size to 40%.
We don’t need the large slice, so we have
to choose no fill for color.
Click on it and go to bucket - Fill&Line tab
and choose no fill.
We will change the color of the other slices.
Start from this: change it to dark red from
more colors, red, orange, yellow, light yellow,
Delete the legend also.
If you want to resize it, it’s okay.
Finally, from plus icon, go to data labels,
more options, and check category name (uncheck
the others).
From home tab, change the font size to 12
and bold.
The first chart is ready.
To create the second chart, you will have
the doughnut chart highlighted.
Now from design tab, choose Select data.
Click on Add Series.
The series name will be the A15 cell, the
pointer data title.
The series values will be all these cells,
from B16 to B18.
Click OK.
See a second doughnut chart around the first.
We will be convert this chart into a needle.
The first step is to change it to a pie chart.
Design tab and choose change chart type.
Go to Combo chart – it’s the last from
the categories, and the pointer has to be
a pie chart and don’t forget to check secondary
axis.
And OK.
Right-click any of these parts and choose
Format data series.
The angle of first slice has to be, again,
270.
Also, change the fill color from this tab.
No fill for this ….and no fill from this
slice.
The color of the needle should be black….
And no border.
Better now.
The last step is to add a data label because
it’s crucial to know exactly the value of the needle.
Edit the horizontal (category) axis labels
and choose B17 then with CTRL B16 and B18.
Click needle, plus icon, data labels, more
options, and category name.
Delete the other labels.
Finally, highlight this label, go to home
tab and increase the font size and bold (if
you want change the font color to red)
We created a really impressive speedometer chart.
Let’s try to change the value of the needle.
It all works fine.
Of course, you can change the colors of the
chart area or add a shape effect.
Speedometers are often used in professional
dashboards.
