Intro to Pivot Tables

Quick reference

• 00:05 In this chapter,
• 00:06 we're gonna start focusing on using pivot tables with our dashboards.
• 00:09 And we assume that you have pivot table experience,
• 00:12 which is why this module is gonna be a very quick review
• 00:15 of creating basic pivot tables and doing some minor formatting.
• 00:18 If you've never worked with pivot tables before or
• 00:20 you're not very comfortable with them, this might be a little fast.
• 00:23 And I highly suggest that what you should do is actually go and
• 00:27 take the GoSkills course on pivot tables.
• 00:30 That's a much more relaxed course that will actually take you through from
• 00:33 never having used a pivot table to being a pivot table ninja by the end.
• 00:37 Regardless, you should still watch this, but
• 00:40 hang on cuz it might be a little quick, and here we go.
• 00:43 All pivot tables start with data, which we have on the source data page right here.
• 00:47 Its format is a nice table, which is perfect.
• 00:50 So I'm gonna say Insert Pivot Table, and
• 00:53 I'm going to put this on an existing worksheet,
• 00:56 right here into my wireframe area, where it says Pivot Table with Categories.
• 01:01 Now, it will ask me to replace it because I've drawn my wireframe, so
• 01:05 it's gonna overwrite that data setup, not a big deal.
• 01:08 I'm now gonna quickly go and grab my category and drop it on here, so
• 01:12 I now have a unique list of categories, whether that dataset is 76 rows or 76,000,
• 01:17 and I'm gonna drop units and sales directly onto my pivot table.
• 01:23 And that looks pretty good, except that I wanna make some formatting changes, so
• 01:27 I'm gonna go and set this one up as Units Sold, and the next one as Sales dollars.
• 01:33 Of course, when I try and hit Enter on this, it's gonna tell me that pivot table
• 01:37 field name already exists because it shows up over here.
• 01:40 But not to be outdone, I'm gonna put a space after this because this makes it
• 01:44 unique so that I can commit that to a cell.
• 01:47 I'm now gonna quickly go and say, let's go make some changes here.
• 01:51 The value field settings for number format.
• 01:54 I'd like a number with 1,000 separators and 0 decimals, and we'll say OK and OK.
• 02:01 And I know that those now look formatted nicely, and
• 02:03 any new data items will get added to the data set with the same format.
• 02:07 I'm gonna do the same thing for the next guy over.
• 02:10 All right, so here we go, 0 decimal places with a 1,000 separator,
• 02:15 and OK, and OK, and this pivot table actually looks pretty good.
• 02:19 I'm gonna change my row label at the top here to say Category.
• 02:24 And I'm pretty much happy with this pivot table except for one small thing, and
• 02:28 that is that I want to make sure that when it updates,
• 02:31 the categories or the columns don't change size.
• 02:33 So I'm gonna go right click Pivot Table Options and
• 02:37 uncheck Autofit column widths and updates so
• 02:40 that it always stays with the column widths exactly as I've set them up.
• 02:45 So that's the first pivot table.
• 02:47 Let's create a second.
• 02:49 I'm gonna go back to my data and say, Insert Pivot Table.
• 02:52 Once again, I'll choose Existing Worksheet, and we'll go and
• 02:56 put this in the location where I've chosen to put my pivot table with the Month End,
• 03:00 Vendor, Sales and Profit.
• 03:01 I'm gonna say OK, and accept the warning that's telling me that's
• 03:05 it's gonna overwrite what I actually have in this cell.
• 03:07 I'm fine with that, and there we go.
• 03:10 Now, once again, I'm gonna build another pivot table.
• 03:13 The concept here, though, is we're gonna go with Month End on Rows,
• 03:17 because I want to see my data by month.
• 03:19 I'm then gonna grab the vendor and drag that underneath months so
• 03:23 I can see that my months are segregated by the individual vendors.
• 03:26 But I'm already deciding that I don't like the format of this guy here.
• 03:30 So I'm gonna go to Pivot Table Tools > Design, and
• 03:34 I'm gonna go to my Report Layout, and I'm gonna show it in Outline Form.
• 03:39 And what you'll notice is that this first column right now, and it's all combined,
• 03:43 is gonna split into two separate columns so that it looks a little bit nicer.
• 03:48 I'm now gonna go and drag my sales and
• 03:52 my profit onto my pivot table so I have columns for both of those.
• 03:56 And now I'm gonna make, again, some formatting changes with this.
• 04:00 I'm gonna right click on my sales, go to Value Field Settings.
• 04:04 I can change my title up the top here, sales dollars.
• 04:07 Again, I'm gonna need that space so
• 04:09 it doesn't conflict with what's showing up in the field list.
• 04:11 And we'll say number format, number, 0 decimal places
• 04:17 with a 1,000 separator, and say OK and OK again.
• 04:22 On my profit, same thing.
• 04:24 Right click Value Field Settings.
• 04:26 I'm gonna go in, call this one Profit.
• 04:29 But this one, I think I'm gonna add dollar sign to it.
• 04:32 That does not conflict here, so I'm good to go.
• 04:34 We'll say, number format, number, 1,000 separators,
• 04:40 no decimals, and that looks good, and OK.
• 04:43 The last thing, I kind of want my subtotals to
• 04:46 show at the bottom of this group, so I'm just gonna move those around as well.
• 04:51 That's on Pivot Table Tools > Design > Subtotals, and show at bottom of group.
• 04:56 So this looks quite nice now.
• 04:58 I'm much happier with the way the pivot table looks.
• 05:00 Again, I'm gonna right click on it, say Pivot Table Options,
• 05:04 uncheck the Autofit Column Widths, and say OK, because now I can resize
• 05:10 this to look a little bit closer to what I actually want to see on a regular basis.
• 05:15 I'll just widen these two columns up a little bit here as well.
• 05:17 There we go.
• 05:19 The final thing that I might do with this is actually collapse the dates.
• 05:23 So I'm gonna right click here, say Expand Collapse, and collapse the entire field.
• 05:28 This will give me a nice little table that I can drill into and
• 05:32 out of when I want to see the detailed records.
• 05:35 And I now have a good dashboard
• 05:38 set up that's starting to take some shape that I'm happy with.
• 05:41 And again, I realize that was a quick run through here, but again,
• 05:44 it was intended to be a review.
• 05:46 If any of that stuff is new, there's a fantastic pivot tables course in our
• 05:50 catalog that you really should check out.

Lesson notes are only available for subscribers.