Locked lesson.
About this lesson
In our quest to visually represent data, a chart is indispensable.
Quick reference
Charts
A chart is an indispensable tool to visually represent your data.
To create a chart
- Select your data (try to include the numbers plus the top and side headings)
- Click on Insert > Charts > Recommended charts - may save you some time
- Once the chart is inserted, move it around by clicking and dragging or resize by clicking on the chart to select and dragging the edges to resize
Note the 2 shortcut buttons on the side of the chart when selected:
- One to add and remove chart elements and
- One to change the chart style
Slicers
- Charts can accommodate slicers
Bar Chart – order of bars
- To change the order of the bars in a bar chart, sort your source data
Line chart
- Select the data, Insert Line chart, resize and add/remove some chart elements
To add an axis to the right hand side
- Select the chart, right click and change chart type
- Choose combo from the list
- Make both chart series line charts
- Tick the secondary axis button on the right for the series you need the second axis for
Format the axis
- Double click on the axis
- On the format axis screen on the right, make your changes
Add data
- Add data to the table
- Refresh pivot and the chart will update with the pivot
To add trendline
- Click on the chart to select
- Click on the shortcut button to add trendline
- 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.