Learn how data analyst use MS Excel to create KPI (thermometer) gauge chart
One of the key responsibilities of a professional Data Analyst is to build KPI charts for companies.
In this short tutorial, you will learn how to build the template of a KPI gauge (thermometer chart) using MS Excel to monitor the performance of the Sales team of a company in Port Harcourt.
What is KPI and whatβs the purpose?
KPI stands for key performance indicator, a quantifiable measure of performance over time for a specific objective. Companies use KPIs to provide targets for teams to shoot for, milestones to gauge progress, and insights that help people across the organization make better decisions.
KPI in MS Excel
In MS Excel, KPIs are represented as visual measures of performance. Supported by a specific calculated field, a KPI is designed to help users quickly evaluate the current value and status of a metric (ACTUAL) against a defined target (TARGET).
—–
How to create KPIs in MS Excel
In KPI there are 2 variables, an expectation (TARGET, set to 100%), and an achievement (ACTUAL, set to a percentage of what is achieved).
KPI can be used for the HR dashboards, sales or any other department that needs evaluation and reporting.
Examples of KPI charts
- Thermometer Chart
- Dial / speedometer gauge
- Funnel Chart
- Heat map
- Waterfall Gantt chart
- Bullet chart
In this tutorial, weβll focus on Thermometer Chart.
In Excel, all KPI charts are not there by default, you create them. In Power BI these charts are included in the advanced chart sections.
—–
How to create a thermometer chart in MS Excel
Step 1. Create the actual and target dataset
Step 2. Select the headers and the data, go to insert. In charts, go to bars, and select the stacked column in the middle.
Step 3. Right click on the target column (orange colour), and select format data series.
Step 4. In the format data series, under series option, check the secondary axis.
Step 5. Next in the chart, click the vertical (value) axis.
Step 6. In the format axis, under text option, set the maximum to 1.
Step 7. Click the secondary vertical (value) axis.
Step 8. And also set the maximum to 1.
Step 9. In the chart, click on the bar (orange colour tagged series target point 1). Then go to the format data series panel, click on the fill tool. Under the fill section check no fill. Under border check solid line. Under colour select black. Change the width to 1.
Step 10. Click on the plus sign on the top right of the chart (chart elements), uncheck all the items except Axes. And in Axes, uncheck all the items except primary vertical.
Step 11. Click the vertical (value) axis. Then in format axis, click the text options. Go to Tick Marks, and set the Major Type to Inside.
Step 12. Finally, reduce the width of the column.
To create the base of the thermometer, go to insert, illustrations, shape, oval. Create an oval shape. Position it at the base of the bar. Go to format shape, set the fill to solid fill, line to solid line, and color to black.
Step 13. Change the actual value to 25% and confirm that it is working.
You can create this for the sales team and use it to evaluate the performance of each member of the team.
If the business has several branches, with a Pivot Table, you can create a KPI chart for each state. And then visualize all the branches on one single dashboard. This way, the head office can evaluate and make decisions easily concerning the input of each branch. This is one of the main functions of a data analyst.
To learn more …
