How to Create Interactive Dashboard in Excel | 5 Steps to Create Interactive Dashboard in Excel

November 21, 2024April 23, 2023 by Insightoriel

Dashboard creation is a demanding skill in the edge of data science. Interactive Dashboards are required to present a data set in the form of summary or report to users. We are encircled by various dashboards in our daily life or at workplaces. Dashboards are integrated part of smart phones, laptops, and smart watches. Moreover, terms like user interfaces (UI) and user experience (UX) which are linked with the concept of dashboard. Dashboard become more powerful when you can interact with it and get the answer to your questions. In this article, we’ll discuss the basic of a dashboard. Also, steps to create interactive dashboard in excel.

Table of Contents

Toggle

What is a dashboard?

Dashboard is a summary of all related information briefly. Dashboard can be in the form of visual graphs or in tabular format. If I explain you with the help of sales dashboard as example, it contains the information like time series sales trend report, contribution of major products or product categories, geography wise sales report, total profit from the products and so on. A dashboard is the reflection of the need of stakeholders. So, it will contain the information as per the need of users.

What is the meaning of interactive dashboard?

Interactive dashboards are consisting of filters and slicers which enable you to do various slicing and dicing of the summary format so that a specific question can be answered. Though with multiple slicers, you can get answer to the question, it is called as interactive dashboard.

Let’s say, by default a dashboard is showing total sales of an area. You want to know the sales of a specific products from that area. So, in that case, product filter needs to be used to slicing the sales based on product. In another situation, you want to know unit sales of a specific product. In that case, you want to select unit sales (if it is showing only value sales). That’s how you can interact with the dashboard.

Advantages of dashboard/ interactive dashboard

The advantages of dashboard and interactive dashboard are quite similar but interactive dashboard has some more advantages.

• Drill down into more information or granular level details: As discussed earlier with example that drill down can be done in a good dashboard if you want to see granular level information.
• Saving time to get relevant and to the point information: Though all the operation are controlled by filter, it is saving time though you are only selecting the required option from given filter.

Points to remember to create a dashboard

If you want to create a user friendly, interactive, and useful dashboard, few points to remembers.

1. Understand the need of user or stakeholder. In other words, understand the need of the dashboard
2. List down the probable questionnaire which may ask by user which should be answered by your dashboard.
3. Dashboard will show data from which hierarchy level
4. Which dashboard or BI tool you will be using
5. It should not be very cluttered
6. Using simple charts or table
7. Using proper heading for each detail. If anybody looking it for first time, he/she should not spend more time to understand it
8. Not more than two to three colour tones should be used to for better aesthetic
9. Examine the data sources which will be used to create the dashboard

What is a dashboard used for?

Dashboard is used to summarize and representing a set of data in terms of visuals and tables so that relevant insight can be derived. In today’s world, dashboard term is used frequently. As example, health tracker dashboard available in smart phone, sales dashboard in business or corporate setup, Key performance indicator or KPI dashboard etc.

What is another name of dashboard?

Dashboard is a well-known term used all the sectors. Sometime different names are being used of a dashboard project or to give a fancy and different name. These names can be considered as another name of dashboard, like summary report, console, cockpit etc.

What are different types of dashboard?

Majorly dashboards can be categorized in four types. The type is linked to the use or objective.

1. Strategic
2. Operational
3. Analytical
4. Tactical

What are 5 features of dashboard?

The most helpful and effective five features of a dashboards are below.

• Convert the data into effective information which can be consumed by user quickly
• Dashboard show the information very evidently with help of charts, graphs, or table.
• Time series trends or pattern is another significant feature of dashboard where user can visualize the variations in figures over a period.
• Dashboards are consuming limited space to show or communicate all the relevant information.
• The information can be sliced and diced by using filters to get of a specific question.

Difference between dashboard and report?

Every dashboard is a report, but every report is not dashboard. Report contains pre-defined metrics in the static form. Where dashboard is in interactive form so that user can get the required answer.

