Create a Sleek and Interactive Business Dashboard in Excel [PART 2]

Welcome to Part 2 of creating a sleek and interactive business dashboard in Microsoft Excel!

Before we begin, we recommend you to read Part 1 if you’re learning for the first time. This article is Part 2 of creating a sleek and interactive business dashboard using the data charts we created in Part 1.

Read here: Creating a Sleek and Interactive Business Dashboard [PART 1]

In the second part, we will elucidate on transferring the data charts onto the dashboard, make an interactive and responsive dashboard, and ultimately give it a sleek and gorgeous look! Let’s begin now!

Steps to create a stunning business dashboard

We will create the dashboard in the same workbook where we have the data charts and metrics prepared. Follow the steps to begin.

1. Inserting a background image

Create a new worksheet.
Rename it as Dashboard or anything you like.
Go to the Page Layout tab.
Uncheck View under Gridlines.
Click Background in the same tab.
Choose a picture to add to the background of the dashboard.

2. Adding a beautiful header text

Go to the Insert tab.
Pull down on Text.
Click on Text Box.
Draw a box in the heading area of the dashboard.
You can name it Performance Dashboard, Sales Dashboard, anything you like.

Select the text in the text box.
Go to the Home tab and choose a light font for the header.
Segoe UI Light and Helvetica Light (Mac) are best for sleek headers.
Set the font size between 40 to 48 points.
Right-click on the text box.
Click Format Shape.
Open Fill and select No fill.
Open Line and select No line.
Set the text color as White in the home tab.

The main header is ready!

If you want to add a sub-heading, shift the main header upwards. In the sub-heading, you can add your company’s name here.

Insert a thin line between the main header and the sub-header.

Go to the Insert tab.
Pull down on Illustrations.
Pull down on Shapes.
Select a line and draw it beneath the main heading.
Right-click on the line and click Format Shape.
Set the color to white.
Go to the Size and property tab in the windowpane.
Set the height as 0 for a straight line.

Now, copy and paste the main heading again and change the name to your company’s name.
Set the font size to 28 points or lesser if you like.
Make it a white color.

The heading of the dashboard is ready! We changed the main heading color to Blue-Gray. Looks better now!

3. Adding tiles and elements to the dashboard

Now, we are going to add tiles to display the metrics on them and few more elements. Let’s learn how!

Go to the Insert tab.
Pull down on Illustrations.
Pull down on Shapes.
Select a rectangle and draw on the left side of the dashboard.
Right-click on the shape and click Format Shape.
Give a color matching the theme under Fill.
If you want to create a dark theme, choose dark colors like black, dark blue, or dark purple.
For a light theme goes for bright colors like red, yellow, white, or light grey.
Set the transparency to 60%.
Under the Line tab, click no line.

We have 5 data charts prepared so we will have 5 tiles in total.

Make a copy of the shape by clicking on the chart.
Press CTRL+C once to copy and CTRL+V to paste the shape any number of times.
Resize each tile as per your will. You can change the sizes later as well.
Arrange the tiles neatly.

Take a look at the arranged and resized tiles. This arrangement will change as the dashboard develops.

To move all tiles together, press and hold CTRL and select tiles one by one.
Release the key and move the tiles.

4. Adding titles to the tiles

To add titles to every tile, follow the steps below.

Don’t create a new text box. Just copy the company name text box and paste it on every tile.
Select all text boxes by pressing and holding CTRL.
Set 20 as the font size.
Rename each tile as your data chart. Here it is Sales Trend, Sales by Employee, Sales by Region, etc.

The tiles are renamed properly now.

5. Inserting icons for tile titles

To make the dashboard look attractive, we will insert icons beside the tile titles. Here’s how you can do it.

Go to the Insert tab.
Pull down on Illustrations.
Click on Icons.
Choose an icon from the window for each tile title.

Click and drag on an icon to place it beside a title.
Click on an icon and go to the Graphics Format tab.
Change the color of the icon from the Graphics Fill option.

Take a look at the icons in the dashboard. They’re looking elegant!

The basic structure and elements of the dashboard are ready.

6. Adding the data charts to the dashboard

Now, it’s finally time to add the data charts and metrics to the dashboard. Let’s begin!

Start with the first chart that is the Sales Trendline chart in a different sheet.

Go to the Sales Trendline sheet.
Copy the trendline by pressing CTRL+C.
Paste (CTRL+V) in the Dashboard sheet.
Resize the tiles as per the chart sizes.
Place the chart in the Sales Trendline tile.

Double-click on the border of the chart area to open Format Chart Area Options.
Under Fill, click on No fill.
Under Border, click No line.
Click on the trendline and change the line color under the Line sub-tab.
Change the marker fill and border color under the Marker sub-tab.
Scroll to the bottom of the section and check Smoothed Line. The lines will become smoothly curvy.
Hide the title and legends by clicking the plus icon on the chart.
Uncheck every element and check Axes only.
Select the axes by holding CTRL and change the font color to white.
To hide the grey field buttons from the chart, right-click on a button and press Hide all value field buttons on the chart.

The first tile is completed and ready. Take a look at that!

The tile looks sleek and elegant.

Repeat the same steps for the other charts after pasting them in the dashboard.

7. Adjusting appropriate colors to the charts

To adjust the colors in the colorful bar charts, follow these steps. You can follow these general guidelines while making any media creatives or charts.

Click on a bar, and change its fill color.
Go for color combinations based on your theme.
Use dull and dark shades of color for the bars or any graph.
Use two- or three-color shades only, but not more than three.
Never use bright or highlighting colors in a dark-themed dashboard.
Use cool colors for a light theme like yellow and light-yellow, baby pink and light red, light grey, etc.
Maintain the color combinations and eye comfort in the entire dashboard. Never mix different color combos in one place.

Make sure your chart doesn’t look flashy or colorful, be it any theme.

Notice this portion of the dashboard. All colors are in combination, and the tiles look proportionate throughout.

Take a look at the final design with all charts arranged and formatted appropriately!

The next and the most crucial step is to make the dashboard interactive using filters and slicers in the charts. The designing part is done and completed, which brings us to the end of PART 2!

Create a Sleek and Interactive Business Dashboard in Excel [PART 3]

Conclusion

This article is a comprehensive how-to guide to create beautiful and responsive dashboards for your organization.

In Part 1, we learned to create metric charts from a database represented by the map, bar, doughnut charts, and more using pivot tables, pivot charts, maps charts, etc.

In Part 2, the most exciting part of this guide, we learned to create a stunning, sleek, and systematic design for the dashboard using the data charts we prepared in Part 1.

Part 3 is all about adding filters and slicers to make the business dashboard interactive with the users!