Locked lesson.
About this lesson
Exercise files
Download this lesson’s exercise file.
08-01-What is an Interactive Dashboard-Start.xlsx3.8 MB
Quick reference
What is an Interactive Dashboard?
Understand what an interactive dashboard is and why they are useful.
When to use
We create an interactive dashboard whenever we want to present data and key metrics in an engaging way.
Instructions
Interactive dashboards have risen in popularity over the last few years. We use them to present key metrics and other data in an engaging and clear way.
There is no right or wrong way to create a dashboard but it is worth taking some time to think about what questions the dashboard needs to answer.
We also need to consider dashboard design. Think about the colors, branding, and what charts we need to use.
Dashboards should present results clearly and be easy to use and interpret by the end user.
What to Include on a Dashboard
What we include on the dashboard is really determined by the metrics we want to present. We might include multiple Pivot Charts. We might include formulas. We might include conditional formatting or sparklines. We can even include visual elements like pictures and icons.
It really is up to you how you design your dashboard.
Global Sales Analysis Dashboard
In this section of the course, we will be walking through the process of creating a global sales dashboard.
This dashboard will be based on one dataset in the 'Source Data' tab. The dataset holds the sales information for a 7-year period of a global megastore.
We need the following questions answered by our dashboard:
- What is the Profit by Month?
- What are the Average Sales by Country?
- What is the Total Revenue by Item Type?
- What is the Total Profit by Year and Sales Channel?
We will be creating multiple PivotTables and Pivot Charts to answer these questions.
At the top of the dashboard, we will display the important metrics.
These will be created using several formulas on the 'Calculations' sheet.
We will add slicers so we can filter our data.
We will also apply formatting to the dashboard and create a dark/neon theme to present our results.
Finally, we will see how to update the dashboard when new records are added to the source data.
- 00:04 In this section of the course,
- 00:05 we're going to be doing something really exciting.
- 00:08 We're going to build an interactive dashboard.
- 00:11 And I just wanted to start out in this lesson by showing you an example of
- 00:15 the dashboard that we're going to create, and
- 00:18 just talking a little bit about what exactly an interactive dashboard is,
- 00:22 because you might never have come across them before.
- 00:25 Now, interactive dashboards have really risen in popularity over the last
- 00:31 few years simply because most of us are working with data these days.
- 00:36 And being able to present that data effectively so that your team,
- 00:40 your managers, your CEO, stakeholders can make better business decisions.
- 00:46 And when it comes to dashboard design, the only limit is really your imagination.
- 00:52 I have to say I'm not a particularly great designer.
- 00:55 I don't have too many creative bones in my body.
- 00:57 But even with my limited creative skills,
- 01:00 I can always manage to come up with the dashboard that looks pretty decent and
- 01:04 presents my results clearly and in an engaging way.
- 01:07 So if you take a look at the dashboard that I have on the screen now,
- 01:11 this is basically what we're going to build throughout this section.
- 01:15 And I will say that this is a reasonably simple and straightforward dashboard.
- 01:19 Dashboards can get really complex and fancy.
- 01:22 Now a dashboard is really there to present key metrics and
- 01:25 answer those important questions that you've asked of your data.
- 01:30 So you can see here I have a global sales analysis dashboard that I've built based
- 01:35 off of one source dataset.
- 01:36 And we have that source dataset over here.
- 01:39 So these are basically sales figures for
- 01:41 a global mega store that has stores all over the world.
- 01:44 And what we're doing throughout this section actually is we
- 01:48 are analyzing this data, and
- 01:50 it culminates in presenting those results on a really attractive looking dashboard.
- 01:55 Now in order to achieve something like this,
- 01:57 we've had to go through quite a few different steps.
- 02:01 You can see here that the main bulk of this dashboard is made up of these
- 02:05 different types of charts.
- 02:07 Now, these are all pivot charts that I've created using pivot tables.
- 02:11 And it's all based off of that source dataset.
- 02:13 So before we even begin, we kind of need to work out exactly what questions
- 02:17 we're looking to be answered with this dashboard.
- 02:21 So we're going to take a look at how we can build out these pivot tables and
- 02:25 build out these pivot charts.
- 02:27 I'm going to show you lots of different tips and tricks along the way.
- 02:30 Now, the reason why this has a dark design is simply because dark-themed backgrounds
- 02:35 are super popular at the moment.
- 02:37 So I thought I would jump on top of that trend and
- 02:40 build one of these in this course.
- 02:42 And I think this looks really effective.
- 02:45 Now, aside from our pivot charts at the bottom,
- 02:47 we do have some other things on this dashboard.
- 02:50 You can see at the top here we have these four, what I like to call cards.
- 02:54 They are effectively just shapes with some text inside, but
- 02:58 these pieces of text are actually functional.
- 03:01 These are all linked to formulas that we have on our calculations work sheet.
- 03:05 And that is another thing that we're going to take a look at when
- 03:09 we're building this dashboard.
- 03:11 How you can build up different types of calculations to extract results from your
- 03:15 source data and pull them through to your dashboard.
- 03:18 And the best thing about these little cards at the top and
- 03:21 all of the pivot charts is that everything is completely dynamic.
- 03:25 So that if anything changes in the source data,
- 03:28 the formulas will update automatically, and if we want to update the pivot charts,
- 03:33 we simply need to refresh them to pull through that new data.
- 03:36 So everything is really efficient.
- 03:38 Another thing that we have on here are these slices underneath.
- 03:42 And slices are effectively just visual filters.
- 03:45 Now, these slices might look different to slices that you've used in the past
- 03:50 simply because of the way that I have them formatted.
- 03:53 So I'm going to go through, I'm going to show you how these slices work and
- 03:57 you can see as I click them, they are springing into action.
- 04:00 And we're also going to take a look at how you can format them to make them look
- 04:04 a little bit more attractive because they do look kind of ugly when you
- 04:07 first insert them.
- 04:08 So we have a lot of different skills going on here.
- 04:11 So this is a really great way to practice them.
- 04:13 And I'm going to show you some invaluable techniques that I use all the time when it
- 04:18 comes to dashboard design.
- 04:20 I'm going to show you where I go to get inspiration for my designs and how you can
- 04:24 think about planning out your dashboard before you actually start to build it.
- 04:28 And of course then we are going to look in detail at how to build out the pivot
- 04:32 tables, the pivot charts, the formulas and
- 04:34 then bring it all together on this dashboard.
- 04:37 So, if you weren't sure what a dashboard was before,
- 04:41 hopefully that gives you a little bit of insight.
- 04:45 I'm really excited for
- 04:47 this particular section because I love designing dashboards.
- 04:52 So without further ado let's get on with it
Lesson notes are only available for subscribers.