How to Create an Excel Dashboard?
A dashboard in excel is a glance of previously done calculations based on specific data. It shows the performance of the key areas or the matrices in the data.
In excel, dashboard is based on the set of data which is represented by some set of calculations. A dashboard can be viewed as charts or in tables. Before creating any excel dashboard we need to be clear about the structure of the dashboard, for example, which specific thing is to be represented where and what not.
To create an effective and illustrative dashboard in excel we need to keep a few things in mind. First, make two or more sheets in a workbook. In one sheet copy the raw data if it is from any other source or insert the raw data in it. In the other worksheets we can either create tables and calculations based on formulas and make it more visual we can use charts to represent the data.
Customizing the charts and tables helps a viewer to have a better look at the data represented by the dashboard.
Creating Dashboard in Excel Example #1
We have students data with the marks of their five subjects. We will create an interactive excel dashboard in which we can marks of the students and see if they are eligible to be awarded or not. First, we must be clear what we are trying to make, so let us have a look at the raw data.
In such a format, raw data will be pasted. The rules are if the total of the student marks is above 400 he will be awarded or else declared as fail. So the very simple dashboard should look like this,
K column will identify if the student is to be awarded or not.
- In J column Type, =G3
- Press Enter and drag it to the end of the data.
- Now in K3 type,
- Press Enter and drag it to the end of the data.
- This dashboard is based on the data provided but if we change the data, the dashboard changed too. For example, increase the marks of Vikas in English from 65 to 88 and see the result.
From Fail, the status was changed to awarded.
The above example was a very basic dashboard using just sum and If excel function.
Creating Dashboard in Excel Example #2
In this example we will create a complex excel dashboard, using many functions and charts and graphs. First, we need to have the data in excel. If the data is from any other source, we need to import it in excel. We will make two worksheets, one in which there will be raw data and in another, we will make our dashboard based on that raw data.
First, we will make tables based on the raw data provided. Then based on those tables we will make the charts and the dashboard.
The employees of the company have some baselines defining their performance and we will make the dashboard in the employee performance. Have a look below at the raw data,
The total performance score is calculated by summing each parameter by multiplying by its weightage. Then we will show the performance of the employee based on his performance score and we will also show the key parameters in which the employees need to work. We can create this excel dashboard by making tables and slicers but we will use charts as they are more illustrative.
- Calculate the employee performance score, in cell Q2, write the formula,
- Select the array from B3 to F3.
- After inserting a comma, select the array from J3 to N3.
Currently, the parameter score is zero so performance score is displayed as zero.
- Drag the formula to cell Q6.
Now enter the data to the Parameter.
Then the Performance score will Change as given below.
- Go to the dashboard worksheet, and in the Insert Section, under the charts category click on, any chart, in this case, we will choose a line chart.
- Right Click on the chart and click on select data.
- A dialog box pops up.
- In the Chart Data range box, go to the raw data sheet and select the performance score section.
- Click on OK and the chart is prepared in the dashboard sheet.
We can insert any more tables or charts on the basis of the data we calculated, and as soon as the data changes the results in dashboard changes too.
Explanation of the Dashboard in Excel
- Dashboards are a very effective form to represent data. It can give a simple status update to a finalized report for any work. In day to day lives, almost everybody uses dashboard at some point in their lives. They are very easy to create if we have a clear mindset of what we are trying to make and we analyze our data properly.
- A dashboard is a glance of previously done calculations based on specific data.
Things to Remember About Creating a Dashboard in Excel
There are a few things we need to remember while creating a dashboard in Excel:
- Have data setup in a worksheet. Analyze the data properly on what data ranges need to be visualized.
- We need to have a clear mindset about how the dashboard will look alike.
- Customize the charts and tables to have a better visual of the data.
This has been a guide to creating Dashboards in Excel. Here we discuss how to create a Dashboard in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –