Learn the fundamentals of effective dashboard design! We will explain the basic rules of data-driven storytelling. They are different users with different requirements. It’s a usual situation. Choose an effective way to tell a story using data visualization.
Remember, the goal of the dashboard is to help identify a problem or help to make the right decisions.
Dashboard Design Fundamentals
Before you start to create a dashboard in Excel, plan the structure! First, we need to establish links between data and users. After that, build a wireframe, it can help in the planning phase.
The link between Data and User
- Let users think. As first, we need to know the end-users requirements. It is under these conditions, and these conditions only will be the dashboard useful. Create a proper scope. It can decrease the timeframe and the costs of development.
- Strive for clarity and simplicity: Maximize impact, minimize noise. If it does not add value or serve a purpose, get rid of it. That’s all.
- Display alerts to grab user attention. If we would like to emphasize the changes of an indicator by any viewpoint, we can make an attention-grabbing sign for the user. This can be the classical red-yellow-green cell coloring, in other words, conditional formatting. First, we have to set a value system or pattern. After this is done when the value changes, colors will automatically also change.
- Get rid of unwanted information! No one likes to work with useless reports. It is evident that we have to focus on displaying KPIs. Remove the unnecessary objects! Furthermore, it’s worth building custom views to filter data. The filtered data contain relevant information for the end-user.
- Allow users to drill-down the data. This method serves only one purpose: we have to get an overview of the most important metrics first. But it is important to enabling users to check what is under the hood.
- Use clean data visualization. We recommend using custom charts (gauge chart, bullet chart, variance chart, conditional formatting shapes), and widgets. It may occur that we are using regular built-in chart types.
- Use a well-structured workbook. We have already mentioned the most important rule: split the data onto different sheets (preparation layer, namely dashboard, calculation, and unprocessed data). So, we definitely will not get last in the sea of numbers.
Storytelling and Dashboard Design
Using storytelling, we can present data using effective data visualization. Tell a story using data and show the main indicators on a one-page interactive dashboard.
- Scope. Using exploded pie charts are a bad idea. Furthermore, it’s important to remove unwanted components like backgrounds and chart borders. Get rid of outdated chart types and focus on the main scope.
- Focus on creating a narrative. Don’t just show the data, tell a story. Communicate key insights clearly, quickly, and powerfully. Make it simple for your readers to choose what to focus on.
- Navigation. Create clean menu structures using shapes and ribbon customization. Above all, users need the most relevant information. There are advanced tricks to build menus for the dashboard.
- Apply grid layouts and group items. If we do this, we can handle views and objects together. When the user initiates interaction, the dashboards won’t show useless empty spaces.
- KPI Structure. Try to build a logical structure for the KPIs. For example, users can view the yearly, quarterly, monthly, and daily breakdown. If we build a top-down hierarchy, users will always know which level they are on in the metrics.
- Help and support. Dashboards without built-in help are useless. Build tooltips to improve the user experience.
- Make it fit on one page. We all know that sometimes less is often more. To replicate business procedures in a clear manner, we always have to think in a one-page dashboard! Creating larger tables often brings problems. When scrolling, the names of the lines and columns cannot be seen. In this case, use freeze panes that make navigation easier.
Additional Dashboard Design Tips
- Create printable spreadsheets. We have to define Print Area beforehand, so we don’t get a surprise when wanting to print. With the proper set of layout and orientation, we can be sure that the end result will be easily transparent in an offline form.
- Avoid using 3D charts. We hope this is evident to everyone. These are hard to interpret and distort data. Just forget about them!
- Clean up and secure the final dashboard. It doesn’t matter if we use a shared drive to present the dashboard, or we send the presentation in an e-mail; we have to pursue the smallest file size. Make a backup and remove all unnecessary worksheets and raw data. If necessary, set a password to the workbook.
- Pick a proper color scheme. In most cases, the 60/30/10 rule works fine. Pick three different colors carefully. A possible combination of colors: dark blue, blue and green. Apply them in the ratio of 60:30:10. Follow this rule, you can create an attention-grabbing color scheme for your reports.
- Red and green colors. When we display the efficiency of a product with a red color. For example, on a sales column chart, we automatically think this was the least efficient product. Why? We identify the red color with the poorest achievements. But it might be the best based on the numbers. Displaying red and green on one chart is also not the best choice in the case of colorblind users. If we prefer this combination, we should use chart types that separate the colors indefinitely. These types are like the traffic light widget or gauge.