Dashboards to Display Critical Information at a Glance

Quick reference

• 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.