Creating a good Excel dashboard is an objective for anyone doing data analysis. Unfortunately, as many people will discover a dashboard is not an automatic output or magic wand phenomenon, you just do not wave a dashboard into existence. A bit more thinking is needed to create a dashboard that will make sense not to just the creator but also to the audience for whom the dashboard is meant for. Excel is a lot of fun once you get the hang of how to read the “formulas”.
The most impressive dashboards are a combination of a number of tools such as Developer Tools, Pivot Tables, Pivot Charts or other formulas that may seem like they are from another planet. To create a good dashboard one needs to have a methodology in place such as the one that Eunoia Consultants recommends.
A methodology helps you to structure your approach on how you are going to approach the analysisis to get to the end game or the destination.
1. Understand the Data
It is not just data but a story that is being told. You need to be able to make heads and tales on the data that is being collected and be able to identify the fields, the field types as well as the constraints that maybe in the data.
Your data speaks volumes and you must be able to capture the story so that you can create sense out of it. Remember there is no magic wand to create a good data dashboard, there is the magic of Excel which is putting in the formulas and the rest of the story together.
2. Understand the Objective
What is it that you are looking at and what is it that you want to achieve from it. If you are looking at sales for example you want to understand how the patterns of sales are distributed with an overall objective for decision making. If one does not understand the data that they have, then normally one will get lost in a sea of analysis which does not go anyway.
3. Create Instructions
After creating the objectives comes the specific instructions of what you need to do. Consider these as simplified algorithms on what you need to do. It maybe as simple as summarizing data or grouping transactions. An example of instructions can be noted below
For our instructions we write as much as we can which will bring out an understanding of what we need to appreciate about the data. Instructions are a necessary part of the process so that you are guided on what to add and what not to add. This is not the final draft but rather a guide on what needs to be accomplished.
4. Identify Formulas and Tools
Eunoia Consultants refers to this as your formula scoreboard where you can identify the tools that you will need. This includes visual and formulas. This can include tools such as Pivot Tables besides the formulas.
You may identify more formulas as you go, as the need will demand that you learn or use more formulas. You might even discover that you do not need some of the formulas, for example you might not need VLOOKUP as XLOOKUP will do the job just fine.
5. Implement Analysis
In “analysis” this is where you implement your instructions in different sheets to see how it comes out. It will assist you in bringing your dashboard together to make sense. Your analysis is you implementing you formula scoreboard and as you go you update your scoreboard so that you can trace what intricate formulas you utilized towards the making of the dashboard.
Your analysis as you go will provide a better direction.
6. Create Final Presentation (aka Dashboard)
The second last port of call is the actual Excel Dashboard which requires organization and a bit of design intricacy. In designing your dashboard you need to be mindful of the color scheme, the arrangements and simplicity. It is not just about creating the dashboard but to make it easy for the audience to understand what it is that you are creating.
7. Review Data and Formulas
Every good report requires a revision to be made. Your master piece deserves corrections and do overs which will allow it to properly capture what is that you were making out. There maybe areas that need revision that you may not have noticed but after a few considerations you will need to make them.
