Locked lesson.
About this lesson
Having a macro-level - or a birds-eye view - of your marketing efforts is essential. It allows you to see trends, make better business decisions, and point out areas to investigate.
Exercise files
Download this lesson’s related exercise files.
Dashboard Data exercise.xlsx177 KB Dashboard Data Solution.xlsx
30 MB
Quick reference
Dashboards
A dashboard in this example is a collection of charts linked to pivot tables and it displays your data visually, on one sheet, to give a quick glance at critical information.
Background to the dashboard
- Insert a sheet by clicking on the plus next to the sheet tabs, drag the sheet to the desired position
- Right click on the sheet name and rename to dashboard
- Insert a heading to the sheet, adjust the font type, size and color on the Home tab
Hide gridlines
- To hide the gridlines, go to the page layout tab, sheet options grouping and untick the box next to view under gridlines.
Insert background
- You can insert a background picture from a file or from from the internet.
- On the page layout tab, page setup grouping, background button and follow the instructions from there
- It may take a bit of resizing and picture editing to get the effect you want – this needs to happen outside of Excel
Themes and colors
- Themes and colors can also be adjusted on the page layout tab, on the themes grouping
- If you set a theme here the theme will be available in other Excel documents
Slicer
- To format the colors for the slicer, click on the slicer to activate the contextual tab for the slicer
- Select a style that is close to what you want, right click and duplicate
- Click on the new style, right click and modify each element of the slicer in terms of font, border and fill
- On the slicer contextual tab, the buttons grouping, change the size of the buttons
- Link the slicer to other charts or pivots in the slicer grouping, the report connections button
Add charts
- Cut and paste your charts from the older sheets, move and resize as required
- Try to do editing on the charts before you move them, you can do it from the dashboard, but it takes a little longer
Map chart
- Copy the data from the map pivot next to the pivot
- Select the data and click on insert tab, charts grouping, maps
- Excel finds the relevant map and links it to your data
- Right click on the chart, select data and change the chart data range carefully to point to the pivot (if you mess up, just press Escape and repeat)
- Delete the copied data
To only show the top 10 countries, on the pivot and the map, click on the row label filter button, value filters, top 10 countries
Login to download- 00:04 Having a macro level or a bird's eye view of your marketing efforts is essential.
- 00:09 It allows you to see trends and make better business decisions.
- 00:13 It also helps you to see backwards, so to speak.
- 00:16 So you can learn from mistakes, as well as wins.
- 00:19 But as marketers, we're usually playing the role of detective,
- 00:24 piecing together clues to determine why newsletter subscribers skyrocketed,
- 00:30 or why traffic slowed.
- 00:31 A dashboard will provide a quick glance of key metrics to
- 00:34 alert you if something is amiss, so you can dig into the data more if you need to.
- 00:39 Now in this video, we want to get to this type of dashboard.
- 00:44 We have all the elements prepared.
- 00:47 So let's talk about how we do this.
- 00:49 The elements we have are our data in a table.
- 00:52 So just note that our data is greatly reduced because 6,000 lines
- 00:56 just takes too much time to update.
- 00:59 Now, I have data under that to update with.
- 01:03 I have my pivot table for traffic per month, and my chart.
- 01:06 I have my traffic by source pivot and a bar chart with a slicer for months.
- 01:12 And you'll see the coloring is different in the document.
- 01:16 To change the coloring, you go to Page Layout and you can set a Theme.
- 01:21 If there isn't a default theme that you like,
- 01:25 you can edit a theme by just clicking on Colors,
- 01:28 customizing colors, and choosing other colors for the theme.
- 01:33 And you can give it a name.
- 01:35 And I've done that already.
- 01:36 So I have my theme Excel for marketers set up already.
- 01:41 And that is how the colors are different.
- 01:43 Now, change the colors for the slicer, you click on the slicer,
- 01:48 the slicer contextual tab will appear.
- 01:51 And you'll find the slicer styles, and
- 01:53 just choose the one that is closest to what you want.
- 01:57 I'm going to use this one as a quick example.
- 01:59 So right click on the Style, because you can't modify it from here.
- 02:04 Just duplicate it, and you'll get a new option that you can modify.
- 02:10 So it takes a bit of effort to modify your slicer.
- 02:12 But you can click on Selected Item with Data and
- 02:16 that means I've selected January and there's data for January.
- 02:22 So Format, I can change the coloring of the outline, I can change the width
- 02:26 of the line and the color, and apply that to the outline of my button.
- 02:31 Before I hit OK, you can change the color of your fill.
- 02:36 I'm quite happy with this one.
- 02:38 You can even change the font and the color.
- 02:41 But once you're done, hit OK.
- 02:42 And you've changed the format.
- 02:44 And now hit OK, and that makes the line a little bit thicker.
- 02:49 So play around in there until you're happy with your slicer.
- 02:53 I've made changes to my bar chart.
- 02:55 I've reduced the gaps in between the data on the format
- 03:00 screen Gap Width, that's where you make the gap smaller.
- 03:05 I only need to look at two sets of data at most at a given time.
- 03:09 So I'm pretty happy with what we have here.
- 03:11 But then I have my traffic by country pivot.
- 03:15 And I would like to do a map pivot on this one.
- 03:19 So that would look like this.
- 03:21 Excel can't do a map chart on a pivot table, so we need to do a bit of a cheat.
- 03:28 Let's go and select my pivot.
- 03:34 And we'll copy, and we'll just paste it right next door to it.
- 03:39 Now, while it's selected, I'm going to click on Insert and
- 03:46 Maps, and I have a filled map.
- 03:49 Now, while the map is selected, I don't want any legends,
- 03:53 let's deselect that, click on the chart, right click, we'll select data.
- 03:58 And I need to change my data source.
- 04:01 I need to change that K to an A.
- 04:05 Be very careful.
- 04:06 Click next to the K and delete one letter at a time.
- 04:10 Don't move with your arrows.
- 04:12 And the S, I'm going to change that to an I.
- 04:16 Once that's set, I'm going to hit OK.
- 04:20 And now my chart refers to the pivot table and Excel is none the wiser.
- 04:27 I can highlight my temporary data and delete, And I still have my chart.
- 04:35 So it's best to edit your chart while it's still on this sheet.
- 04:39 Because it takes a bit longer if it's on the dashboard sheet to edit.
- 04:45 So this is my Traffic by country chart.
- 04:48 Let's rename the title right now.
- 04:50 And there we go.
- 04:52 Now I have all my charts and I only need my dashboard.
- 04:56 So I need an extra sheet for the dashboard.
- 04:59 I'm going to insert a new sheet, and I'm going to reposition it right at the front.
- 05:07 I'm going to rename it Dashboard.
- 05:08 On the dashboard, I don't want to look at any grid lines.
- 05:12 So let's go to the Page Layout tab > Sheet Options.
- 05:17 I'm going to uncheck Gridlines, so I don't have to see them.
- 05:21 And I'd also like a background.
- 05:23 And I do have a background saved in a folder.
- 05:26 So let's click on Background.
- 05:27 We'll go from a file, because it's on my computer.
- 05:31 And in my marketing folder, I have a picture that is ready to set up.
- 05:35 That looks great.
- 05:37 Hit Insert, adds it to the background.
- 05:42 There it is, that looks great.
- 05:43 So now, I need my heading.
- 05:45 And this part's pretty simple.
- 05:47 The cells are still there, even though the grid lines aren't.
- 05:51 So I'm just going to click in that first cell, and I'm going to just choose some
- 05:55 formatting options with the font and the size, and call this Demo Company.
- 05:59 And then, going to use a slightly different style.
- 06:03 Let's go with Calibri Light, and we'll make it a little smaller, let's go 14.
- 06:08 And I'm going to use a light grey color for this little subtitle, let's try this.
- 06:14 Okay, so we'll type in
- 06:18 Traffic Dashboard.
- 06:23 That is too light, that's not going to work.
- 06:24 Let's just choose a darker color, that's much better.
- 06:28 All right, now I can go back, click on my chart, and I'm just going to cut and
- 06:33 paste onto my dashboard.
- 06:34 So in Windows it's Ctrl+X.
- 06:38 For Mac, its Cmd+X to cut.
- 06:39 And this one will take a little while, so don't be in too much of a hurry.
- 06:46 Okay, now on the dashboard sheet, we hit Ctrl+V to paste.
- 06:50 Wait for it to load.
- 06:52 There it is.
- 06:55 Let's do the same thing with our slicer and our traffic by source chart.
- 07:00 Again, we hit copy and paste.
- 07:05 And we'll just kind of put it right next to the mapped one, that's fine.
- 07:09 On second thought, we'll move that down there.
- 07:13 And let's go grab the other one, Traffic per month.
- 07:17 We do Ctrl+X and Ctrl+V to paste it in here.
- 07:25 Now that we have all these elements here,
- 07:28 we can even resize them to make them look better on the dashboard.
- 07:32 You see we can move the map around, we can move Traffic by source over here.
- 07:38 Make some room, maybe make it a little smaller so we have room for
- 07:42 Traffic per month.
- 07:43 It would be nice if they were the same size, we just kind of click and
- 07:48 drag the bounding box on the sides and reposition and resize as we want to.
- 07:54 Move that up very slightly.
- 07:55 This is starting to look really good.
- 07:58 And now let's reposition the slicer so
- 08:01 that it's actually covering the whole bottom of this.
- 08:07 But no, I mean those buttons aren't going to work very well.
- 08:10 With slicer selected, let's go to the contextual menu.
- 08:14 Let's change the number of columns.
- 08:15 You can see that's a much better layout for all those buttons in the slicer.
- 08:20 Instead of the one column, now we have six.
- 08:23 So that looks great, but we don't want those last two buttons.
- 08:26 The last two buttons are there because Excel sees those fields as dates.
- 08:31 So I could just move the slicer border so it excludes it.
- 08:34 And that kind of takes care of it, right?
- 08:38 Let's select the slicer again, because right now,
- 08:42 it's linked to Traffic by source to that chart in pivot.
- 08:46 And you can see, as I select and deselect that the traffic by source changes.
- 08:52 Well, on the slicer contextual tab,
- 08:56 there is a report connections button.
- 09:01 So let's click that and link it to Traffic by country and Traffic by source.
- 09:06 And both now, Traffic by country and
- 09:09 Traffic by source charts will update with the slicer.
- 09:14 But I don't like the current chart.
- 09:15 There are too many countries with colors.
- 09:18 And it's kind of difficult to know where the bulk of my traffic is coming from.
- 09:21 So let's go to Traffic by country, go to that pivot,
- 09:25 and I want to filter my row labels to show me only the top ten countries.
- 09:29 If I click on my Dashboard, the chart only highlights the top ten countries for
- 09:33 each month.
- 09:34 And there is your dashboard.
- 09:37 It's ready for you to keep an eye on the metrics that mean the most to you.
Lesson notes are only available for subscribers.