Dashboard Design in Excel

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 excel dashboard. 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.

Effective Dashboard Design – Layouts and Mockups

What is a dashboard layout?

A dashboard layout prototype exactly demonstrates the final design for the given department or the management. It is very important to make the layout 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.

What is a visual mockup?

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.

What is the purpose of a user interface?

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 complement it. The layout 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 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? On the figure below we show an example of a typical layout.

dashboard design fundamentals

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 layout 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’re grouped vector objects namely grouped shapes.

We can crate all kinds of dashboard ui components! The limit is set only by your imagination. In this chapter we’ll show you how to make complex shape based layouts that allows divers usage.

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.

insert a shape

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 few clicks. Check the initial layout below:

align shapes

Now Go to Format tab, and choose the following path: Arrange, Align then Align Bottom. We’ll able align the shapes to the right direction.

customize the positions of shapes

Choose the Format Tab again. Select the Align and choose Distribute Horizontally command from the drop-down list.

distribute the menu structure

In this example we’ll distribute the shapes Horizontally. It’s a smart feature because this command provide the distance between the objects 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 shapes.

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 use also the ‘Send to Back’ tools to proper arrangement.

report-layout-design-excel-bring-to-front-05

The transformation is complete.

report-layout-design-excel-replaced-06

Step 4: Group or Ungroup shapes

Hold the Ctrl button and select all shapes. Right click then choose Group. From now all formatting style will be applied for all elements.

report-layout-design-excel-grouped-objects-07

Step 5: Create and save final layout

Insert, modify, format, arrange and group your elements to finalize your layout. When it’s done, we recommend saving the layout 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 design.

We have divided the pack into two sections:

Chart Mockups

You can download the most known chart types and the newest generation charts also. Most notables are, the bullet chart, treemap, traffic light and gauge chart.

final mockups using various charts

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. On the figure below you can see some useful shapes.

form control mockups in Excel

Advantages of grid layouts

Below are some typical grid layouts. 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.

a great looking grid layout

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, than 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 a main sheet is more easily readable
  • Simplified the menu structure
  • Flat color schemes and typography is attention grabbing
choose the proper color scheme

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 one hand considerably decrease the time consumption of development. On the other hand, provides contingency to work with the principals of corporate identity in mind.

Additonal resources