Bring it all Together and Keep Information Updated

Quick reference

• 00:04 In this final lesson of this section, we're going to bring everything together.
• 00:08 So all of the pivot charts, all of the key statistics,
• 00:11 we're going to add some slicers, and do lots of fun stuff.
• 00:14 So let's start by pulling into our dashboard those high level statistics from
• 00:19 the calculations worksheet.
• 00:21 Now, if you want to link to other things in different worksheets,
• 00:25 we can link directly from text boxes.
• 00:27 So if we click on Insert at the top, and click on Text and
• 00:32 choose Text Box, what I can do is draw a text box just here.
• 00:37 We're going to make sure that we're clicked on the text box and
• 00:40 then we're simply going to go up to the formula bar, type in =, and
• 00:43 then link to the metric that we want to pull through.
• 00:45 So let's jump across to the calculations worksheet.
• 00:48 And this is the first one, cell D7, hit Enter, and
• 00:52 now we can apply some formatting.
• 00:55 So let's go to shape format.
• 00:56 I'm going to remove the background fill and also the outline.
• 01:01 I'm going to change the font to Roboto Black.
• 01:05 We'll change the font color to white.
• 01:08 We'll make it a little bit bigger.
• 01:10 And let's just align that to the middle.
• 01:12 And of course you can spend a bit more time than I am right here making these
• 01:17 look really nice.
• 01:18 So I want to do a similar thing for the other cards.
• 01:20 The quickest way to achieve this is simply to duplicate or copy and
• 01:23 paste the first card.
• 01:25 So I'm going to press Ctrl-D to duplicate it.
• 01:28 Let's drag it across.
• 01:29 And now all we need to do is change the cell that the text box is pointing to.
• 01:34 So in the formula bar, =, let's jump back across to calculations, and
• 01:38 this time we want to display this metric just here.
• 01:41 Hit Enter, and of course to copy across the formatting we can use
• 01:46 our format painter to make that process a little bit quicker.
• 01:50 So I'm going to do exactly the same for the other two,
• 01:52 join me back here once I've done that.
• 01:54 So now we have those high level metrics,
• 01:56 we can start to bring across the different charts.
• 01:59 So let's go all the way back to the first one.
• 02:02 I'm just going to grab it Ctrl-C, and
• 02:04 then we're going to paste it on to the dashboard.
• 02:11 I'm just going to repeat that process for all of these charts.
• 02:14 So I have the charts pasted on the dashboard, and I've just organized them so
• 02:18 that they look a little bit better.
• 02:20 And I think I'm just going to drag this one a bit further down.
• 02:23 And let's apply some formatting.
• 02:25 So some of these have borders around the outside.
• 02:27 Let's get rid of those.
• 02:29 Unfortunately, you can't do these all in one go.
• 02:31 So we need to select the first chart up to the Format tab, and
• 02:35 we want to say No Outline.
• 02:37 And I'm going to do the same for these other two as well.
• 02:40 Now what I'm also going to do is, so that these blend in a little bit better
• 02:44 with the dashboard background, I'm going to remove the shape fill also.
• 02:48 So that's going to remove any color.
• 02:50 So everything looks like it's supposed to be on this dashboard.
• 02:56 Now, another little formatting trick you can do here which can make these look
• 03:01 a lot nicer and all parts of one dashboard, is instead of having these
• 03:05 different headings Profit by Month, Average Units, Total Revenue, so on and so
• 03:09 forth, we could delete these and add a more stylized heading for each of them.
• 03:14 Now, I'm not going to walk you through this process step by step,
• 03:17 because it really is just a case of adding text boxes, so
• 03:19 let me show you the finished result.
• 03:21 So if you take a look at what I have now, I've actually removed the individual
• 03:26 chart titles and I've just added some more stylistic titles.
• 03:30 So these really stand out.
• 03:31 We have that same neon border and then just some text.
• 03:34 And I think you'll agree that it makes this dashboard look a lot
• 03:37 more interesting.
• 03:38 So always think about doing things like that,
• 03:41 little flourishes make the world of difference.
• 03:43 The next thing to do is add in some slicers in order to filter our data.
• 03:47 So we're going to click on this first chart up to PivotChart Analyze,
• 03:51 and choose Insert Slicer.
• 03:53 And here we can see all of the column headings from our source data,
• 03:56 I'm going to have three slicers.
• 03:58 That's going to be Item Type, Sales Channel, and Order Priority.
• 04:03 Let's click on OK.
• 04:05 So now I have these, I want to format these so
• 04:08 they fit in nicely with my dashboard design.
• 04:11 So let's click on the Slicer tab at the top, and in the Columns area, I'm going to
• 04:16 take the columns up, so that these slicers run horizontal as opposed to vertical.
• 04:21 And I'm going to do the same for all of these.
• 04:24 So we need to make sure we click on each individual slicer and
• 04:27 then just adjust the number of columns.
• 04:29 So now we can drag these out and drag them up.
• 04:34 Now, I'm just going to do this roughly,
• 04:35 because we are going to do a little bit more formatting in a moment.
• 04:38 So let's just get these on here.
• 04:41 Drag this out and drag that up.
• 04:45 The next thing I'm going to do is make these look a little bit cleaner by
• 04:48 removing the slicer headings.
• 04:50 So once again, let's click on our first slicer.
• 04:53 On the Slicer tab we can go to Slicer Settings, or
• 04:56 we can right click on the slicer, and we have Slicer Settings right at the bottom.
• 05:00 And we're going to choose to not display the header.
• 05:04 So we want to remove the tick from this box, click on OK.
• 05:08 I'm going to do exactly the same for all of these like so.
• 05:12 Now I'm going to change the slicer color.
• 05:14 So let's go to the Slicer Styles area, and I think I'm going to choose
• 05:19 this one just here, because that fits a little bit better with my theme.
• 05:23 So whilst I've selected this slicer theme,
• 05:26 I don't particularly like this white in the background.
• 05:30 I want it to blend in a little bit more with my dashboard background.
• 05:34 So to do this, we effectively need to modify the slicer style that we're using.
• 05:37 So we're using this one just here,
• 05:40 we need to right click and duplicate it, and give it a name.
• 05:45 So I'm going to call this my initials and Custom.
• 05:48 And then when I click on Whole Slicer and Format,
• 05:51 we can't set the slicer background to transparent, unfortunately, but
• 05:55 we do have a Fill tab and we can fill the background with the exact color.
• 05:59 So if you have the hex codes, you could go into more colors and type them in here.
• 06:03 I don't, this is a bit of a guess.
• 06:05 But I'm thinking that one's probably going to be fairly close.
• 06:07 And I'm also going to remove the slicer border.
• 06:11 We're going to set that to none.
• 06:13 Click on OK, and OK again.
• 06:15 And now we should find that we have our custom slicer up here.
• 06:19 And when we apply it, it's going to apply that formatting.
• 06:22 And I think that looks pretty good.
• 06:24 So I'm going to apply it to the rest of my slicers.
• 06:28 So now these look a little bit better.
• 06:30 They blend in more with the dashboard.
• 06:32 We can do a little bit of jigging around to get this to
• 06:35 look exactly as we want it to look.
• 06:38 So let's just move that up a little bit.
• 06:40 And I'm pretty happy with that.
• 06:42 Now currently these slicers are only attached to this first chart.
• 06:46 If I start clicking around,
• 06:48 it's only going to affect the first chart on the dashboard.
• 06:51 And that's because that was the chart we selected when we inserted the slicers.
• 06:56 So you want to make sure that you connect your slicers
• 06:58 up to all of the charts that you want them to control.
• 07:01 And we do that by right clicking on the slicer, jumping into Report Connections.
• 07:05 And you can see here exactly which charts this slicer is controlling.
• 07:09 So for me, it's just the Profit by Month chart.
• 07:12 So I'm going to select all of the charts, click on OK, and
• 07:16 I'm going to do exactly the same thing for the other two slicers.
• 07:20 So now we should find that our slicers control all of the charts.
• 07:27 The final thing to show you here is how we can very simply update our dashboard and
• 07:32 all of the charts on it when the data changes.
• 07:34 So if we jump back to our source data over here, and go to the bottom,
• 07:39 when we add new data onto the bottom of this table,
• 07:42 we want to be able to click one button and have everything update.
• 07:47 Now in order to do this, I'm going to cheat a little bit.
• 07:49 I'm going to extend the table down, just a bit,
• 07:52 and we're just going to copy and paste some of the existing data.
• 07:56 So let's just take this little range here and paste that down here.
• 08:02 So now effectively we've added more data.
• 08:04 If I want to include this on the dashboard,
• 08:07 all I need to do is go back to the dashboard.
• 08:10 And this is going to be a very small change,
• 08:12 because we've just added a tiny bit of data.
• 08:14 So you're going to need to watch closely.
• 08:16 But if we click on one of the charts at the top here, go to PivotChart Analyze,
• 08:20 we have a refresh button in the middle.
• 08:22 And what you should find is that when you click Refresh All,
• 08:26 there was a very tiny little change there, but
• 08:29 that is going to update all of that new data on the dashboard

Lesson notes are only available for subscribers.