Locked lesson.
About this lesson
Exercise files
Download this lesson’s related exercise files.
08-05-Dashboard Design-Start.xlsx2.3 MB 08-05-Dashboard Design-Complete.xlsx
2.3 MB 8.05 dashboard-design - Exercise.docx
53.4 KB Exercise - Dashboard Design.xlsx
8.7 MB 8.05 dashboard-design - Exercise solution.docx
69.2 KB Exercise Solution - Dashboard Design.xlsx
8.7 MB
Quick reference
Dashboard Design
Research Excel dashboards online to get design inspiration.
When to use
We need to think about design every time we create a dashboard. Consider any company constraints such as branded colors, house-style etc.
Instructions
How we design our dashboard is really only limited by our imagination. There is no right or wrong way to design a dashboard. When thinking about the look and feel of the dashboard, we may need to consider things like company colors, house-style fonts, company logos, and imagery.
Create a Wireframe
All dashboards should start with a wireframe. A wireframe is simply a digital version of sketching out the layout in a notepad. We can create a very quick wireframe in an Excel worksheet and color-code the different areas that will house each element.
Get Inspired!
Struggling to think of an engaging design or simply have a creative block? Get inspired by searching for 'Excel Dashboard Design' on Pinterest.
Start Assembling the Dashboard
In our example we are building a dashboard that has a dark theme:
- Remove the gridlines (View > Deselect Gridlines).
- Change the background fill color to dark gray (Press CTRL+A to select all > Home > Font > Fill Color)
- Add a Text Box for the Title.
- Add an Icon as the Logo.
- Add Rounded Rectangle Shapes with a Glow Shape Effect to hold the high-level key metrics.
Login to download
- 00:04 So, now we have many of the elements that we need to assemble our dashboard.
- 00:08 We have our PivotTables and our PivotCharts, and
- 00:12 we also have our calculations ready to go.
- 00:14 So, the final stage of building this dashboard is really to
- 00:18 start thinking about how we want to design our dashboard.
- 00:21 Now, the decisions that you make when it comes to the actual look and
- 00:25 feel of your dashboard might be determined very much by branding guidelines
- 00:29 that you have at your company.
- 00:31 Maybe you have to use specific colors, maybe you need to have your company
- 00:35 logo somewhere on there, maybe you need to use specific fonts.
- 00:40 These are all things you need to consider when you're putting together your
- 00:43 dashboard.
- 00:44 And really dashboard design is entirely up to you.
- 00:47 And I would say that if you're looking for some really good inspiration when it
- 00:51 comes to how to design your dashboard, what colors to use, what layout.
- 00:55 Then jumping onto Pinterest and just typing in Excel dashboards is a really
- 01:00 great way of seeing some excellent examples of Excel dashboards which will
- 01:04 hopefully inspire you when you're creating your own.
- 01:08 Now another little tip when it comes to designing your dashboard.
- 01:11 I always like to create what we call a wireframe and
- 01:14 I've got an example of a wireframe just here.
- 01:18 Now I've done mine in Excel,
- 01:19 this could be something that you simply sketch out in a notebook.
- 01:23 And really all the wireframe is therefore is to really just sort of get a general
- 01:27 idea in your head as to which elements you're going to add onto your dashboard
- 01:31 and where you're going to place those elements.
- 01:33 For example, here I've got a little header sketched out at the top, so
- 01:37 that might be where I have the title of the dashboard and maybe a logo.
- 01:42 Underneath that I then have summary statistics.
- 01:45 So I told you that we were going to create summary statistics out of those
- 01:49 calculations that we did in the last lesson.
- 01:52 So we want to make sure that we leave a place at the top for those.
- 01:55 Underneath that I might then want to add some slicers so
- 01:58 that my dashboard is easy to filter, so I'm going to leave a gap for that.
- 02:02 And then I have, effectively placeholders for all of my different pivot charts.
- 02:07 And of course, this is simply a rough guide, once you get your charts
- 02:10 onto the dashboard, you might decide you want to rearrange them.
- 02:13 It's absolutely fine, but it's a good idea to have an idea in your head as to how
- 02:17 this dashboard is going to look.
- 02:19 The next thing is then to consider the colors, the fonts,
- 02:22 and the overall style that you want to use for your dashboard.
- 02:26 Now I've decided to go with a darker dashboard theme this time.
- 02:29 If you remember when we were creating all of our pivot charts,
- 02:32 I was choosing the darker chart style each time.
- 02:34 Now, I'm sure you're going to have a lot more time put into thinking about
- 02:38 the design of your dashboard than I have here.
- 02:41 I've really just got this basic dashboard design up on the screen, so
- 02:44 you can see how this looks when we pull everything together.
- 02:46 Now let's just run through the different changes that I've already made to this
- 02:50 spreadsheet so you understand how to do this.
- 02:53 Now the first thing I did was I removed the grid lines from my worksheet, so
- 02:57 on the View ribbon in the show group, just deselect grid lines.
- 03:01 I then simply pressed Ctrl A to select all cells, and
- 03:04 I changed the background fill of these cells to a dark gray color.
- 03:08 I then simply added a text box to hold the title.
- 03:13 I changed the background fill of cell A1 to make that pink and
- 03:17 then I just added an icon that's available within Excel.
- 03:20 If you're not sure where your icons are, if you go to the Insert ribbon and into
- 03:24 Illustrations, you have a whole gallery of different icons that you can use.
- 03:28 So I just literally typed in globe and I chose this icon just here.
- 03:32 I then simply started to mark out where those high-level summary statistics
- 03:37 are going to go.
- 03:38 So once again, I've just added a shape, this time a rounded rectangle.
- 03:43 I've removed the background fill, I've given it a green outline and
- 03:47 I've also given it an outline effect.
- 03:50 So if we go to shape format, you can see here, Shape fill is set to No Fill.
- 03:56 Shape outline is green, and
- 03:58 then I've added a glow shape effect to get this nice glowy box.
- 04:03 And these headings in here again are just simply text boxes.
- 04:07 So nothing too complex there when it comes to the design of this dashboard so far.
- 04:12 Now, when we bring all of the dashboard elements together,
- 04:15 I'm going to pull in the high level statistics into these boxes at the top.
- 04:19 I've marked out another area below, this is where my slices are going to go, and
- 04:23 then underneath here, this is where we're going to place our charts.
- 04:27 So this is the start of a really nice dashboard design and
- 04:30 this is only really limited by the extent of your imagination.
- 04:33 You don't have to be the most amazing designer in the world to
- 04:36 create a dashboard that looks really nice.
- 04:38 So in the next lesson, we're going to bring everything together.
- 04:41 We're going to pull in our pivot charts, we're going to add some slicers and
- 04:46 add the high level key statistics at the top.
- 04:49 And then I'm going to show you how easy it is to update your dashboard if more data
- 04:53 is added into your source data.
Lesson notes are only available for subscribers.