Excel Traffic Light Dashboard
We have good news for the Excel community! No more traffic jams if you use Excel! Just work or learn using our fully editable and free (as always) Excel Traffic light dashboard.
Setting up your business targets is a primary element of deciding on the business that’s most suitable for you. Using these indicators you can measure business or employee performance.
Excel Traffic Light Dashboard – KPI signals
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. The green light needs no explanation when we see this on the dashboard than everything is in the best order. 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 dashboards, KPI reports or other key performance indicator templates.
These dashboards can help us to understand what type of focus to give to our key performance dimensions. We designed a basic free dashboard one year ago. Now we continued the work to create a fully redesigned excel sales dashboard with better visualization option. It is a simple product comparison template with two new features.
Our sales dashboard will show you how to make a great looking business template from the ground up using Excel and traffic lights control. We have 4 products to measure yearly sales performance.
Excel Traffic Light Dashboard – Formulas
We used classic Excel formulas like OFFSET and MATCH.
The OFFSET function is a great help in many situations. In our case we use it to sum up the values of a period under the chosen month. The MATCH function can be greatly used for search.
We do not want to go into details, we recommend you download the template from under the article so you can learn its use a lot quicker than if we would explain it on several pages.
You can check the drop-down list, it contains the months. If you select the ’August’, the dashboard summarize the sales data from January to August. If you select the ’April’, the sales amount will be summarized from January to April.
Excel Traffic Light Dashboard – Dashboard Area
Our Excel Traffic light dashboard is divided into three sections. In the first part there are the names of the months with four chosen products. There is data linked to every month and every product, you can modify these as you like.
The Total Sales column contains the marketing values of the four products in monthly breakdown. Naturally we display the yearly result for all products, you can find this in column “SUM”.
The left area of the template contains the sparklines (mini excel charts) and the drop-down section too.
In this case we use sparklines to display trends in a series of values. The expressiveness of these mini charts are amazing. First we have encountered them several years ago, it is fascinating how they can place the numbers in context.
Because a lot of times we are not interested in the number itself but we want to see how it compares to the previous period. And these tiny graphs are prefect for the job. If we have to analyze yearly data and display trends we recommend their use very much.
On the right side of the dashboard would be placed the traffic lights section, you can change the default KPI settings on the calculation worksheet. The signals work similar to traffic signals.
Excel Traffic Light Dashboard – Summary
A few words of summary: with the help of the Traffic Light Dashboard we display information from a large quantity of data.
It is important that we can manage to do this only on one worksheet. Usually this is the expectation of the management that’s why we are creating dashboards!