Report Layout Design in Excel

Report layout design in Excel! Learn how to 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 layouts. Bring your report idea to life by using a shape tool in Excel. A wireframe is a detailed visual model of an application’s UI that is like a blueprint of content and functionality.

Mockups and Prototyping for report layout design

A report 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 UI report prototype in Excel that helps to reach our goals.

Let’s stop here for a moment! The planning phase does not substitute an exact report specification but complement it. The layout will not describe how the completed report should work! This is the task of the specification phase. Within the frame of this we set the user needs.

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 report layout in Excel than rebuild the structure after it is already completed.

We consider the state of a finished report when we already initialized data sources and connectors and prepared queries (views or stored procedures) in SQL. UI design is a key task in Excel. With its help we can effectively support the report 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.

Explanation: Let’s review together the effect of the UI prototyping to the eventual report. 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 report layout design.

report-layout-design-excel-inline

Build your mockup using custom shapes in Excel

Because our goal is to create a sales report the task of the Business Analyst or the report designer is to meet and coordinate with the manager of the given organizational unit or company. Don’t forget! It is a lot easier to modify a mockup than an already finished Excel spreadsheet!

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 report 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.

The utterly brave can use paper and pencil but we prefer Office (Word, PowerPoint or Excel).

How to create custom report layout design in Excel?

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.

report-layout-design-excel-insert-shape-01

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:

report-layout-design-excel-initial-02

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.

report-layout-design-excel-align-03

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

report-layout-design-excel-distribute-04

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 in Excel. 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 report 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.

UI Prototyping Meets Excel Shapes – Free Downloads

Here’s a little surprise for you! We make available a downloadable pack free of charge for those whose daily task includes report 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.

report-layout-design-excel-chart-mockups

Form Control Mockups, Tables and Headers: Form controls provide interactivity to the spreadsheets or reports. We thought we make form control mockups with the help of Excel shapes. With this we can greatly support the report layout design. Tables, headers and buttons are indispensable additions to report layouts. On the figure below you can see some useful shapes.

report-layout-design-excel-form-control-mockups

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 report structures or schemes.

report-layout-design-excel-grid

Besides the professional looks the co-workers of the company can also interpret the information in the simplest manner.

Corporate color schemes and typography

After the department has decided on the type of grid 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 to your reports:

  • It draws the eye to details
  • Information on a report is more easily readable
  • Simplified the menu structure
  • Flat color schemes and typography is attention grabbing
report-layout-design-excel-typography

Conclusion: Create proper design in Excel

Let’s shortly summarize the introduced knowledge about UI prototyping and report layout design. The making of a reusable mockup in Excel on one hand considerably decrease the time consumption of report development. On the other hand, provides contingency to work with the principals of corporate identity in mind.