Tools to create dashboard

Dashboard creation is required a tool to convert the data into dashboard. There are multiple tools available in the market to create a dashboard like Microsoft Excel, Power BI, Tableau, Qlikview, Qlik Sense and many more. Among all other dashboard tool, excel is mostly used dashboard tool around the world.

Why excel dashboard?

The reason behind excel as mostly used dashboard are, excel is installed and used across all the computers, it is mostly used spreadsheet tool and it’s free.

How to create interactive dashboard in excel?

Will discuss how to create a dashboard in excel with the help of pivot table and required graphs.

5 steps to create interactive dashboard in excel

1. Understand the data- First step is to understand the data set in terms of columns, rows and type of columns.
2. List down the dashboard information and imagine the output- Imagine the output which you are looking for. List down the information which needs to be shown in the dashboard. Next decide, which charts, or table and filter options which will be the best fir for dashboard.

We’ll create the below dashboard with help of excel pivot table (Pivot table in excel tutorial) and graphs.

Understand the data:

Let’s first look into the data in terms of column names, what type of information are there and type of data present in each column. So, we have the details like order, order date, shipping details, customer details, geographical details, product details and sales details in the data which we are going to use. Please have a look into the data.

List down the dashboard information and imagine the output:

Let’s decide what are the information we would like to show in the dashboard and in which form. Crating a table with three columns, data to show, column name to use and chart type.

Also, following filter or slicer options will be given to the dashboard for information drilldown.

Geography filters/ slicers: Country, state, region, city.

Other filters/ slicers: Segment and ship mode.

Clean the data:

Data cleaning is an important step before any data analysis or dashboard creation. To clean the data, remove duplicates and remove null values. Outlier removal is not required for dashboard unless it is creating some issue in the visualization.

Create required pivot tables:

As we need to get the first report of monthly sales trend with sales and quantity, let’s create a pivot table in a separate sheet. Use columns of year (rows), month (rows), sales (SUM in values), and quantity (SUM in values).

Copy the pivot table and paste as special to create a line graph out of it. If we use the same pivot table to create line graph, pivot chart will be created, that is not required. Now cut the pivot table from the sheet and paste in the dashboard. Name it as Monthly sales trend.

Similarly, create next pivot table with product category and sales. Paste the table as paste special and create donut chart. Name it as Category wise sales contribution.

Repeat the process to create the 2/2 matrix of scatter plot as Sub category wise sales vs profit. In pivot table, use sub categories in rows and sales & profit in values (SUM).

Also, same techniques has been used to create top 5 products in sales and sub segment wise profit chart.

Most important in such dashboard is slicer, where pivot is the base and need to make it interactive with filter. Slicer is a very useful option in pivot table which is easier to use by user in a dashboard. Though we have multiple pivot table created from same data, first let’s add slicer from first pivot which is monthly sales trend.

Now click on the slicer and go to options tab, select report connections. Then, all the pivot table names will be visible. Select all the relevant pivot tables so that all pivot tables get linked with the slicer. Repeat the process with all the slicers.

Next step is to cut the slicer and paste those in the dashboard sheet.

To create the plates like total sales, total quantity and profit, use shapes in excel (Insert tab -> shapes). Then click on it and put the cell name in formula bar from where the data should be picked. Here Total sales is getting picked up from D1 cell in Helper sheet. Use formula so that every time the specific cell data get refreshed. In this case used grand total data from pivot table.

Format content, headers and charts:

Format, colour selection and alignment is an art. Practice more to get mastery into it. In this example, kept all the geography filters at the left and used yellow colour. The dashboard background kept black which was done by selecting entire sheet and use black colour with fill colour (Home -> Font).

Insights from the dashboard: There are few quick insights from the dashboard.

1. Technology category contributes 38% (highest) to sales.
2. Tables as sub category contributed to loss in spite of good sales.
3. Top 3 top selling products are smart phones.

More insights can be found while play with the dashboard.

Please follow and like us: