Creating PivotTables

Quick reference

• 00:05 The best and
• 00:05 fastest way to summarize large amounts of data in Excel to use a pivot table.
• 00:11 If you're familiar with pivot tables, this is gonna be a very quick review.
• 00:14 And if you're not familiar with pivot tables, this is gonna be a very quick
• 00:17 introduction to the way that I'm gonna use them in the course,
• 00:20 just to show that some values have actually been aggregated correctly.
• 00:23 It's well worth taking a course on pivot tables and GoSkills has a fantastic one
• 00:28 if you wanna learn more about them and you really should.
• 00:31 So what we have here is we have a table of data.
• 00:34 This is the way data out and it's actually perfect because the best thing to serve
• 00:40 a pivot table is an official Excel table, which is what power recreates for us.
• 00:44 You see that we have a table.
• 00:45 There's our transaction's name.
• 00:47 And here is how we use it for a pivot table.
• 00:48 We go to Insert and we go to Pivot Table.
• 00:53 This is gonna give us a nice fullname here,
• 00:54 if you ever see dollar signs in here, that's a bad thing.
• 00:57 We want a table name, so that's perfect, we'll say okay,
• 01:00 put this on a new worksheet and here is why I love pivot tables right here.
• 01:04 I'm gonna go and grab group, I'm gonna throw it on to rows.
• 01:08 And you'll notice it summarizes all 4,500 values,
• 01:12 down to the two unique of alcohol and food.
• 01:15 I'm gonna grab category and
• 01:16 slide it right underneath group, this is just a left click drag and drop.
• 01:20 There we go, now I've got my unique categories.
• 01:23 Sub categorized by the group which is great.
• 01:26 I can take units sold, drag it down, throw it into the values area and
• 01:31 even put gross sales down into the values under units as well.
• 01:35 And just like that I have summarized all 4500 rows of my data
• 01:39 into a nice readable form.
• 01:41 Now it's not super pretty, so I can change that.
• 01:44 I'm going go change this right now to say units, so
• 01:46 I'm just gonna type in the cell here.
• 01:49 I can't make this the same name as what's going on in one of these fields but
• 01:53 anything else acceptable including using the same name with the space after.
• 01:57 I can call this one.
• 01:59 Gross, sales, you can't call it gross sales but,
• 02:02 I can call it gross sale dollars, and there we go.
• 02:04 And, with the click little right click value field settings,
• 02:11 and into the number format, I can even go and
• 02:14 change this to use a number format that I might be happy with,
• 02:17 so let say units, I don't want any decimal places and I want a comma separator.
• 02:21 And we will say OK and then I'm gonna right-click go to Value Field Settings,
• 02:28 Number Formats, Number and I'm gonna out 1000 with two decimal places on heer.
• 02:35 And now the beautiful thing is this pivot table looks pretty nice and
• 02:38 I can see exactly what's going on in nice readable format.
• 02:42 But the best thing about pivot tables is that they're kind of like
• 02:45 the Rubic's cube of data.
• 02:46 We can actually look at data in different ways.
• 02:49 So I can go back to my original data table, click a cell on it somewhere,
• 02:53 go to insert and I can insert a different pivot table.
• 02:58 Again based on the transactions table to say okay And
• 03:02 now I can start look at my data in a different way.
• 03:04 Now I might start he same with group.
• 03:06 I wanna put MyCategory underneath it and
• 03:10 I wanna put my ItemName underneath that, you know what?
• 03:15 That's gonna make a really long table.
• 03:16 Actually let's not.
• 03:17 Let's drag ItemName back and let it go.
• 03:21 There we go. So we can move things around.
• 03:23 Maybe I want to put group or
• 03:25 category onto my column, so it looks like this with my gross sales.
• 03:31 I go nah, that doesn't really look all that good, so
• 03:33 I'm gonna put that category back under group again, and I can play around with
• 03:38 this to find what kind of version or layout really speaks to me.
• 03:42 I'm going to go grab hour right now, and
• 03:44 I am going to drop that into columns area as well.
• 03:47 And boom, look at that, I now have a sales summary by hour, by group and
• 03:52 category to see exactly how many dogs I've sold.
• 03:55 It's hard to read though because it is not formatted well.
• 03:57 So once again, right click Value field settings.
• 04:01 This is the way that we format for Pivot Tables that always works.
• 04:04 We'll go to Number, Comma, and I don't know that I really need the decimal
• 04:08 places on this one, so I'm gonna drop those off and say OK and OK.
• 04:13 And there we go.
• 04:14 I've got a great Little Pivot Table laid out.
• 04:18 Now, the one key thing if you're not used to working with Pivot Tables,
• 04:21 that first thing I'll tell you is don't be afraid to just try and drag and
• 04:24 drop things and whatever else, because you know what?
• 04:26 Any table that comes out of Power Query is the perfect source for this.
• 04:29 And you can experiment, you can see what's going on.
• 04:31 But one thing you have to be aware of.
• 04:33 If I go back to my data right now, I'm gonna go up to the top here, and
• 04:36 I'm gonna go and make this gross sales a ridiculously big number.
• 04:39 So we're gonna put lots of 3s in here.
• 04:41 So this is if for my $12 express lunch,
• 04:43 which is an entree that was sold in hour 8.
• 04:46 And if I go back to Sheet 2 that I was just looking here.
• 04:50 Here is my entries for hour eight.
• 04:51 You can see we are only $3,400.
• 04:54 One of the big things that is different about pivot tables versus regular
• 04:57 formulas, is that these are not live and need to be refreshed.
• 05:01 The easiest way, data, refresh all, will automatically do a refresh,
• 05:06 and it automatically expands your pivot table so everything will fit.
• 05:10 I'm now going to press control Z a couple of times here.
• 05:13 And undo my data just to make sure that it comes back to normal.
• 05:17 But the key thing to remember is always refresh your pivot table so
• 05:21 that you know that you're relying on the latest and greatest information.

Lesson notes are only available for subscribers.