Pivot Tables from Tables

Quick reference

• 00:04 In this video we're going to look at pivot tables.
• 00:07 As marketers, we're often handed large swaths of data, and
• 00:10 we are asked to make sense of it.
• 00:12 This could be Google Analytics data, social media data, sales data, and more.
• 00:16 But whether you need to create reports, graphs, or some other way
• 00:20 to visually explain the data, we need to make sense of what's there first.
• 00:25 And pivot tables will help you do just that.
• 00:28 With this one tool you can grab the data you need without using additional
• 00:32 functions or formulas.
• 00:35 In this example I've got data from Google Analytics.
• 00:38 I've added a column at the end called month to show which month the data
• 00:42 is from.
• 00:42 I'd like to create a report and add only the new data to the table and
• 00:47 to the report.
• 00:48 But because the first data is in a table, I can just copy the data to the table,
• 00:53 and the table will grab the new data and it will be included in my report.
• 00:58 So I have my data in the format of a table.
• 01:02 Now I want to use a pivot table to summarize my data.
• 01:05 So click inside the table, and you can either click on Table Design and
• 01:10 then Summarize with PivotTable.
• 01:12 Or you can go to the Insert menu and just click PivotTable.
• 01:17 It'll automatically select your table, and
• 01:20 it wants to put it in a new worksheet, and I'm happy with that.
• 01:25 Now, here's the area for my pivot table.
• 01:28 With a pivot table you have your contextual tabs, Analyze and Design.
• 01:33 The Design tab can help you make everything look nice.
• 01:36 And I encourage you to check all that out on your own.
• 01:39 But in this video we're going to focus on the Analyze tab.
• 01:42 So let's take a look at a few things here.
• 01:46 On the right hand side we have our PivotTable Fields, and
• 01:48 this is nothing other than the headings of your table.
• 01:51 We have four areas below that we can place these fields in.
• 01:56 So let's see how that would work.
• 01:58 I could, for example, drag country into column.
• 02:02 And you can see what happens here.
• 02:05 It's way too wide.
• 02:07 We can barely see a few countries before the whole table is cut off.
• 02:10 So let's drag countries to rows instead and see how that changes things.
• 02:16 There we go, that's much better.
• 02:18 Now I can drag something else, like the month, into my columns.
• 02:23 And that's not bad.
• 02:26 So I only have one month of data in there.
• 02:28 So this makes sense.
• 02:30 But now, what do I want to look at per country, per month?
• 02:34 I would like to look at sessions.
• 02:36 And what Excel does here, when you drag that into the values window,
• 02:41 it adds up all your sessions for that month for each country.
• 02:45 You can also add a filter here.
• 02:47 Let's say you take the default channel grouping, drag that into filters, and
• 02:52 then you can choose that you only want to show, say, the social.
• 02:56 And now you only see your social sessions per country for the month.
• 03:01 If you change your mind about that filter, you can go back up, and then just
• 03:06 click All to get all the sessions back, and the numbers are there again.
• 03:12 But maybe you change your mind, maybe you don't want that filter.
• 03:14 You just click and drag it out of the filters window, and
• 03:17 it's gone from your pivot table.
• 03:20 Instead, what we could do is we could add a slicer.
• 03:23 You see up here on the Analyze contextual menu, you can hit Insert Slicer.
• 03:27 Now, a slicer is basically the same as a filter, just looks nicer.
• 03:31 And to be consistent,
• 03:32 let's choose the default channel grouping again to demonstrate this.
• 03:36 So there it is.
• 03:37 It's like a filter with buttons.
• 03:38 Now, all the blue buttons mean that they're highlighted,
• 03:41 means that data is selected.
• 03:43 If you want to deselect it, you just click it.
• 03:45 And that's how a slicer works.
• 03:50 So now we can just clear the filter, there we go.
• 03:54 Now let's see what happens if we add data to this.
• 03:57 The end of August has come, and now we have September data,
• 04:00 we'd like to add it to our pivot table.
• 04:03 Well, I'm going to grab the data from down below.
• 04:05 Let's move to the bottom of the data, so that we end up at the top.
• 04:09 Here, we've got everything there.
• 04:11 Let's deselect our headers, because we don't need those again.
• 04:16 And like you've seen demonstrated before, let's drag and
• 04:20 drop this new data to the bottom of our previous data to add it to the table.
• 04:26 And you'll see that we're going to get a message that pops up.
• 04:29 There's already data here.
• 04:30 Do you want to replace it?
• 04:31 And that's just referring to those headers down below with September's data
• 04:35 that we didn't want to copy over.
• 04:37 We don't need those, so we can hit OK here, and we're just fine.
• 04:42 So just looking at the formatting,
• 04:44 you can see that the table has grabbed that new data from September.
• 04:48 And if you want to confirm it, just go to the bottom right-hand corner.
• 04:52 You'll see that sizing handle extends all the way down to include September's data.
• 04:58 So that looks good.
• 04:59 But if we go back to our pivot table, nothing has changed.
• 05:02 What's the deal?
• 05:04 Well, we need to refresh this pivot table.
• 05:06 So click anywhere within the pivot table, right-click, and hit Refresh.
• 05:10 And now we have data for August and September.
• 05:14 Now let's say I want to see if there was a percentage change because of
• 05:18 an increase or decrease.
• 05:20 Here's what we do, we need to drag sessions into the values box,
• 05:25 and it's going to duplicate.
• 05:28 I now have two sums of sessions in there.
• 05:31 There's the normal one that I have, one for August, one for September, and
• 05:34 then there's a duplication of it.
• 05:36 Please follow along here.
• 05:38 This will be cool, I promise.
• 05:39 In the duplication column, right-click, and say Show Values As,
• 05:46 now you could choose Percentage Difference From.
• 05:51 And I want to base the calculation on my month and on the previous set of data.
• 05:57 The first column has nothing in there because there's no previous set of data
• 06:00 for August.
• 06:01 But there is for September.
• 06:03 That's August data.
• 06:05 So we can see that there was a 12.5% increase.
• 06:09 And I don't need the total session.
• 06:11 So I'm going to select those two, right-click, and
• 06:14 I'm going to say Remove Grand Total.
• 06:16 I don't need to see that.
• 06:19 And then to clear it up further, I can select this column and I can hide it.
• 06:24 But now remember, a pivot table, you can update it and change,
• 06:28 but we're going to sit with a hidden column.
• 06:31 So I wouldn't change this table.
• 06:32 And if you do change it, remember that about the hidden column.
• 06:36 But here it is.
• 06:37 This is your pivot table report.
• 06:39 Now, let's say this report is too long to look at, and
• 06:43 I only want to see the top ten countries.
• 06:46 Well, I do have a row filter and a column label filter.
• 06:50 And in the row filter, you could look at the value filters and look at the top ten.
• 06:54 And you can change that if you want.
• 06:56 You can see the top 15, top 20.
• 06:58 Let's go with 15.
• 07:02 And there you go, it shows you the top 15 countries per value.
• 07:06 And look at the table if I change my slicer.
• 07:09 Look at the numbers, how they change, some of the countries change.
• 07:14 So those filters work.
• 07:16 And there we go, this is how pivot table works.
• 07:19 If you'd like to make it look better, go into the Design tab up top,
• 07:23 choose a format that you like, play around with it.
• 07:27 So with this you can easily control large datasets,
• 07:30 pulling the information that's most important for you.
• 07:34 Data is useful when it's contextualized and
• 07:37 distilled into manageable chunks, and pivot tables help you do just that.

Lesson notes are only available for subscribers.