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 user-friendly templates. 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 Build Dashboard Layout using Grouped Shapes
- 5 How to Design a Custom Dashboard UI?
- 6 Plan a Dashboard layout: UI Prototyping Meets Excel Shapes
- 7 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 dashboard fundamentals!
Part 1 – The relationship between Data and User
- Start with users, not the data. The best way to start is to understand the objectives and motivations of end-users. If you can realize this for sure, you will create a dashboard in Excel that is most useful for them. What is this all mean in practice? Go and talk to the users! Keep calm! Sit down. Try to understand the scope. Create a dashboard that is not in constant need of updates! Because of this, you can dramatically cut development costs.
- Don’t flood the user with unwanted information. You should seek that the dashboard is useful for them. You can even create such custom views. Custom views and filtering data enable to display relevant information only for that user.
- Provide an overview and allow users to check the details. A well thought out, and well-planned dashboard should be like a quality newspaper. On the front page provides a clear overview of the key information and leading news there are within. If one wants to look at the information in detail must know where to navigate. Allow users to see the essential information.
- Use visualizations and create a clean, uncluttered dashboard in Excel. Charting possibilities are almost endless. That’s where data visualization comes into play. Use the traditional chart types if you want; however, based on our experiences, most useful to use a gauge, bullet, and variance charts also.
- Think about what is going to be most impressive at telling our story. Dynamic charts are better than pie charts. Avoid over the top visualizations and graphics. Exploding pie charts and stacked bar charts are bad ideas too. Remove unnecessary components from the dashboard. They distract the attention from the essence.
- Create a clean structure and easy navigation. Create a detailed and well thought out menu structure. Your goal is that the users be able to orientate easily about the information most relevant to them. There are several Excel tricks that exist for making professional menus. Try to organize the indicators into the appropriate hierarchy. So, the users will always know which level they are on in the structure.
- Provide help and guidance to improve user experience. Most ideal if the dashboard doesn’t need any additional explanation. However, what if a new colleague arrives at the company and right away has to overtake the tasks of an experienced business analyst. What if you seldom use an indicator?
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.
Build Dashboard Layout using Grouped Shapes
Because our goal is to create a sales spreadsheet, the task of the Business Analyst is to meet and coordinate with the manager of the given organizational unit or company.
After this, we can start the actual work! There are a wide variety of tools at our service to make the above showed wireframe or similar designs.
Now that we have explained the role of the mockups in the design, it is time to talk about the realization. We should know about the mockups that they’ve grouped vector objects, namely grouped shapes.
We can create all kinds of dashboard UI components! The limit is set only by your imagination. In other words, in this chapter, we’ll show you how to make complex shape based layouts that allow divers usage.
In addition, don’t forget: It is a lot easier to modify a mockup than an already finished spreadsheet!
The utterly brave can use paper and pencil, but we prefer Office (Word, PowerPoint, or Excel).
How to Design a Custom Dashboard UI?
Step 1: Insert a shape
Go to Insert Tab and select Shapes from the drop-down menu. Click on your favorite object. How to format the shape? Select the shape and use the Format menu to customize your object.
Step 2: Align Shapes
You want to organize the shapes, but it seems a little bit hard task to move them one by one! What should we do? A nice built-in feature allows us to distribute and align shapes in a few clicks.
Check the initial structure below:
Hold the Control button and select the the menu elements. Now Go to Format tab and choose the following path: Arrange, Align, then Align Bottom. We’ll able to align the shapes in the right direction.
Choose the Format Tab again. Select the Align and choose to Distribute Horizontally command from the drop-down list.
In this example, we’ll distribute the shapes Horizontally. It’s a smart feature because this command provides the distance between the objects that are equally distributed.
Step 3: Send objects to the front or back
Each vector object is drawn on its own unique layer. You can move it on the top or bottom of other shapes. In this example, we want an object to be over another shape.
As first, select the shape. After that, use the Format tab of the ribbon. Finally, right-click and in the Arrange group, click Bring to Front. Instead of ‘Bring to front’ command, you can also use the ‘Send to Back’ tools to the proper arrangement.
The transformation is complete.
Step 4: Group or Ungroup shapes
Hold the Ctrl button and select all shapes. Right-click, then choose Group. From now all formatting styles will be applied for all elements.
Step 5: Create and save the final layout
Insert, modify, format, arrange, and group your elements to finalize your layout.
When it’s done, we recommend saving the wireframe into your reusable library.
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.