Interactive Dashboard in Excel

We all know that Excel is packed with tons of powerful features, libraries of formulas, and galleries of interesting charts, making it one of the most useful tools for business analysts.

Business analytics involves using data to make crucial strategy-level decisions. The question is how to represent that data in a way that’ll help our leadership team or decision-makers come to a consensus quickly. This is your skills as an analyst and a dashboarding specialist will shine.

In this article, I will show you how to create a dynamic and stunning dashboard in Excel. We will look at creating pivot tables, designing the dashboard layout, adding various charts to the dashboard, unifying the charts into one theme, and then adding slicers/filers that control all the charts. Here is a preview of what you will be able to build at the end of this article.

Now, that we are set and done, let’s get right into it!

Now, that we are set and done, let’s get right into it!

Step 1: Dataset for Dashboard

For our Ultimate Excel Dashboard, we will use a huge amount of transactional sample data. Here is the snapshot of the sample sales data:

Step 2: Creating Visualizations for the Dashboard

For the given sales data, different types of plotting can be performed. We will create charts to analyze:

1. KPI (Sales, Profit, Quantity, No. of Orders, Profit Margin)

2. YOY Growth

3. Sales and Profit analysis

4. Category wise Profit

5. Category-wise Sales share %

6. Sales by State

7. Top 5 Categories by Sales

Now that you have your data set prepared, let’s start creating and preparing the Pivot Tables and Pivot Charts that we need to fill our Excel dashboard with life.

Create Pivot Tables and Pivot Charts:

First will work on creating 5 charts for our dashboard and then will move towards KPI.

For every Pivot Chart, we start by creating the Pivot Table. Once the Pivot Table is set up, we can drag and drop the required data fields into the rows, columns, and values of our Pivot Table section.

• Sales and Profit analysis

For Sales and Profit analysis we need to create a pivot table with sales and profit. To create a pivot table, navigate to the Insert tab, and then the PivotTable option on the left.

This will open up a new sheet with Pivot options on the right. We need month vise sales and profit so will simply drag the sales and profit field into the values section and the field into the values section and order date in the axis (categories). You will notice order date will display year wise so remove year and quarter from axis to keep list of months only.

Change the format of sales and profit values as shown below by right click on selecting sales and profit columns.

Now, let's create a chart for this pivot table. for that click on any cell on the pivot table to activate the pivot chart and select combo chart as we want to show sales and profit both on the same chart as a dual axis.

• Category wise Profit

We are going to create a waterfall chart for the category-wise profit. First, create the pivot table by taking profit in values and category in the axis field of the pivot table and try to select the waterfall chart which will give you the below error.

The Waterfall Chart and Map Chart are 2 exceptions Chart which currently requires copying the Pivot Table Data outside of the Pivot Table and then inserting a regular chart that references the copied data.

Format the profit values into $0.00,”K” format as shown before.

• Category-wise Sales share %

We want to know which category has highest sale out of all the available ones. For this, we will plot a pie/donut chart. In the pivot table, drag “Category” in the rows section and “Sales” in values. Show sales values as grand total as shown below.

To plot the donut plot, click on the pie icon in the pivot chart options and select the donut plot at the end. Display labels by clicking plus sign on the right corner. Next, remove all the unwanted field buttons to enhance the chart visually.

• Sales by State

For every new plot, you need to create a new pivot table. Therefore, repeat the process of creating a pivot table and navigate to the new pivot table sheet each time. Now, for sales by date, a Map plot would convey more information about the actual region of the country.

Drag “State” to rows and “Sales” to values to create a pivot table. we cannot plot the Map plot directly from a Pivot table. Therefore, you need to copy the pivot table, paste it into the same sheet, and then create the reference of the values to the pivot table values. Below image will help you to understand this part.

• Top 5 Categories by Sales

We want to show the Topmost 5 categories that have the highest sales, and for that create a pivot table with “Category” on rows and “Sales” on values. Then, sort the data from largest to smallest.

Choose the top 5 categories and then select pivot chart to show top 5 categories by sales.

• KPI (Key Performance Indicator):

KPI is a measurable value that demonstrates how effectively a company is achieving key business objectives. Organizations use KPIs to evaluate their progress and success at reaching targets.

Here, we going to create 5 KPIs for our dashboard. let’s start with the first one which is Sales. You can use a Sparkline chart for KPI but here I would like to use a line chart as a sparkline chart, will show you how.

