Designing a Dashboard in Excel
Designing a dashboard in Excel is fairly easy and simple. You can design quickly and share it with your team members.
Excel Dashboards are Flexible, and have many options for graph charts. It can be stored in a central location (like SharePoint Site) and accessed by anyone in the company intranet.
Dashboard Requirements
Initially we have to collect requirements from the stakeholders who need this report. The time spent in collecting requirements from the interested parties, will be very useful in during project build process.
You should not think it’s a waste of time to check with all stakeholders. Always get the requirements in writing. If you are taking notes when collecting the requirement, send an email to all the party involved mentioning the requirements that you received.
This is to confirm what they are expecting from the Dashboard project. This will help in avoiding Scope Creep also.
Design of the Dashboard
It’s better to draw a sketch in a paper and show it to the stakeholders, to give an idea what you are planning to produce and deliver. You can create a Proof Of Concept (POC) with sample data to them.
Indicate what type of report you are producing.
What type of Metrics are provided in the reports.
What data are brought into the report.
Design consideration
Where is the source data?
How to bring the source data to the Dashboard?
How to link the data to the dashboard on an ongoing basis?
How the data feeds into the Dashboard charts and tables in the Dashboard?
How the Dashboard is designed?
Some common graphs include, RAG reports, Pie charts, Bar charts.
Maintenance of the Dashboard Charts and Reports
The data that you are bringing to feed the chart have to be linked to get the live data.
You have to identify which linking method to use. You can use ODBC to get data from external databases like Access, Oracle, SQL Server etc. The ODBC is a driver that uses the Open Database Connectivity interface by Microsoft.
After ODBC connection is established the Dashboard gets the latest data from the database. The data are refreshed when opening the report.
The following are some of the commonly used types of Graphical Charts in Excel
| CHART TYPES |
| Column or Bar Chart |
| Line Chart |
| Pie Chart |
| Histogram |
| Tree Map |
| Sunburst |
| Box and Whisker |
| Scatter |
| Bubble |
Data Import
The import functionality will bring the data from external source to Excel. The external data source can be a flat file or database. Internal file structure to store the imported data can be a flat file or a pivot table.
Pivot table: These tables are different from flat files. Pivot tables are used for data processing. You can group data, count them, calculate the total of all the cells, find the average of all the values in the cells, summarize the data, sort the data, filter the data etc.,
They are used for summarizing large quantities of data. You can find unique values from a large set of data.
You can transpose the columns into rows and vice versa.
How to create Pivot Tables in Excel?
- Open an excel sheet
- Copy the source data table in the excel sheet
- Select one cell in the table that you copied.
- Go to the top part of Excel which is called a Ribbon,
- In the Ribbon, search for Insert tab and click it.
- In the Ribbon, you will see ‘Pivot Table’ and select it.
- TheIn the Tables group, click Recommended PivotTables.
- In the Recommended PivotTables window, scroll down the list, to see the suggested layouts. …
- Click on the layout that you want to use, then click OK.
