Interactive Dashboards in Excel

This is a departure from the more development-centric posts I usually write. However, I had a recent privilege to team up with a family to up-skill their digital literacy & one of the topics we decided on is Excel.

I worked as a full-time data analyst for several years. This title has somewhat been diluted in recent years as many varying roles are required to process, obtain insights and present data. Even as a DevSecOps engineer today, I still have to do that to ensure all the systems I manage are working effectively & efficiently.

Excel is in my opinion, the pioneer, and most widely used data engineering & analytics software in the "non-tech" world. The data analytics & visualisation skills learnt here can easily translated to any domain, and any software as long as we develop strong fundamentals.

This tutorial was created using Mac Excel v16.77.1. There might be some small difference in functionalities & appearances from earlier or later versions.

Outcome

The outcome of this exercise is to produce an interactive Dashboard identical to the one below. Cool, right?

Superstore Dataset

To do that, first download the USA Superstore dataset from Tableau's website. This a fictitious company with data on their product, sales and profits for us to play with.

The format of the file is in .xls . We need to change it to the latest .xlsx format for some functionalities to work. To do that:

1. Open the sample_-_superstore.xls spreadsheet, and you will see 3 tabs of sheets, Orders, Returns & People. Now copy the entire contents of the of the Orders sheet. We don't need the others.
2. Create a brand new empty Excel spreadsheet, and paste the Orders contents into it. Save it. It should be of the format xlsx by default, unless you are using a super old version of Excel.

1) Pivot Tables

Next, we will create 2 pivot tables to calculate the total profits & sales. It is a tool to easily analyse & calculate between data fields. Pivot tables & its other pivot variants are required to allow interactions between each other in a dashboard.

To do that, click Insert > Pivot Table > OK to accept the default values. It will create a new sheet with the Pivot Table.

In the new sheet, there will be a pop-up on the right to customise how the pivot table should be calculated. We only want to calculate the total sum of profits, hence, we drag the Profit field to Values . It will automatically assume it to be the sum.

Now repeat the exact same steps to get the sum of sales, using the Sales field.

2) Pivot Chart (Line)

Now it is time to create pivot charts for the line charts showing the monthly trends of profit & sales. Go to Insert > Pivot Chart at the Orders sheet.

Similar to Pivot Tables, let's select the fields required. Drag the Sales field under Values, and the Order Date under Axis. You may see that Year & Quarter fields are automatically added since Excel recognises the Order Date to be of the date format. Remove them, and a bar chart with the monthly sales should be shown as below.

Now let's convert it to a line chart. Click on the chart, & then go to Design > Change Chart Type > Line > select the line chart.

Now label this Pivot Chart sheet as Monthly Sales.

Now repeat the exact same steps to get the line chart for monthly profits.

3) Pivot Chart (Bar)

Now let's create two bar charts for the profit and sales per product segments with Pivot Charts. The axis field should now be segment & the values fieldprofit .

We want the bar chart to be positioned horizontally, so let's change the chart type.

Last, label the new sheet as Profit by Segment to keep things neat.

Now repeat the same steps to get the bar chart for sales by segment.

4) Map

There is no such term as a "Pivot Map". An alternative to that is to first create a Pivot Table with the row using States field & values using Profit , and then link data to themap.

In that sheet, while selecting on a blank cell, we then create an empty map at Insert > Maps > Filled Map.

With the map selected, go to Chart Design > Select Data > Chart data range > select the two column of cells containing the US state names & their corresponding profits. The map visualisation will automatically recognise the state names & populate the map accordingly.

Now, rename this sheet as Map.

5) Centralising to a Dashboard

Now is when we consolidate everything together into a single dashboard.

1. Create a new sheet called Dashboard.
2. For the 2 sheets containing the pivot tables, cut & paste both the title & sum values for each sheet into the new Dashboard sheet.
3. For the 5 sheets containing the pivot charts & map, cut & paste them from each sheet into the new Dashboard sheet.

6) Slicers

Slicers are objects that filters the charts & values we just created in the dashboard. This is the key for interaction. There are two key components to this:

1. Create the slicer for a pivot table to filter a specific field
2. Connect the slicer to the other pivot tables

Select a pivot table or chart. Under Pivot Table (Chart) Analyse > Insert Slicer > check Region, Year and Ship Mode fields.

Now, select a slicer, and click Report Connections. Here, check all the Pivot Tables listed there. Note that all your pivot charts & maps are also connected via a pivot table, so this will link them together.

A side note for slicers is that they will sometimes modify the cell widths dynamically. To overcome that, select the pivot table for the sum of profits & sales > Options > uncheck Auto-fit column widths on update.

So that's it! You have an interactive dashboard! Try clicking on the slicers' filters to see how all the charts and values change together.

7) Formatting

This section is quite long with a focus on the aesthetics & readability of the dashboard. However, this is one of the most important skill to master if we want it to capture the attention of the audience.

Now let's make the map colours more intuitive, as there are states with negative profit numbers.

1. Double click on the map & a panel Format Data Series will appear on the right. Click on the Series Option icon tab.
2. For Gradient style, select Diverging (3-colour)
3. For Minimum, change the colour to red
4. For Midpoint, change it to Number, of 0 value, and colour white

Now let's move to the sum of profit & sales. We do not want the title, but there is no way to remove a pivot table title, so we replace it with a dash - . The font size is then increased, and the number format is converted to currency, without decimals.

The title is added at the side as a vertical text. This can be done by right clicking the cell > Format Cells > Alignment tab > dragging the text alignment or input the Degrees to make them vertical. The colour of the text are then changed to green.

Repeat the steps for the sum of sales value but using the colour blue.

For the line chart of monthly profit, we just want a simple trend line without the titles, axes and legend (aka spark-line). Click on each of the said component & delete them. Remove the chart border by select the outer chart area & selecting No line as shown below. We also want to change the line colour, which can be done similarly but this time selecting the chart line & changing the line colour.

For the monthly sales chart, we do the same cleanup, but we leave the x-axis labels. The axis is formatted by removing axis line & rotating the labels.

The last visualisations are the bar charts. We do the usual cleaning by removing unnecessary chart components. Then, the bar width is increased by modifying the Gap Width.

Next, add the data labels within each bar. You can also modify the number format and font size after that. As with the line chart, remove the border, and change the bar colour.

We are almost done! Now adjust each of the chart sizes & titles. The only eyesores now are the gridlines.

This can be easily removed at View > uncheck Gridlines.

Summary

This is a long post with lots of steps. However, we just need to grasp a few main concepts and it should not be difficult to develop your own interactive dashboard in Excel.

1. A dashboard is made out of slicers, pivot tables, pivot charts, or other visualisations like maps within a single sheet.
2. All visualisations must be connected to a pivot table for slicers to work.
3. Slicers provide interactive capabilities by connecting to pivot tables & filtering all their values at one go.

Links

• Superstore Dataset from Tableau: https://public.tableau.com/app/learn/sample-data

Tags: Dashboard Data Science Data Visualisation Excel Programming

Comment