How to create Excel Dashboard? – Excel Formula


Excel dashboard designing is very easy, if you have good grip on data handling and business drivers of the particular activity. Once you prepare the dashboard, it can be used for a long time until that particular process get huge changes in the dimension of the data. It is going to be dashboard template for your valid information. Hence you can use the same template every time by clearing the old data and feeding new data into the dashboard template.  Excel dashboard is creation is one of major work in the MIS department

What is Excel dashboard?

Dashboard is a excel template that will have all the KPI (Key performance indicators) and CPI (Critical performance indicators) metrics along the visual graphs for all the important data points. Also raw data would be included in the dashboards by cleaning the data. Usually raw data sheets can be hided when you are circulating to the management.


Important points while building the excel dashboard

  • Template should have the home page or summary sheet which should be connected with all the display sheets as show in the below screen shot
Dashboard Home page
Dashboard Home page
  • Main sheet which is going to be circulated for management as screen shot will have all the important number which are related to KPI & CPI metrics. The same screen shot can be sent as an email copy and provide the link below for the dashboard shared location by hyperlink
  • Graphs and tables can be showcased in the rest all of the sheets for the same business drivers.
  • Main sheet related each and every KPI or CPI metric can be enlarged in the separate sheets by creating the visualized tables. If management wants look into particular business driver, they can go and check in line.
  • Based on requirement, we can write the macros also to automate the dashboard. This can be very helpful to clean the data.
  • There are many varieties of dashboards, like Accounts payable dashboard, Procure to Pay dashboards, Accounts receivable dashboard, finance related dashboard, banking related dashboard, marketing related dashboard and sales dashboard,
  • Usually dashboards have the huge data with lot of formulas. In such cases, you need to turn off the formula calculation to update the raw data in the template sheets. Else dashboard gets hang each and every time when you update the data. It keeps on calculate the formulas.
  • If it is daily dashboard, you can clear the data on daily basis or weekly basis in the sense weekly you can clear the data, similar way for month or quarter as well. In order to clear the data, it is better to write small VBA macro code as described in the above point. You can save some time to clear all the data from various sheets.
  • It would be awesome experience in creating the excel dashboard. It takes some time to create fully. Because once you start, you will keep on getting ideas to key the information in understanding manner for the management.
  • Once you create the template, it is very easy update the data into the dashboard based on your frequency either daily or weekly.

This is what all about the dashboard creation, hope you understand. And this was written based on my work experience.





Please enter your comment!
Please enter your name here