Do you plan on turning your raw data into an interactive dashboard using MS Excel? This is for you. In this episode of our data digest, we will be discussing how to design an interactive dashboard using Microsoft Excel.

If you prefer videos to written articles, below is the video, else you can follow the step-by-step tutorial that comes after the video.

Instructor: Anthonia Omeiza (LinkedIn)

What is Microsoft Excel?

Microsoft Excel is a spreadsheet software developed by Microsoft which is used for calculation, creating charts, analyzing data, and so on. It is a common tool that is used by business analysts and data analysts.

Microsoft Excel has long been used for making analyses and also for data visualization. Let’s take a look at how to create an interactive dashboard with this awesome software.

How do you describe a dashboard?

A dashboard is a tool that provides the visual display of the KPIs and metrics from a dataset to the users. It assists the technical and non-technical members of the team to understand the development that the company is going through within a specified moment.

How do you identify an interactive dashboard?

A dashboard is said to be interactive when it allows the data to tell a story using slices of visualization. This implies that users can visualize the data in several formats using the available slicers that are attached to the dashboard. With this, you can change the parameters accordingly and have your chart responsive immediately.

This tutorial will enable you to create an interactive dashboard using Microsoft Excel. You should be able to tell a story with these two types of visuals:

1. A column chart that compares different universities based on a certain criterion.
2. A pie chart that compares the number of students in each university across all faculties.

Quick note: You need to use Microsoft 365 to carry out these activities.

Creating a column chart with a slicer

To start with, let’s have a look at what our final column chart will look like.

It is seen that the metrics are of two types, one forms the column while the other forms the rows.

Creating the first category selector

We need five (5) selectors to form the slicer as shown in the image above on the top left-hand side. The selector will allow one to select five (5) different institutions.

• Create a new sheet and rename it to dashboard. Press the Shift key and hold on F11, once the sheet is created right-click on the sheet and select rename to allow you to rename it.
• In the dataset, there is a sheet named options. Highlight, copy and paste the first three columns (Institution, Options, and Faculties) somewhere in dashboard. You can view the highlighted columns on the page below

highlighted columns

• Click on the Data tab and select Data validation from the Data Tools wizard.

• Choose List as the validation criteria and drag down the whole Institution column to be included in the Source: box. Ensure to click on OK afterward.

• With this, a dropdown box will be shown on the dashboard table. This dropdown will contain all the universities in the Institution column.
• Copy the cell and paste it into four (4) other cells below it. Each of the five (5) cells has a dropdown list from which we can select the university of our choice.

Creating the second category slicer

• Similar to the steps taken to create the first category, repeat the same process but in this case drag down the Options column instead. This slicer is concerned with all the different types of rankings that are used as metrics by each institution.

• A dropdown box will be shown on the dashboard sheet. After this we can rearrange the dropdown boxes and have it this way as shown:

Note: All of these cells are dropdown boxes that will allow you to select any other value as you wish.

Populating the cells below the second category slicer

• To achieve this we will make use of two excel functions: Filter and Index-Match function
• Copy from the "Ranking data" sheet the first cells of column C to column M (column C has the Institution while columns D to M contain the metric that is filled in the second slicer).
• Paste it somewhere on the dashboard sheet.
• Using the filter function with the formula as shown:

Note:

The formula above indicates that the range of values from the Ranking data sheet used is from cell A2 to cell M122 written as 'Ranking data'!$A$2:$M$122 (comprising of the institution and the ranking types.)

The dollar sign here indicates the values are absolute values that are subjected to no change. 'Ranking data'!C2:C122 indicates that the data is filtered by column C which is the institution*.*

dashboard!$E$3 indicates that the result, according to the cell of the selector that is chosen, should be displayed otherwise none should be displayed.

• Afterward, use the index-match function with the formula:

• Insert the above formula into the cells that are below the second metric selector.
• INDEX() returns the value of the cell in the table based on the column and row number while the MATCH() returns the position of a cell in a row or column.

Labeling the two slicer categories

• Type Select metric: into a cell before the second category slicer and Select 5 Institutions right on top of the first category slicer. To do this, you may need to adjust the position of the two slicers accordingly to accommodate the titles.

Inserting the chart

• Highlight the two slicers categories and go to the Insert tab.
• Select the appropriate chart i.e. 2D-Column.

Formatting the chart

💡 For any kind of formatting, click on the chart and see the format options that come up on the right-hand side

To edit the Chart Title:

• Click on the chart and select Select Data…
• Edit the Chart data range, Name, Y values, and Horizontal axis labels to the respective values as shown in the image below.

(This will ensure that the Chart title changes accordingly as any new metric is selected)

To get rid of the gridlines:

• Go to the View tab and uncheck Gridlines in the Show wizard

The whole page will become blank without any gridlines making it conformable for a dashboard.

To reposition the data labels

To have the data labels present on the left-hand side of the column chart inserted into the bars:

• Click on the chart and select Format.
• On the right-hand side, click on Series,
• Then click on the Data Labels button.
• Select Inside End in the Label Position.

With this, our chart is ready along with the slicer!

You can as well carry out other formatting styles on the chart as you deem fit. It could be font type, shadow, color, background, and so on.

Creating a pie chart with a slicer

The aim of this visual is to compare the number of students in each university across all faculties.

Let’s have a look at the expected result of the chart.

Creating the first category slicer.

We need eleven (11) selectors to form the slicer as shown in the image above on the top left-hand side. The selector will allow the selection and comparison of 11 faculties at a go.

• Click on the Data tab and select Data validation from the Data Tools wizard.

• Choose List as the validation criteria.
• Drag down the whole Faculties column to be included in the Source: box.
• Ensure to click on OK afterward.

• With this, a dropdown box will be shown on the dashboard table. This dropdown will contain all the faculties in the Faculties column.

• Copy and paste the cell into nine (9) other cells below it. Each of the nine (9) cells has a dropdown list from which we can select the faculty of our choice with the last row named Other

Creating the second category slicer

• Similar to the first category slicer, repeat the same process but in this case drag down the Institutions column instead. This slicer is concerned with all the different universities

• A dropdown box will be shown on the dashboard sheet. After this we can rearrange the dropdown boxes and have it this way as shown:

Populating the cells below the second category slicer

The formula above indicates that the range of values from the Faculty data sheet used is from cell A3 to cell K123 written as 'Faculty data'!$A$3:$M$123 (comprising of the institution and the faculties.)

The dollar sign indicates the values are absolute values that are subject to no change.

'Faculty data'!A3:A123 indicates that the data is filtered by the column A which is the institution*.*

dashboard!$C$21 indicates that the result according to the cell of the selector that is chosen should be displayed otherwise none should be displayed.

• Afterward, use the index-match function with the formula:

• Insert the above formula into the cells that are below the second metric selector.
• INDEX() returns the value of the cell in the table based on the column and row number while the MATCH() returns the position of a cell in a row or column.

Inserting the Pie chart

• Highlight the two slicers categories
• Go to the Insert tab.
• Select the appropriate chart i.e. pie chart
• The final result is shown below

We do hope that you found this episode of the Data digest series exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thanks for learning with us!