Friday, October 28, 2011

How To... Create a Basic KPI Dashboard in Excel 2010

With increasing amounts of data now being processed and the concept of "Big Data" taking over Business Intelligence and Analytics - it is important to be able to use tools to visualize large amount of data. Since many people use Excel for Data Analysis - it makes sense for people to be able to use what they are familiar with to analyse their data.

Last week I saw a neat Excel Spreadsheet that had traffic light icons (red, amber, and green) beside some of the figures on the sheet. They were being used as a KPI Dashboard - KPI stands for Key Performance Indicator. The traffic lights were being used as a status for each number to provide a visualization of the data. If you see a lot of green lights you get a sense that overall performance is good, but a lot of red lights indicate poor performance.

It turns out that this type of KPI dashboard is really easy to create using the Excel Conditional Formatting function. For example, just tell Excel that any percentage value over 90% should show a green light, any value between 80%-89% should show an amber light, and any value less than 80% should show a red light. This will result in a series of traffic lights whose colour will depend on the values shown. Check out my latest YouTube video to learn "How To... Create a Basic KPI Dashboard in Excel 2010":


  1. Thanks for this KPI formulation with Excel. It is very applicable for small companies who are looking to adapt the score card valuation. We also use small business software and we could formulate KPI grades based on monthly performance.