How to make a Dashboard in Excel? If your goal is to monitor, summarize, and analyze large volumes of data, then a Dashboard in Excel is for you. It is a tool that allows you to evaluate the performance of a project or process according to a set of indicators.
A Dashboard in Excel or “Tablero” by its definition in Spanish, has become a very popular tool today, since this reporting instrument helps you to show relevant information of a company in a simple way, and also easily reading.
How to make a Dashboard in Excel? What are the main requirements that you need to include in your Dashboard?
We will share some examples of dashboards designed in Excel that you can apply in your work.
If you don’t have knowledge about how to make a Dashboard in Excel, follow the steps below:
When creating a Dashboard you must define what its objective will be, that is, answer the following questions:
- What are you designing the Dashboard for?
- What data will the dashboard provide?
- What is the business information you want to know through the indicators?
Once the objective is clearly defined and established, you proceed to continue with the rest.
For example, if you want to know how the Human Resources department is doing, the objective of your Dashboard would be: Give a perspective of the human resources area. That means that your Dashboard must respond to it.
The previous image shows specific data (indicators) that demonstrate and meet the objective, since it frames all areas of the company, hiring, salaries, years of service, etc., that is, information concerning the human resources department .
-
Information linked to the objective
By establishing and defining the objective correctly, you will be able to design the dashboard indicators based on that objective, which is why this step is related to the previous one and is very important.
For example, if your Dashboard will be aimed at the production area, you will have to include data related to this department and not human resources, finance or another. It is essential that you always keep in mind that the information must be linked to the objective of your Dashboard.
Now, if the objective of your Dashboard is: “Show the number of manufactured parts, the orders awaiting processing and what is the production rate”, we will show you the following image:
If you look closely, the amount of product A in the warehouse, or the percentage of absenteeism from the department, should not be shown on the Dashboard if they do not correspond to or are linked to the objective.
A dashboard must be clear and clean, this means that the more unnecessary data is placed, the more confusion there will be and the reading of the information will not be clear.
-
Induce action in each component
When creating a control panel, each component or data must provoke the action, that will be your main reason and one of the most important steps.
For example, if one of the indicators is related to the number of pieces that were produced yesterday, then the rate will indicate that you are going low, therefore, the action it will cause is to put more effort into ending the week successfully.
Another example would be, if your control panel is aimed at the inventory area of the plant, one of the indicators would be to show the products that have the most demand, then, the action that this indicator would cause would be led to verify if you will have enough stock ( products) to cover the demand for the following months.
With both examples it is evident that each indicator on the dashboard leads to or causes an action in the department and/or company.
In this step you should start your Dashboard in a general way and move on to the data in a more specific way.
For example, if your Control Dashboard will be about the sales department. You can start with those sales that the company has had for a long time, this translates into a very, very general data.
Now, in the next component, show the summary of sales for a particular year, which would be more specific data. And while you go down and read the information, each indicator is shown with more specific data. In a few words, it is about designing the control panel in a general and specific way, that is, that it has a vertical structure.
Reports that are displayed on a single sheet are usually easier and simpler to review. What’s more, I practice showing a report or dashboard that way, since we assure you that if you show one that has 2 or more screens it will be more difficult for them to review it.
The most important aspects of the department can easily be displayed with the indicators on a single sheet.
Remember that the methodology of your Dashboard must be easy, agile, general and, above all, generate an action.
Now, if you want to include information about something specific, it is advisable to make another indicator board for it, where you can indicate the data and details of the requirement.
If your requirement is related to analyzing the particulars of a product, for example, do not clutter the main dashboard, remember all the previous steps, run a dashboard specifically for that product.
-
User-friendly components
Remember that each component must be interpreted in a simple and straightforward way, this does not mean that it will be easy for whoever is designing the board, but rather for the user who is going to read all the information.
Always design your Dashboard with the end user in mind, because it is those people who will be constantly checking the dashboard.
For example, if on your dashboard you place indicators such as:
- The profitability coefficient
- business growth rate
- The percentage of retention of a client
- gross profit margin
But this is aimed at the production area of the company, it is clear that the staff will not understand anything that you are showing them. For this reason, it is extremely important to include clear information that can be understood by the corresponding personnel.
This is one of the indicators preferred by managers, since it shows data trends, either in graphs or in tables.
That is, it tries to anticipate the data, because these components allow them to answer certain questions such as:
- If we continue like this, what will happen next week?
- How will tomorrow be?
In addition, the graphs show, for example:
- How many products were sold?
- How is business currently?
If you show trends, you will be able to know if the goals will be met in the future or if they will be achieved sooner than expected. All of this information helps to give you the go-ahead and interpret the dashboard much easier.
So, are you ready to present your Dashboard? Hopefully these points will help you exploit your ideas and you can design your control panel in a simple, efficient and complete way.
