What is a Gauge Chart
Gauge chart also known as dial chart or speedometer chart. It uses a needle on a dial shape to show information as a reading on a dial. It is often used for a single data point and aims to track the progress of a KPI against its set target or against some other time period.
It’s biggest advantage lays in the fact that it is easy to interpret. It also uses different colors to represent different scales of the variable which is the part of KPI. Usually these scales are the maximum, the minimum or the medium. Gauge chart is suitable for executive dashboards where stakeholders want to see where KPI is against the pre defined parameter.
How to create Gauge chart in excel with multiple values
In excel to create Gauge chart we will be using doughnut and pie chart. The doughnut will be used to show different regions to different values and Pie chart will be used to show pointer.
We will use 2 sets of data, first set will have 4 rows of data while nest set will have 3 rows of data. From first set , initial 3 values represents first arc of Doughnut while 4th value represents second arc. And, in next set Value represents the value that we want to show, the pointer is the size of pointer(to make it thinner we can decrease the number) and the End is 200(it completes pie chart) – value – pointer.
Now we will plot a doughnut using the first data point. This will look like as below image
Now we will double click the half portion of doughnut chart(yellow one) and then select Format Data Point option by right clicking on it. We will get a new box on right side and then we will select on No fill option. Now the chart will look like this.
Now again we will right click on chart and then right click on it, we will get select Format Data Series option. In the new option box on the right side , the value of first option Angle of the first slice needs to be changed to 270. The below images illustrates the same process and new chart visual as well.
Now, again we will change the value of last option(Doughnut Hole Size) in the box to 50 to make it more wider.
Now its turn to change the color of the slices.
Now first part is completed e.g. value part in the next step we will work on second part that is creation of pointer. Right click on the chart and click on Select Data.
A new pop up box will come like below.
Now click on Add option and select data as illustrated in the below image.
Now we will go to Chart Design tab then Change Chart Type, a new pop up will come and we will select options as mentioned in the below image.
Now our chart will look as shown below in the below image.
Now we will select both bigger pies of the chart and then select No fill option. Then fill value of 270 in the Angle of first slice. After this select pointer on the chart and then select Solid fill in both Fill and Border with Black as a color. The new chart will look like as below.
In the final touch we will change value of pointer from 5% to 1% to make it more thin to look like a needle. Now the final chart will be like below. Also put can put a data label of % completed.
That’s all for gauge chart for now. Will meet again with something new,
You can read more on gauge chart from the below blogs.