Select the line chart from the pivot chart for a pivot table with month and sales and remove everything from the chart except the line only so we can use this line chart as a sparkline chart.

Repeat the same process for the other three KPIs for Profit, Quantity, No. of Orders.

For Profit Margin KPI, we have to calculate the profit margin which is =Profit/ Sales using calculated field.

Now, all five KPIs with charts are ready to go for a dashboard.

• YOY Growth

Year over Year (YoY) growth measures the change in an annualized metric across two comparable periods, typically the current period and the prior period as of the fiscal year-end date. The formula to find YOY growth is:

Increased YoY Growth → Positive

Decreased YoY Growth → Negative

Calculate YOY Growth for all KPI fields using the above formula. To show positive YOY growth give green color with an up arrow and give red color with a down arrow for negative YOY growth.

([Green]▲0.00%;[Red] ▼0.00%)

Step 3: Raw Dashboard Design

All the required charts are created and now we are ready to design the dashboard. Follow these steps:

1. Create a new sheet in the workbook

2. Goto to page layout, and under the sheet options, uncheck the Gridlines view checkbox.

3. Next, click on Background and choose a background image. You can search for images on Google, Unsplash, or create a fresh dashboard layout in Canva.

4. Now, you can add the title and subtitles to the report by going to the insert tab and selecting the text box option at the right. You can also change the font color, text, and other text properties.

Will start with adding KPIs to the dashboard first and put them next to each other in 1st row. For each KPI, there will be total shapes you need to add which are arranged as shown below. By selecting all these 4 shapes together, you can create a group so it will be easy to move them all together.

Similarly, keeps adding all other KPIs to their respective place and arranging them properly before creating a group with all five KPIs together.

After adding the background, title text, and KPIs it’s time to create the titled view of our dashboard. Insert a rectangle, fill the gradient fill color as you want, make it around 25% transparency, and remove the border colors. These boxes/tiles will be the base for our charts. Place them in an ordered collage manner. It would look like this:

Step 4: Insert and Redesign Pivot Charts

Now, to add the charts to their respective positions:

1. Simply copy them from their pivot sheets and paste them into their tiles.

2. You will need to adjust the size according to the tile size or modify the tile size to accommodate the charts.

For example, let’s add the first chart Sales and Profit Analysis.

For every chart to be in the same theme and immersive into the tiles made:

1. You need to change the gradient color respective to the selected theme and the Border color to none. This can be done by navigating to the format tab.

2. Change all text colors to white.

3. Remove the unnecessary field buttons from charts by going to the PivotChart Analyse tab and then to the Field Buttons option to the right. Deselect all the choices there or choose the Hide all option.

Add the waterfall chart and donut chart to their respective shapes. There are a few format options available that you need to do for the donut chart like hole size, explosion, different shades of color per donut section, label formatting, etc. as shown below.

The next one is Map Chart for Sales by State. Follow the same steps to add the chart and another formatting. Select a series of colors to make it more clear visualization.

Last chart remaining before we add slicers is the Top 5 Subcategory by Sales. Select show data labels from chart elements and format them to the inside end to save space as shown below.

The dashboard with all the charts placed, given title, and customizations applied should look like this:

Step 5: Add Slicers for Interactivity

Slicers are the magic component that will make our charts interactive and cross-dimensionally filterable. We insert a slicer for every dimension that we want to filter in and connect all slicers to every chart.

To achieve this, simply create a slicer for any of the charts placed.

1. Select a chart

2. Navigate to the Insert tab

3. Click on the Slicers option in the Filters section

4. Select the desired columns/fields for which you want to filter the data

Here, I have chosen a Waterfall Chart to create slicers and chosen Year, Segment, and Region so it will give three slicers.

Add these slicers to the dashboard by simply copying and pasting.

Now it’s time to format slicers……

Now, for slicers to update other charts as well,

1. Right-click on the slicer

2. Select Report Connections

3. Select/Check all the pivot tables checkboxes

Final Dashboard in Excel

And here is our final dashboard creation……

Have another look for the dashboard as GIF to understand its interactivity.

Conclusion:

In this blog, I have explained how to create dashboards in excel. I have given screenshots with descriptions to create pivot tables, insert charts into the sheets, format options, create the layout of a dashboard, how to add all charts together on the dashboard, make the charts beautiful with correct and even theme, and how to connect all these charts to common slicers.

Thank you for taking the time to read our content. Appreciate your claps and comments.