How to create a speedometer chart in Excel type
On this occasion we want to show it to you how to make a graph in excel type of speedometer in a very simple way. A speedometer graph is one of the alternatives to the pie or donut chart, which we can give the appearance of a speedometer.
In this type of chart in Excel we can configure that the arrow will mark the current status or value of a metric of our activity or report. With that in mind, we use it to mark or indicate where the focus is on achieving a goal.
According to the indicator that the arrow of our graph in excel we can indicate whether it is a good state, a regular state or a bad state.
So let's go make a graph in excel 5-state speedometer and that the arrow indicates the coverage value.
For us Excel Charts Tutorial we will have a target equal to 100, a executed equal to 20 and a coverage equal to 20%, which is what the arrow of our graph in excel.
If we say we have executed 50, the arrow will move to the left due to the ratio of execution to coverage.
You may also be interested in: >> Dropdown lists in Excel
How to insert a speedometer chart in Excel
We see, how to make a graph in excel. For this, the first thing is to generate the amount of states we want in our Excel chart.
For example, we will create 5 states and generate 5 numbers which add up to 100 and above, below them I put another number which will always equal 100%. Next, we'll insert a donut chart.
We select the data and add the values of the series, including the percentage of 100% and click on accept. Here we have the donut chart with the 5 states and the additional field 6 which has a value of 100%.
But why do we do it? To divide the circle into two parts, the one in green represents the last data, which is 100%, and the other 5 fields represent the other half of the circle.
Now we will transform ours graph in excel so that our 5 fields are facing the screen. Simply what we do is double click on our excel chart and we go to the series options and in Angle of the first sector we will say that it is 270°.
We mark the green part that refers to field 6 of 100% and we will leave it empty so that it will not be shown to us. Now let's size up the hole in the ring to make it a little smaller. With which, we have our own graph in excel somewhat more structured speedometer type.
Now we can give the color we want to the 5 states of ours graph in excelthen we select each one and give it the fill or customize the states that we will measure to our liking.
Excel chart: Speedometer arrow configuration
we already have in ours excel chart the model of the speedometer and we need the arrow that marks the indicator or the status of our metric.
What we will do is generate four new records, where we have the width of arrow 1 which is equal to the coverage, i.e. the resultant that divides the performed on the target. The width of arrow 2 will equal 2.50% and then we will produce our third record which is basically the sum of the width of arrows 1 and 2 minus 100% and below these values we will color another 100%.
Now what follows is that we are on the graph in excel, select the data and add another series with our four values. our second excel chart It will mark us on the outside of the speedometer model we had made.
And we will do the following process. But before anything else we will put our series two above and indicate the excel chart from our series two and we'll tell it to change the chart type and insert a circle chart and click accept.
Now we have this, the chart template and the interior, which is the data from the last series we added, al circular excel chart We also give it an angle of 270° and what we do now is place the bottom part of the circle with no fill and also the aspect ratio which appears in gray and blue.
speedometer type graphic
Now we have our arrow which is represented by the brown color, to which we will fill it with black color so that it looks like our speedometer. Now we can vary the execution and the arrow will move according to the percentage of compliance or coverage.
What we are left to do is a text box indicating the value of coverage that our graph in excel. We're going to put – textbox and put it in the center and make it equal to coverage.
we already have ours graph in excel type speedometer In fact, this is one way to do it, there's another way to do it that uses trigonometric functions, which is a bit more complex. This is, in our opinion, one of the easiest ways to create a graph in excel type of speedometer.
Hopefully this little Excel tutorial they liked it and that they can share it with someone who might need it. We recommend that you subscribe to our newsletter so that you can enjoy many more tutorials, tips or guides to increase your knowledge in Excel.
We also recommend that you read other articles of interest to supplement the information you have found here: