How to create Gauge chart in Excel?

visualization
dashboard
excel

#1

Hi,

I am creating a sales dashboard in excel and I want to visualize four key metric with the help of Gauge chart. Can you help me to perform this?

Regards,
Mukesh


#2

@Mukesh

It is good to create guage chart to visualize four or five key metrics like Target Vs Achv, Performance against last year,compare to competition and others.

In excel, gauge chart is not the inbuilt available option. Here we can create it with the help of two different chart types.

  1. Doughnut Chart: It is used to represent the color scale of gauge chart (Red, Amber, Green)
  2. Pie Chart: It is used to represent the needle of gauge chart.

Let’s say, we want to represent target vs achv% using gauge chart. Here actual %achv is 70 and color scale are less than 50 for red, 50-75 for amber and 75-100 is green.

Now, follow the below steps to create it:

Step1: Create table for color scale and needle

Color Scale table:

  Red           Amber          Green            Rest
    50             25            25                100

Here, sum of red, amber and green value is 100 and value for rest is 100 because we are going to use doughnut chart and it is in the form of circle and to represent color scale we would require only half of circle so other half(Rest) also has same value.

Needle Table

Actual_Value/Start           Pointer         Rest
    70                      1               129

Here actual value is 70 and want to represent with needle and assume the size of the would be 1 so total is 70+1=71. Now we are going to use it with doughnut chart where sum of the values are 200 so in this table value for Rest is 200-71=129.

Step2: Create doughnut chart based doughnut table.

Step3: Change the angle of doughnut by 270 degree, you can see above that doughnut is vertical.

Step4: Remove color fill of second half of the doughnut and no border. Here you can change the color of individual component as red, amber and green.

Step5: Create Pie chart based on needle table.

Step6: It is vertical, rotate it by 270 degree by selecting first slice.

Step7: No fill for Start, Rest and No fill for chart background also. and change the color of needle slice of pie to black for better look.

Step8: Now equal the size of both Pie and Doughnut chart and superimpose pie chart on Doughnut chart. Here you have your Gauge chart.

Hope this helps!

Regards,
Sunil