Get the best dashboard practices! In this post, we’ll show you how to improve your Excel skills with the help of a FAQ section.
How many performance indicators are needed? You should know exactly. If the goal is to track only 5-10 KPIs, it is sensible to use a gauge chart; most companies will need much more. A gauge chart is an incredible tool, but it could be more efficient. On the other hand, the best choice is the bullet chart if the goal is to track more than 10 KPIs.
How to track and visualize budget vs. actual performance? By all means, use a variance chart, but the bullet chart is suitable as an alternative.
What should be the format of the final work? Will we have to share our information through the intranet or only share it with a few managers? We have to pay attention to set authorizations. For example, the template should always be read-only! Imagine where it would lead if somebody opened the file, modified it, and then forwarded it in an e-mail. I don’t even dare to think.
Compatibility first! Remember that different versions of Excel have different functions. Suppose you created the file using Excel 2016, and someone wants to open it with Excel 2. Nothing good will come.
Data Cleansing: Lastly, clean raw data using built-in macros and VBA solutions! It’s crucial to convert tabular format data into an Excel table. All right, how should we start?
Dashboard Practices and Fundamentals
Focus on the essence! The basis of every report is the one-page layout. Keep in mind that the user doesn’t always interested in the details. For example, a CEO is usually still busy, having just enough time to look through some KPIs. At the same time, the business analyst has to see even the most minor details.
Before you effectively start creating a dashboard, here are some valuable tips and advice:
- KISS! Could you keep it simple and sparse? Very important is that we appropriately apply this rule! Don’t jam the screen full of unusable charts.
- Apply a user-friendly interface. What we measure is not the only important thing. Representation is also essential. When you create a report, you have to think about others and understand them besides you.
- Add an alert for KPIs. Apply various tools! Some great technics are gauge charts, traffic lights, free widgets, and bullet charts.
- Use form controls
- Get real-time data from several bases. The most frequent data sources are the following: online and offline databases, OLAP cubes, pivot tables, text files, and internal and external data sources.
- The logical structure behind the scenes: most mistakes can be filtered out if we work with well-structured databases and data tables. Use three worksheets. The first one contains the raw data tables. On the second one, we make the calculations. Finally, on the third one, we display the final dashboard.
- Display actual data, forecasts, or trends.
Conclusion – Use the best practices
When an idea is born in our heads, two possible ways to go about it, we immediately get to work in the first scenario and neglect the planning part. But, of course, this never leads to nowhere. So in the second case, we carefully plan the design and the necessary steps.
What do you think is more productive in the long run? We believe the planning phase should take up 70% of the project. The remaining 30% is needed for the implementation. Mistakes are a lot less if we advance step by step. Stay tuned; we’ll append the article with the latest dashboard practices!
I am not a designer but would like to create splendid presentations. What should I do? Read on! More of our free tools support beginner users also.