Locked lesson.
About this lesson
You often need to make sense of data using a variety of reports and stats. Pivot tables help you do just that.
Quick reference
Pivot Tables
When to use
Pivot tables are used to summarize data, usually for reporting purposes. It is easy to assemble and flexible.
Instructions
It is easiest to work from a data that has been formatted as a table.
- Click anywhere in the table, on the INSERT TAB, TABLES grouping, click on Pivot table.
- Click in the designated pivot area, and on the field list screen, drag and drop fields into the different areas. Move fields around until you are happy with the result.
- You can format most of the pivot table by right clicking inside the table and working from that menu.
- If you add a filter to the pivot, remember that you can use a slider instead of the filter for a more visual way to select options.
Remember: A pivot table is not live data, you have to refresh the data if the source have changed
To show variances
- Add the value you want to show as variance twice in the value area.
- In the pivot, right click on the second column and show values as percentage difference from, choose the type of previous value shown (month in this example).
- Hide the Grand Total column by right clicking on the heading and selecting remove grand total.
To show the top 10 countries
- Click the row filter drop down button and select value filters and select Top 10. You can adjust that to show only the top 3 if you want.
- 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.