Excel is the best place to start designing a new executive report or dashboard. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined (and then send project to IT for implementation).
Once you know what will the dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating a dashboard in Excel:
- how to bring data into your Excel spreadsheets;
- how to manage the data and link it to the dashboard objects, like charts and tables;
- how to design the dashboard report.
Let’s take a look at each of them.
How to bring data into your Excel dashboard
Yes, Excel is a very flexible tool, but to create a dashboard you can’t just paste some data and add a few charts, can you? The dashboard must be maintained and updated and if you want to minimize the cost associated with that tasks you must impose some structure to your data.
Usually the data will (should) not be entered directly into the spreadsheet. This means that you must copy /paste the data from the source (not wise, really) or connect the spreadsheet to the data source. There is a standard way to communicate with external databases called ODBC that you can use to connect your dashboard to a table in Access or Oracle, for example.
Once you have that connection established, every time the data changes at the source it also changes in the spreadsheet, after refreshing. Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it. However, if there are calculations to be performed, try to perform them at the source.
Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster, while the flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must test this in your own project).