Charts as a Visualization Tool

Quick reference

• 00:05 In the previous video, we looked at conditional formatting.
• 00:08 While it's an exceptionally useful tool to visualize data,
• 00:12 it doesn't summarize data very well or look completely polished.
• 00:17 So in this video and the next we'll cover the best tips and tricks for
• 00:21 putting together charts and dashboards that are both beautiful and informative.
• 00:27 Now, before we begin, let's outline what constitutes a good chart.
• 00:31 A good chart is easy to understand, and it represents data clearly.
• 00:37 This means not putting too many data points or variables,
• 00:40 and picking a chart that's appropriate for the type of data.
• 00:45 Thankfully, the newer versions of Excel are pretty intuitive, and
• 00:49 they'll auto suggest chart styles that suit your data.
• 00:52 So it's easier than ever to present your case for increasing your PPC budget or
• 00:57 communicating the successes of previous email campaigns.
• 01:02 Now, in this sheet I have Google Analytics data for
• 01:05 January through March in table format.
• 01:09 And then I have April to July information ready to be updated to the table.
• 01:16 I also have two pivots, one that I want to use to set up a bar chart and
• 01:20 that shows my sessions for each month by source and
• 01:24 also my total sessions compared to the total leads for each month.
• 01:30 So let's start with the bar chart first.
• 01:33 To insert a chart is really very easy.
• 01:36 You select the data that you want and then you press a button.
• 01:40 You may question what data to select.
• 01:43 I include my headings, and I include my categories and my data.
• 01:47 But just go up and click on Insert.
• 01:50 And here are all your chart options.
• 01:55 Very often the recommended charts will kind of hit it right on the head for you.
• 01:59 And that makes things a bit easier.
• 02:01 In this instance, we have our clustered column as a suggestion and
• 02:04 that's exactly what I want.
• 02:06 So we hit ok and there's your chart.
• 02:09 It's all decorative changes from here.
• 02:11 You can left-click and drag to move it around.
• 02:15 Left-click and drag the corner to resize.
• 02:21 And a chart has three contextual tabs up Tom.
• 02:25 So you can choose options there.
• 02:29 There's also two shortcut buttons to add and take away elements and
• 02:33 to edit the chart style.
• 02:35 This is a pivot chart, so it comes with these gray field buttons that you see
• 02:40 around the outside of the chart.
• 02:42 I would rather have a slicer which looks better.
• 02:47 So I don't want these buttons.
• 02:48 If you hover over this with your mouse, you can right click, and then hit hide
• 02:53 all field buttons on chart, and then hit enter and that gets rid of those.
• 02:58 Now let's go up to the chart elements button.
• 03:00 Click that so we can do some more editing to this chart.
• 03:03 We'd like to have a title.
• 03:05 Definitely want that, but I don't want these gridlines and
• 03:07 I want to hide that legend.
• 03:09 I don't want that in there either.
• 03:10 So just by clicking a few boxes, you can make some quick changes to your chart.
• 03:15 And now, let's retitle this, I want to call this Traffic by Source.
• 03:20 That's really a better name for this than chart title.
• 03:23 So we'll type that in and hit Enter.
• 03:26 And now before we're done, let's add that slicer I was talking about,
• 03:30 go up to the filter section here and click Insert slicer.
• 03:35 We can do that by month.
• 03:37 Hit OK.
• 03:39 Now we have this menu to show us the traffic by source by month simply by
• 03:43 clicking on the month we're interested in.
• 03:46 So this is looking really good.
• 03:49 One thing I going to do is just adjust the height of this chart.
• 03:52 I can click and drag that to make it not so tall.
• 03:55 And one other thing catches my eye, it's actually,
• 03:58 this other category that's sitting at the beginning of the chart.
• 04:01 I would prefer that other went to the end so what we can do is to change
• 04:06 the order of the bar is I can click on the Row labels in my pivot table, and
• 04:11 just sort from Z to A, instead of A to Z, and now it's right at the end.
• 04:15 And the fact that this is in reverse alphabetical order doesn't bother me.
• 04:20 I'm happy with the chart.
• 04:23 So now let's add a line chart.
• 04:26 Let's go over to our other sheet.
• 04:28 I'll select my data headings at the top.
• 04:32 Headings are series names on the side with data in the middle.
• 04:38 Now we go to Insert, and I'm going to go straight to the line chart.
• 04:44 Nice.
• 04:45 So let's move the chart.
• 04:47 Right click to hide the field buttons on the chart.
• 04:53 Let's go to the chart elements, we'll add a title.
• 04:59 Let's get rid of those gridlines again, and I want to put my legend at the bottom
• 05:03 so we can click this little menu here, move it from the side to the bottom.
• 05:07 That looks better.
• 05:09 Chart title, we can change that to Traffic per month.
• 05:18 Nice, now I know leads and sessions should be related.
• 05:22 But it's so far away on this chart, you can't really see the relationship.
• 05:28 So let's add a separate axis for
• 05:31 the leads on the right-hand side and now how do we do this.
• 05:35 Click on your chart, right click and do change chart type.
• 05:43 I can choose combo and I can change both options back to line.
• 05:51 Then it looks the same as it does right now.
• 05:53 But for my leads, I can add a secondary axis.
• 05:57 So, let's hit OK.
• 06:00 And now I have an axis for the sessions and an axis for the leads.
• 06:04 And let's just format these.
• 06:07 They don't look all that great yet, click on the numbers.
• 06:11 Double click and let's start with a minimum of 0,
• 06:16 maximum of 70,000.
• 06:19 That's good.
• 06:20 And I want 20,000 units, 20,000 sessions as a gap.
• 06:29 All right, that looks better.
• 06:31 And now let's click on the right hand axis here.
• 06:34 Let's click the maximum 15,000 otherwise my leads are going to look like it's
• 06:39 more than these sessions.
• 06:40 Okay, at least now it's a visually below and let's say my unit increment is 5,000.
• 06:49 So 5, 10, 15, and
• 06:51 I have a visual representation that would indicate the values are related.
• 06:58 I think this chart could probably do with a trendline.
• 07:01 But before we do that, let's just add some data.
• 07:06 So here's my table.
• 07:08 I'm going to select my data from the bottom.
• 07:11 So let's hit control shift, right arrow and then up arrow.
• 07:16 We have that selected.
• 07:18 We end up at the top.
• 07:19 I'm going to click and drag my data and drop it just below.
• 07:23 And you can see the table has grabbed that data.
• 07:27 So that's a done.
• 07:28 Now let's see if our pivot has updated.
• 07:34 So we clear the filter, the pivot hasn't updated yet.
• 07:37 I can just right click on my pivot and hit refresh.
• 07:42 And that updates to include July data.
• 07:45 This includes all the data in the table now, and even my other pivot is updated.
• 07:51 So let's see if I add a trendline, click on trend line here under chart elements.
• 07:58 want to add a trendline for sessions or leads,
• 08:02 let's pick leads and there is my chart.
• 08:06 As the saying goes, numbers talk.
• 08:09 Using graphs and
• 08:10 charts to visualize numbers helps make the message more salient.
• 08:15 Knowing how to visually communicate what the data says is an important skill.
• 08:20 It's one that will help you in putting together reports,
• 08:24 analyzing campaign results and making other business decisions.

Lesson notes are only available for subscribers.