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
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.
- Understand the need of user or stakeholder. In other words, understand the need of the dashboard
- List down the probable questionnaire which may ask by user which should be answered by your dashboard.
- Dashboard will show data from which hierarchy level
- Which dashboard or BI tool you will be using
- It should not be very cluttered
- Using simple charts or table
- Using proper heading for each detail. If anybody looking it for first time, he/she should not spend more time to understand it
- Not more than two to three colour tones should be used to for better aesthetic
- 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.
- Strategic
- Operational
- Analytical
- 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
- Understand the data- First step is to understand the data set in terms of columns, rows and type of columns.
- 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.
- Clean the data- clean the data so that any unwanted data or missing value should not create any problem in the dashboard.
- Create required pivot tables- Pivot table will help to create summary and get the required filters/ slicers.
- Format content, headers and charts- Final stage of dashboard creation to make it meaningful and visually insightful.
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.
| Row ID | Order ID | Order Date | Month | Year | Ship Date | Ship Mode | Customer ID | Customer Name | Segment | City | State | Country | Region |
| 1 | IN-2013-27828 | 8/26/2013 | Aug | 2013 | 8/30/2013 | Standard Class | VT-21700 | Valerie Takahito | Home Office | Luoyang | Fujian | China | North Asia |
| 2 | IN-2013-27828 | 8/26/2013 | Aug | 2013 | 8/30/2013 | Standard Class | VT-21700 | Valerie Takahito | Home Office | Luoyang | Fujian | China | North Asia |
| 3 | IN-2013-27828 | 8/26/2013 | Aug | 2013 | 8/30/2013 | Standard Class | VT-21700 | Valerie Takahito | Home Office | Luoyang | Fujian | China | North Asia |
| 4 | IN-2013-27828 | 8/26/2013 | Aug | 2013 | 8/30/2013 | Standard Class | VT-21700 | Valerie Takahito | Home Office | Luoyang | Fujian | China | North Asia |
| 5 | IN-2014-63178 | 6/24/2014 | Jun | 2014 | 6/30/2014 | Standard Class | SO-20335 | Sean O’Donnell | Consumer | Marikina | National Capital | Philippines | Southeast Asia |
| 6 | IN-2011-29067 | 5/30/2011 | May | 2011 | 6/3/2011 | Second Class | ST-20530 | Shui Tom | Consumer | New Delhi | Delhi | India | Central Asia |
| Product ID | Category | Sub-Category | Product Name | Sales | Quantity | Discount | Profit |
| OFF-PA-10000026 | Office Supplies | Paper | Eaton Cards & Envelopes, 8.5 x 11 | 197.28 | 4 | 0 | 35.4 |
| FUR-FU-10000944 | Furniture | Furnishings | Tenex Door Stop, Black | 307.23 | 7 | 0 | 21.42 |
| TEC-CO-10002526 | Technology | Copiers | Sharp Wireless Fax, Digital | 1422.24 | 4 | 0 | 526.2 |
| TEC-AC-10002255 | Technology | Accessories | Memorex Keyboard, Erganomic | 207.45 | 3 | 0 | 78.75 |
| OFF-AP-10002882 | Office Supplies | Appliances | KitchenAid Coffee Grinder, Silver | 121.074 | 2 | 0.15 | 37.014 |
| OFF-BI-10000168 | Office Supplies | Binders | Cardinal Hole Reinforcements, Recycled | 11.4 | 2 | 0 | 2.82 |
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.
| Data to show | Column name to use | Chart type |
| Monthly Sales Trend | Sales & Quantity column | Line chart |
| Product category wise sales contribution | Category column | Donut chart |
| Total revenue | Sales column | Card |
| Total quantity | Quantity column | Card |
| Total Profit | Profit column | Card |
| Sub-Category wise sales vs profit | Sub-Category, sales and profit column | Scatterplot |
| Sub segment wise profit | Sub segment & profit column | Tree map |
| Top 5 product in sales | Product name & sales column | Bar chart |
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.
- Technology category contributes 38% (highest) to sales.
- Tables as sub category contributed to loss in spite of good sales.
- Top 3 top selling products are smart phones.
More insights can be found while play with the dashboard.
