How to create sales dashboard? Today’s excel template is part of a Excel Dashboard series.
We showing you how to make a great looking sales dashboard ground up using Excel.
Let’s start with a well known and loved subject by all, analysis of sales information and facts. When creating this executive dashboard we endeavored that it will be no trouble for even beginners to understand the operation of it.
We hopeful that after reading this excel tutorial you will handle it easily, whether you use it for work or study.
Create Sales Dashboard using drop-down list
Let’s take a look at the figure below. We have designed a simple list that allows us to be able choose between the months. We highlighted the drop-down list with green color.
In column “B” there are the names of the locations that we will make the comparison of. In the horizontal direction we have the given city’s income data for the given month.
Because the data will be summarized in column “P”, that’s way if you are picking the month of July, than the period from January to July will be accounted for.
Accordingly if you are choosing December, in that case you will have the whole year’s income data available.
Does this sound simple and easy?
Let’s look behind the curtains now and we will show you what kind of gears we have used to create sales dashboard.
Create Sales Dashboard using OFFSET and MATCH
The OFFSET and MATCH formulas will help your work.
The OFFSET will give a resulting data that is given number of lines and columns distance from the given reference.
With the MATCH formula we give a search value, after that we give a search range.
The combination of the two formulas will give the result we want.
We will use these formulas subsequently, they are very useful in making excel dashboards.
Let’s bring a little color amid the calculations! How can we improve the sales presentation?
Sales Dashboard – Dynamic charts
We place the sales dashboard under the main chart, it is visible that upon choosing a month from the list it is changing dynamically. Of course you can shape this the way you want (colors, patterns, shades, etc. using ’Shape Fill’, ’Shape Outline’ or shape effects tool.)
We wouldn’t like to complicate the introduction of the formulas anymore, let’s focus on the bottom line. We think it is most effective, if you try to create sales dashboard.
Use the following link to download free template or watch the tutorial on YouTube!
Create Performance Dashboard
The sales performance dashboard is the best possible tool for portraying the key performance indicators (KPIs) of the company in this field. In one of the first article of our blog we showed the usage of the speedometer, let’s get acquainted with it some more.
Let’s see what kind of improvement our speedometer dashboard has gone over in the past weeks! The most important novelty is that we have resolved one of the most aggravating barriers characterizes these kinds of charts.
The 0 to 100 fixed scales are over! Because of this characteristics most people was reluctant to use them.
Sales Data Visualization
We have imagined the data-visualization that content is primary but we didn’t want to give up design either. On this picture the values can be seen belonging to the three cities already on the speedometer.
According to the rules of the key performance indicator, modeling the critical, acceptable and great values the red, yellow and green color range can be set at will.
Detailed calculations can be found in the worksheet so that you will have an easier task regarding the procedures linked to the different calculations.
You can learn a lot from it and it will come at handy when you would like to create something new based on your own plans.
Excel Dashboard Functions
If you create excel sales dashboard a drop-down list will help the get the appropriate data which contains the number of the products. If from the list we would like to use data for Product 2 on city levels and portray it on the gauge, than we will need the values 118, 163 and 81.
We will use the figure combinations of MATCH and INDEX in order to assign to each city (Barcelona, Victoria, Yorkshire) the marketing values of the four chosen product.
This executive sales performance dashboard shows at a glance which areas are profitable or unprofitable, which product is generating the most sales.
This MATCH – INDEX formula may firstly seem difficult but very useful.
The capability to update data being presented in real time is a necessary part of the procedure.
Use this template to create sales or various excel dashboards.
We hope this example will be useful for you if you can create sales dashboard.
Download the speedometer excel template!