Learn how to design a dashboard in Excel, and create visually effective spreadsheets and layouts, clean menu structures using mockups and UI (user interface) elements. You can apply every displayed visual component if you are creating your presentations.
We’ll show you how to design dashboards in Excel. Bring your idea to life by using a shape tool. A wireframe is a detailed visual model of an application’s UI that is like a blueprint of content and functionality.
Table of Contents
- 1 Dashboard Design: Best Practices, Tips and Tricks
- 2 Tips for Better Dashboard Design
- 3 Dashboard Developer Tools – Layouts and Mockups
- 4 Plan a Dashboard layout: UI Prototyping Meets Excel Shapes
- 5 Effective Dashboard Design using Grid layouts
Dashboard Design: Best Practices, Tips and Tricks
Before you start to create a dashboard, one of the most critical tasks is to plan the ideal structure. It’s not as easy as we might think. We want to help you with the planning phase also.
Here are the ground rules.
Keep in mind these fundamentals!
Tips for Better Dashboard Design
Must-have built-in Tools
The drop-down list is a space-saving solution that is a great value when we create one-page dashboards. You can use data validation to control the type of data or the values that users type into a cell. The most popular data validation uses to create a drop-down list. To build the list of options is to type them on a worksheet. We can do this method on the sheet that will have the drop-down lists, or on a different worksheet.
Conditional formatting is the best choice if you want to highlight cells based on any condition or rule. Of course, you can use other methods besides colors. You can achieve spectacular results using icons, bars, shapes, color scales, indicators, and ratings.
Data Validation: We might want to restrict what users can write in a 1-1 cell. Why is this good for us? Just imagine that ten users in 10 different workbooks write phone numbers. If we do not restrict the format of the phone numbers with the help of data validation when summarizing the workbooks, there might be mistakes. For example, we found not allowed characters that we have to correct manually. If we set a rule that users must use that restricted format. Otherwise, they get an error message.
Data Entry using userform and VBA: Manually, data input data always carries the possibility of error. Instead, use the userform and write a short macro for it! With some VBA support, we can create a user-friendly panel that is easy to customize. Active report elements like form controls or pivot table slicers suggest playing with the chart. We recommend using form controls and automated data entry using VBA to create interactive dashboards.
Pivot tables are the most potent weapon in Excel when we are working with large data sets. It is easy to use with only a few clicks we can summarize data, and we can drill-down the data into any chosen structure.
Named ranges: We like this a lot because, in a matter of seconds, we can call a range with any given name. Just highlight a data range, and in the name box we write the chosen name: for example: ‘sales’. Form this point we can manage the formulas efficiently.
Dynamic Charts and Shapes
Create a hyperlink rollover effect for a better dashboard design! We need a simple macro to change the series based on where the user pointed the mouse. For example, the hyperlink cells have a formula that uses hyperlink and the rollover action built-in macro.
Conditionally formatted dynamic shapes can improve the visual quality of your dashboard!
Charts and Widgets help us to create visually effective dynamic dashboards.
Sparklines are a tiny graph in a worksheet cell that provides a visual representation of your data set. For example, you can use sparklines to show trends in a series of values. Another useful thing: you can highlight maximum and minimum values easily. So, the greatest impacts of sparklines: you can position the chart near its data source.
Dynamic Charts: These days, to use dynamic charts is essential if we want to create interactive charts to refresh upon the user’s choice to use this solution automatically.
Shapes: With the use of vector objects, we can create unique buttons and menus. Most often applied when navigating between worksheets. Good charts and vector elements – when used correctly – can boost your presentation. The opposite is also true: when used wrong, it’ll break things. We’ll explain why excel tools are so important and how you can use them properly.
Dashboard Design Tips – Choose the right chart type!
Let’s see some useful tips regarding the charts. In the last years, charting has been radically changed. For example, the column chart is not able to visualize complex information. We will examine what kind of graphs we should use in the function of a given project.
Compare Values: Their mutual characteristic is that they merely show high or low values. Recommended types for charting are a column, mekko, bar, line, and bullet charts. Like data visualization? Check how the radial bar chart work.
Composition: How can we portray the different sales achievements in different regions? The most appropriate charts are Pie, Stacked Bar, Mekko, Stacked Column, Area, and Waterfall charts.
Analyzing Trends: If we would like to portray the achievement of an examined data set (for example, product) in a given period, then using the following charts: Line, Dual-Axis Line, and Column.
Relationships between variables: With the use of Plot, Bubble, and Line charts, we can show how one variable relates to the others.
Sales Process Tracking: Need to track the sales process or the conversion rate? We recommend using the sales funnel chart.
Show the differences between budget and actual values: the best choice using the variance charts.
We’ll use traffic signals as a status for actual KPI value. If the green traffic light is active that the performance is great, the red light indicates the weak performance.
Dashboard Developer Tools – Layouts and Mockups
A dashboard layout prototype exactly demonstrates the final design for the given department or the management. It is very important to make the wireframe design before we invest considerable time and money to realize the report when we build a good dashboard UI prototype that helps to reach our goals.
The mockup that we create only a visual complement. Nevertheless, of course, we think its creation is important. Why? It is a lot less spent time and expense to create a layout than rebuild the structure after it is already completed.
With its help, we can effectively support the planning phase. We’d like to underline here the importance of this step. Think of it as if you laying the foundation of a house.
Let’s stop here for a moment! The planning phase does not substitute an exact specification but complements it. The wireframe will not describe how the completed framework should work! This is the task of the specification phase.
Within the frame of this, we set the user needs.
Explanation: Let us review together with the effect of the dashboard UI prototyping. Before we start working with shapes, we should exactly know what kind of result we want to see at the end. What kind of requirements the final presentation has to answer?
In the figure below we show an example of a typical layout.
Plan a Dashboard layout: UI Prototyping Meets Excel Shapes
Here’s a little surprise for you! We make available a downloadable pack free of charge for those whose daily task includes dashboard design.
We have divided the pack into two sections:
You can download the most known chart types and the newest generation charts also. In the picture below, we only show a fragment of the type of charts we have made the mockups of. We have built every type of chart that is supported by Excel, and it would take a really long time to list all of them, so we have highlighted / underlined the most important ones.
Form Control Mockups, Tables and Headers
Form controls provide interactivity to the spreadsheets. We thought we make form control mockups with the help of shapes. With this, we can greatly support the dashboard layout design.
Tables, headers, and buttons are indispensable additions to layouts. In the figure below, you can see some useful shapes. This section contains those controls by which a static dashboard becomes dynamical.
- The Label is a text field where we can put descriptions and short instructions for the user can quickly and clearly see how things work.
- With the help of the Group Box square, we can switch very easily between groups, names, and lists.
- The name of the Button itself is talkative: if the user intervenes, namely pushes the button, a short task, or even a VBA macro will startup.
- Use check box if you want to apply multiple selections so practically we can give complex conditions.
Effective Dashboard Design using Grid layouts
Above all, let us see this typical grid layout below! A common practice that an organization or organizational unit uses consistent schemes. This means that the information flow between departments provided by the use of already defined structures or schemes.
Besides the professional looks, the co-workers of the company can also interpret the information in the simplest manner.
Apply Corporate color schemes and typography
After the department has decided on the type of grid dashboard layout they will use, then the color scheme and font type can be assigned to the report. Make a template that contains the following style, color codes, and font types.
Key benefits of implementing flat color schemes:
- It draws the eye to details
- Information on the main sheet is more easily readable
- Simplified the menu structure
- Flat color schemes and typography is attention-grabbing
Conclusion: Create proper Dashboard Design and save your time!
Let’s shortly summarize the introduced knowledge about UI prototyping and dashboard layout design. The making of a reusable mockup, on the one hand considerably decreases the time consumption of development.
Finally, on the other hand, it provides contingency to work with the principals of corporate identity in mind.