Excel Traffic Light Dashboard Template can track your sales quickly and supports KPIs using stoplight indicators.
Setting up your business targets is a primary element of deciding on the business that’s most suitable for you. Then, using these indicators, you can measure business or employee performance. Traffic Light Dashboard can display a large amount of data using a small space.
How to build a traffic light dashboard
#1: Create a wireframe
Before diving into implementing the dashboard, it is worth creating a quick sketch. The plan should contain the dashboard wireframe, the applied metrics, charts, and form controls.
In the picture below, we created a dashboard wireframe with a drop-down list, sparklines, and traffic lights.
Once your plan is ready, it is time to start the next phase and prepare the data.
#2: Prepare Data
First, we split the dashboard into three sections. In the first part are the months’ names with four chosen products. After that, we link the data to every month and every product. Of course, you can modify these as you like.
The Total Sales column contains the sales of the four products in the monthly breakdown. We display the yearly result for all products; you can find this in column SUM.
#3: Insert a drop-down list
You can check the drop-down list, and it contains the months. For example, if you select August, the dashboard summarizes the sales data from January to September. Likewise, if you choose April, Excel will sum up the sales from January to April.
We use INDEX and MATCH functions to summarize the values between January and the selected month.
#4: Create stoplights using conditional formatting
Using conditional formatting, you can create in-cell traffic lights based on rules. First, display the traffic lights section on the dashboard’s right side.
Then, change the default KPI settings on the calculation worksheet. After that, the signals work similarly to traffic signals.
#5: Insert sparklines
The bottom-left area of the template contains the sparklines (mini charts) and the drop-down section. In this case, we use sparklines to display trends in a series of values. Using these mini charts can help us to save space. Although we first encountered them several years ago, it is fascinating how they can place the numbers in context.
Because we are often not interested in the number itself but want to see how it compares to the previous period, these tiny graphs are perfect for the job. Therefore, we highly recommend their use If we need to analyze yearly data and display trends.
Traffic Light Add-in for Excel
Check our chart add-in if you need more dashboard power; we introduce a free solution! With its help, you can create stunning dynamic traffic light widgets.
About KPI Widgets
The yellow sign warns us that – although we are not too far from the plan – the examined process, in our case the marketing, needs increased attention. When we see this on the dashboard, the green light needs no explanation, and everything is in the best order. But, of course, we like this the best!
KPI traffic signals are those red, yellow, and green colored symbols or icons that we see in excel traffic light templates or gauge chart templates. These dashboards can help us understand what type of focus to give our key performance dimensions.