Excel Chart family has powerful tools to present data (Area Charts, Pie Charts, etc.). But, sometimes we have to go beyond the power of Excel inbuilt functions. These types of things require some creativity with proper knowledge of excel.
Today, we are going to create a Gauge Chart In Excel.
If you want to present a progress chart regarding some of your projects, gauge chart is the best option for you. It will help you to present both complete & pending parts of your task. Following is a simple gauge chart showing complete & the pending status of a project.
We have two options to create a gauge chart, one is to create it by using pie chart & other is by using an office app to create a gauge chart (available in 2013 or later). We are going to learn it in both ways But, I always found more fun in creating it by using pie charts. You’ll find this later in this post.
Let’s note down some points before jumping into it.
So, Let’s Do it.
I have created a dummy data set where a construction company needs to construct an 18-floor building. And, the project head asked the site manager to send a progress report at every morning. So, now let’s help the site manager to build a cool progress chart.
Create a table to capture construction status of each floor.
Now, we have to create a pie chart & we are going to use its half part to a create gauge chart with following data.
Please note that I have used min function in M2 cell to restrict value more than 100.
Now, on the basis of above figures, we got a pie chart.
But, remember that we only need half part of the pie chart. One more thing I want to mention, that this is not the default look of a pie chart, you have to rotate the chart so that the unwanted half part stays at the bottom. You can do this by following steps. Format Data Series -> Series Option -> Change Angle of First Slice.
After hiding half part of the pie chart, we got the first look for our gauge chart. You can do this by using fill option.
So, After hitting some customization, we have got a cool gauge chart. Hope you like it.
Microsoft has introduced a nice app feature in excel 2013. You can download lots of useful charts from the app store. Fortunately, we also have a gauge chart on app store & 🙂 it free. So, before using it, you have to download it in your excel application. Just follow some simple step.
Insert -> Store -> Search for Gauge Chart & Install it.
It will give you a cool gauge chart which looks like a speed-o-meter.
We also have options to customize it. You can change themes, number range, customize color highlighters etc.
This is a very personal view, I will always prefer to create a gauge with own hands rather than using from the app. But, If you don’t require so much customization, you can go with your ready made gauge chart.
You can use gauge charts & speed-o-meter chart in various dashboards where you have to show the single parameter. As most of the time gauge chart are space consuming but you can use them as per your need.