Speedometer KPI Dashboard
The speedometer kpi dashboard offers a spectacular visualization to present sales performance of representatives. In Excel there are more tricky ways to create the speedometer kpi dashboard, and in today’s article we will show you the designs that are built on the pie chart and doughnut chart.
The created speedometer kpi dashboard could be a useful instrument for other (not only for marketing/sales) fields also for visualization between the goal and actual values.
With a little modification it is able to show not only the 0-100% range but to display other arbitrary values on the speedometer kpi dashboard.
In the first step we have to create the data structures used by the dashboard and fill them up with the desired values. For this we use four simple data tables:
In the first data table there is the performance of the sales representatives (their name, actual value and goal value) which here we filled out with arbitrary values. To the second data table “Selected Sales rep” we put the data of selected sales representatives from the dashboard (in K7 cells) from the Sales Performance table for which we used the VLOOKUP function.
The third “Zone Settings” chart controls the colors of the speedometer red, yellow and green zones. We talk about this in detail in the 2. Step. The fourth data table controls the ticker of the speedometer. We talk about this in detail in the 3. Step.
Step 2: Color zones of the speedometer kpi dashboard
We create the color zones of the speedometer kpi dashboard with a help of a doughnut chart. In diagram controller chart 3 first we determine the values we consider bad (red zone), acceptable (orange zone), and good (green zone) comparing the actual values to the goal values (in our example 35%, 35% and 30%).
After this we fill out the proper values to the proper places in the chart, then into the hidden zone/section write 50. (Into the hidden zone/section we can write a number other than 50, but then the later be created doughnut chart wouldn’t be 240 degree but will show the color zones in a different range.)
After the fill out choose Charts and “Insert > Charts > Other Chart” menu choose doughnut chart. After this right click on the area of the chart and choose “Format chart area” option. With the help of the highlighted cells on the right hand side picture turn the diagram so the “hidden zone” will be on the bottom. Also set the inside size of the doughnut chart to 90% so that the outside colored zone be thinner.
When we done with this we will do the unique filling of the color zones. Right click on the given zone and choose the Format Data Point option. Under the Fill option fill the given sections with arbitrary ways and color-shades (in our example we have chosen the color-transition filling), also in the hidden zone chose no filling.
Step 3: Creating the indicator of the kpi speedometer dashboard
The whole values of the cells of Table 4, that controls the speedometer, and whole values of Table 3 has to be the same in order for proper operation. We create the pie chart after filling out the values in the following manner.
Right click the given doughnut chart and chose the “select data” option. Click on the “Add” button and mark the values of chart 4 (H15:H17). Than Excel will create a new doughnut chart over the already existing one. Right click this new diagram and chose “Change series chart type” option, and in the pop up box chose the two dimensional pie chart.
Step 4: Format kpi speedometer dashboard
Our last step is to create the sales representative selector stripe located in L7:Q7 and to format the speedometer.
To create the selector stripe click in cell L7, than on the Data ribbon select the “Data validation” option. In order for the stripe better appearance in our example we wrote out the goal, fact and status (%) values, also under the conditional formatting button we assigned the selecting stripe with the value responding colors corresponding to Status (%) values. Follow our excel dashboard training!