Formatting PivotTables

Quick reference

• 00:04 All right, let's create a new PivotTable here.
• 00:06 So, I'm going to click inside my data,
• 00:08 verify that it's already in a table by the fact that the table design tab comes up
• 00:12 and we can see that the table's name is Pet Store.
• 00:15 Now one of the nice things about this is that right on the Table tab,
• 00:18 we can also see that we have a summarized with PivotTable button.
• 00:20 So I'm just going to click that, and notice that it picks up the Pet Store
• 00:24 range, no dollar signs, everything is good.
• 00:27 I'm going to go and drop this in an existing worksheet right beside my
• 00:31 original table right here and say, OK.
• 00:33 And here we go, we get a frame which may be called PivotTable one, or
• 00:37 PivotTable two.
• 00:38 This number increments depending on how many PivotTables you've created within
• 00:42 a single open instance of Excel.
• 00:44 Now, what I'm going to do is I'm going to configure my PivotTable.
• 00:48 We're going to grab Sold By and put it on rows.
• 00:51 We're going to put inventory item underneath that, there we are.
• 00:55 So we now see our individual items by salesperson.
• 00:59 I'm then going to go and put price on my values and
• 01:02 cost on my values underneath price, so the PivotTable looks like this.
• 01:07 Now, that's great, but it's not my favorite way to actually look at my data.
• 01:12 And this is the subject of this video is to look at how can we make our PivotTables
• 01:17 look a little nicer.
• 01:18 So what we're going to do is while we're inside our PivotTable,
• 01:21 so if we click outside we click back in.
• 01:24 If we go to the PivotTable Design tab,
• 01:26 you'll notice that we have a few different options.
• 01:29 We can change the colors of our Pivot tables if we want to,
• 01:31 I'm not going to worry about that right now.
• 01:33 We can change some of the styles, turn on banded rows, and things like that.
• 01:37 But again, I'm not really interested in doing that either.
• 01:39 What I'm most interested in is these, the Report Layouts.
• 01:43 Right now the view that we have is called Compact Form.
• 01:46 And this is where we have our sold by and inventory items in the same column,
• 01:50 column H.
• 01:51 I'm going to switch this out to tabular form, and
• 01:54 you'll notice that this breaks are sold by and
• 01:57 inventory item into separate columns and puts a subtotal at the bottom.
• 02:01 So that's kind of nice.
• 02:02 A little bit different, a little more tabular format.
• 02:05 I'm going to go and change this again though,
• 02:07 I'm going to move it to Outline Form.
• 02:09 And what you can see is the outline puts the subtotals at the top, but
• 02:13 it gives us a little bit of breathing room here,
• 02:15 before we move into our next data set at the bottom of these sales items.
• 02:19 Now that's good and everything else.
• 02:21 I actually kind of liked this little line of breathing room that I've gotten here,
• 02:25 this white line, so that Fred is above the adorable kitty cat.
• 02:27 But I'd really rather have my subtotals at the bottom.
• 02:31 So that's what we can do here is go to subtotals and
• 02:33 show at the bottom of the group.
• 02:35 So now you can see we've got Fred with some whitespace,
• 02:38 we've got our inventory items, and then we have our grand total below.
• 02:42 Now, the big difference between this and Tabular Form, if I go back to
• 02:46 Tabular Form, my PivotTable subtotals will be at the bottom, but
• 02:51 notice that Fred is on the same line as the adorable kitty cat.
• 02:55 So I'm just going to go and
• 02:56 undo this because I prefer to see my PivotTables look this way.
• 03:00 So that's our PivotTable layout,
• 03:02 now I want to get into controlling a little bit more of what's going on.
• 03:05 I'm not really a big fan of the name here, sum of price.
• 03:10 So what I want to do is I want to control that and
• 03:12 I also want to control the number format.
• 03:15 So let's start with the name here.
• 03:17 The first thing that we can do is we can right click on this, and
• 03:20 we can choose to go to Value Field Settings.
• 03:23 And inside here you'll see that we have sum of price.
• 03:25 So I could go and change this to just say price.
• 03:29 And I'm going to say, OK,
• 03:30 and we're going to get an error that the PivotTable field name already exists.
• 03:34 And this is the challenge with this is that you cannot provide the exact same
• 03:38 name as the original column, price, that exists in our PivotTable.
• 03:41 And this is a little frustrating because I know this is the sum of price.
• 03:45 So, here's how you can get past this.
• 03:47 You can put a space at the end or at the beginning,
• 03:51 it looks the same and yet it gives us what we need, okay?
• 03:56 Just one word of advice.
• 03:57 If you're going to do this and you're using spaces,
• 04:00 if you're going to send it to somebody on Mac Excel, you need to put the space
• 04:03 at the beginning because the Unix operating system trim spaces from the end.
• 04:07 Now I'll show you another way that we can do this is we can actually just replace
• 04:10 the value inside the header.
• 04:11 So if I want to type in space cost and hit Enter, that will work as well.
• 04:17 Now for number formats,
• 04:18 what a lot of people will do is they'll actually highlight this.
• 04:21 And then they'll go to the Home tab and
• 04:23 they'll choose to put their comma style on just like this.
• 04:26 Now there's a big problem with this, and that is that when you refresh the data
• 04:30 in a PivotTable that doesn't actually stick, so you don't want to do that.
• 04:34 Instead, if you go right click and go to Value Field Settings,
• 04:38 you'll find that we have a Number Format tab.
• 04:40 Notice that I've selected one cell and I can go and say, let's make this a number.
• 04:45 We'll drop the decimal points off and put a thousand separator on there.
• 04:49 And we'll say, OK, and then we'll say, OK.
• 04:51 And even though I've only done one cell,
• 04:53 it's actually applied it to everything all the way down the table.
• 04:56 I'm going to do the same thing again here.
• 04:58 We're going to go right click Value Field Settings, Number Format,
• 05:03 change it to a number with 1000 separator and drop these off to zero.
• 05:07 And there we go, and we'll say OK.
• 05:09 Now what you'll notice as I'm doing this though, is the column width shift.
• 05:12 This is a little frustrating too,
• 05:14 because I might want these columns to be this wide.
• 05:17 And the problem is, every time I refresh my PivotTable, they go back to narrow.
• 05:23 There is a feature here, and this is done for a very specific reason.
• 05:27 Actually, I should show you that first.
• 05:29 When we actually make this too short to show the values, they get hash marks and
• 05:32 the Excel team said, hey look, people are going to freak out if the column's not
• 05:35 wide enough and we put hashes in there.
• 05:37 So we'll automatically resize it to show the value of these things.
• 05:41 But I know that I want to make my columns as wide as possible here, so
• 05:44 that's never going to happen.
• 05:46 And there's a setting we can do to override this and
• 05:49 turn off that auto resize behavior.
• 05:51 So if you right click on your PivotTable and go to PivotTable Options down near
• 05:55 the bottom, what you'll find in here is an option to Autofit column widths on update.
• 06:00 We're going to turn that off.
• 06:02 And now what you'll see is when I go data refresh all,
• 06:05 my PivotTable column widths stay exactly as they should.
• 06:08 So there you go.
• 06:09 Those are the tricks, main basic tricks of formatting your PivotTables.

Lesson notes are only available for subscribers.