Creating Basic (non-Power) Pivot Tables

Quick reference

• 00:05 In this module, we're gonna start just a quick basic pivot table review for
• 00:09 non-power pivot tables.
• 00:11 And what I wanna call out is before we get started here,
• 00:14 we have a beautiful set of data that looks like it's perfectly ready to be pivoted,
• 00:18 except for one small problem.
• 00:20 You'll notice it has all the characteristics of good data.
• 00:22 It's got a great header row.
• 00:24 It's got consistent data types down the column.
• 00:27 These guys are all dates.
• 00:28 These guys look like they're all numbers formatted as text.
• 00:31 These guys are all decimal numbers and we've got a bunch of text here.
• 00:35 With no blank row between the header and the data,
• 00:38 everything is perfectly ready to be setup.
• 00:39 What we need to do before we pivot this,
• 00:42 it's a best practice to make sure that it's a proper Excel table.
• 00:45 So we'll click inside the table somewhere and then we'll make the choice of what
• 00:49 color we want our table to be which in this case, I'm gonna choose blue.
• 00:53 Notice that it picks up the data range for us,
• 00:55 because it had no blank row between the headers and the data.
• 00:58 It can grab the entire thing, which is great and
• 01:00 it's also identified immediately for me that my table has headers.
• 01:04 It was able to do this due to both the date and the amount columns having numbers
• 01:08 down them that are not formatted as text, because the header's text.
• 01:13 The values are numeric.
• 01:14 It gets this choice right.
• 01:16 If everything were text, in other words, if the date and
• 01:19 amount columns weren't here, I would have to check this box manually.
• 01:22 Because it wouldn't know.
• 01:24 We're now going to say, OK.
• 01:27 This puts a table format on it and
• 01:29 you'll notice that we are immediately on the table tools design tab.
• 01:33 Now, one of the things that was missing in that initial dialogue was the ability
• 01:36 to name a table and this is the most important thing you can
• 01:39 do once you've created a table is to give your table a name.
• 01:43 I'm gonna call this one transactions and
• 01:45 that gives me a nice named table which I can now get back to at any time by just
• 01:50 clicking on the little drop-down list here, and there we go.
• 01:54 Now, the key thing here is we've got our data in the top of the form.
• 01:58 Everything is great, it's in a format and in a table which is perfect.
• 02:01 I'm gonna go now say, insert pivot table.
• 02:04 Notice there's no dollar signs in the table name range here, which is excellent.
• 02:08 It's using the name and that's fantastic.
• 02:11 I'm now gonna go and drop my pivot table onto an existing worksheet and I'm gonna
• 02:15 put it into cell B7, the green cell on this next worksheet and we'll say, OK.
• 02:22 So this creates me the pivot table frame that I can now go and start slicing,
• 02:27 and dicing the way I want.
• 02:29 So I'm gonna drag class onto my rows and
• 02:31 that will show me all of the unique values that were in that set of data.
• 02:35 I can drag group into here as well and it'll actually segregate
• 02:39 these things quite nicely for me and I could put say, department on my columns.
• 02:44 And I could go and throw amounts into my values, and
• 02:47 it's gonna summarize everything up for me very nicely.
• 02:50 I could then say, I wanna see more detail.
• 02:52 Maybe I'll throw account name in here.
• 02:54 I'll think, well, that's kind of interesting.
• 02:57 But maybe that's too much detail, so maybe I'll just take it back off.
• 03:01 So have the ability to move things around if I wanna see,
• 03:04 maybe the department underneath the group.
• 03:07 So I can break it down this way or I decide not to and
• 03:10 I decide to move it back.
• 03:13 The nice thing with the pivot table,
• 03:15 of course is that we have the ability to quickly move data around into different
• 03:19 formats in order to see it the way that we want.
• 03:22 And if we want multiple reports, we can go back to the data tab.
• 03:26 Create a new pivot table and we can have multiple views off the same data.
• 03:30 I like to think with pivot tables is kind of the rubric's cube of data, but
• 03:34 I can slice and dice any way I want.
• 03:36 Now the one thing that is really important about this though,
• 03:40 that we have to remember is that the pivot table is not connected live to our data.
• 03:46 So if we look at this right now, I'm just gonna go and take this information here.
• 03:50 I'm gonna copy the grand total, Ctrl+C.
• 03:52 I'm gonna go right-click up on here and paste it as a value and
• 03:55 then I'm gonna format it as a number, so we can read it a little easier.
• 03:59 So it's 257,000.
• 04:02 I'm gonna go back to the data tab.
• 04:04 And on one of these values here, this sports wear one here.
• 04:07 I'm gonna throw a whole bunch of nines in front of this thing just to try and
• 04:11 make this number really, really big and
• 04:13 it extends it out to be too big to see in the cell.
• 04:15 So if I go back and format it, you can see, here we are.
• 04:17 I'm gonna go back to the report.
• 04:21 Now, it should be pretty obvious that we've changed this.
• 04:23 And if I go back to the report,
• 04:25 you can see that we're still looking at the 270,000.
• 04:30 So the key to remember here and this does not change with regular pivot tables or
• 04:35 power pivot tables, what we wanna do is go to data and
• 04:39 go to Refresh All and Refresh All will force this to update.
• 04:43 Now again, the number format is looking a little bit ugly on this guy here.
• 04:46 So we'll just reformat that for a second.
• 04:48 There we go.
• 04:49 Now, it's showing up quite nicely to show that we actually had something happen.
• 04:54 If I wanna update it again or set it back to where it was, I can go back to data or
• 04:58 strip off all these nines enter.
• 05:03 When I go back, notice the report is still the way it was.
• 05:06 I can also if I want to right-click and do a refresh on the pivot table itself, and
• 05:11 that will update every single pivot table that is built off the original data table
• 05:16 as well.
• 05:16 So biggest thing to remember, lots of dragging dropability and
• 05:20 remember that your data is not setup live.
• 05:22 You must refresh it manually.
• 05:24 That's the one big thing that we have to remember with pivot tables.

Lesson notes are only available for subscribers.