Formatting PivotTables

Quick reference

• 00:04 We're now gonna focus on formatting PivotTables,
• 00:06 making them look a little prettier than what their default sort of comes out as.
• 00:11 So we'll start, we've got a nice table of data here that's formatted as a table,
• 00:14 I can see that because this one's got the table tools design tab showing.
• 00:18 I can go to Insert > PivotTable,
• 00:21 you'll notice the name of the table is prefiilled for me with Pet Store.
• 00:24 I'm gonna go and put this on an existing worksheet and
• 00:27 we'll drop in onto the example PivotTable worksheet in cell A3.
• 00:33 There we go, we have our PivotTable frame ready to go.
• 00:36 I can now start building my PivotTable.
• 00:39 I'll drag sold by into rows, and I'll put price into values, and
• 00:44 I'll put cost into the values area as well, so
• 00:47 we've got two different measures that are actually showing up there.
• 00:51 And now I started thinking maybe I want some more things on here.
• 00:53 Maybe I'll go and bring in my inventory items as well so
• 00:57 I can see a bit more detail.
• 00:59 And at this point I start looking at things, and I go,
• 01:02 you know this doesn't really meet with my standards and expectations for formatting.
• 01:06 Number one, it's kinda hard to understand maybe what the items are that are here,
• 01:11 because they're all in one column.
• 01:13 Even the salesperson and the individual sales items it sales.
• 01:16 Not a big deal if we've only got two columns of data, but if we got three or
• 01:19 four, this becomes more of a problem.
• 01:21 In addition, the number formats aren't exactly ideal for
• 01:24 me either because I like commas separating my thousands.
• 01:27 Now, you might be tempted to just go and grab this selection and go to the Home tab
• 01:32 and hit a comma style on it, but I'm going to get you not to do that and
• 01:35 the reason why is because when our PivotTables expand,
• 01:38 it may not actually bring in all that formatting.
• 01:41 So instead we have a slightly different way.
• 01:44 We're going to right-click on that column > Value Field Settings In this
• 01:50 box we have the option to set the column name, so I say well that's kind of cool,
• 01:55 maybe I can go with a custom name, and we're going to call this price.
• 01:59 Let's do that first and say OK.
• 02:00 And it says hey you can't do that because this name already exists,
• 02:04 it shows up in this field list, so you can't have it called the same thing.
• 02:08 I say all right, no problem, we can fake that out by putting a space after it.
• 02:11 And then if we say OK,
• 02:12 it now gives us a column that says price, that's price space but it looks the same.
• 02:18 Let's go back in now and change the number format.
• 02:21 So Value Field Settings again, we'll go to number format and
• 02:24 what we're gonna do is go to accounting.
• 02:27 I'll take the symbol off and drop the zeroes off cuz I don't need to see those,
• 02:32 and when we say OK, and OK, you'll notice that I now have my commas.
• 02:37 So I'm gonna do the same thing but this time I'm gonna change cost here.
• 02:41 I'm gonna just type this in a cell.
• 02:42 So we'll say cost and it says you can't do that,
• 02:44 it already exists, so we'll say, all right, fine, cost space.
• 02:48 There we go, that works.
• 02:50 And now a right-click > Value Field Settings > Number Format >
• 02:54 Accounting > None > Zero Decimals.
• 02:58 We'll say OK and OK and our number format looks nice there.
• 03:03 So that's good, I'm happy with this.
• 03:05 And the nice thing is if I were to ever separate this,
• 03:08 these formats now every time the price column appears in my PivotTable
• 03:11 will show in this format, which is cool.
• 03:13 In addition to this though, I want to look at this, I want to break this apart,
• 03:17 I don't want all of this information in one column.
• 03:19 So, in order to fix this, I'm going to go to the PivotTable tools design tab, and if
• 03:24 you don't see this, it's possibly because you're selected outside your pivot table.
• 03:28 This is another one of those contextual tabs
• 03:31 that shows up when you click inside a PivotTable.
• 03:34 Like your field list the only difference here is
• 03:36 when you click outside in your field list and you click back in your pivot table,
• 03:40 the field list comes back unless you've dismissed it.
• 03:43 If that's the case, no clicking will change it.
• 03:46 We can show it again by right-clicking the PivotTable and
• 03:48 saying show field list to bring this one back.
• 03:51 Regardless, we've got our PivotTable tools design tab.
• 03:55 I'm going to go change the report layout and
• 03:57 i'm going to flip it into what's called tabular form.
• 04:01 So, when you see this, what happens is we've got Fred and the very first record
• 04:05 for Adorable Kitty Cat and it moves our subtotals down below our data, okay?
• 04:10 So that's kind of an interesting view.
• 04:12 I'm gonna show you an alternate view now, which is Outline View.
• 04:18 When we look at Outline, the subtotals show above our data, but we have a little
• 04:22 bit of white space from Fred before we get into our actual sales items.
• 04:25 Remember in tabular form, Adorable Kitty Cat started the next row.
• 04:28 Now I look at this and I go you know, I don't like this,
• 04:30 I like my subtotals to show below, so lets make that happen.
• 04:34 We'll go to Subtotals > All Subtotals At Bottom Of Group, so the difference
• 04:39 between this now is it in tabular form while the subtotals show at bottom,
• 04:43 in outline form we have a little bit of a white space to actually separate things,
• 04:48 so that's kinda useful.
• 04:50 Now one more thing I want to show you is when we go and
• 04:53 we update our sales price to something really big.
• 04:58 So, we'l go with, what do we've got here?
• 05:00 We've got a million dollars.
• 05:02 If I go back and right-click and
• 05:04 refresh my PivotTable, watch the column widths, they change.
• 05:09 And this is kind of frustrating because if I go and
• 05:11 set my column widths and right-click > refresh, they change.
• 05:15 It always expands to try and get to the width required to show the values.
• 05:20 So we can change this as well.
• 05:21 We can right-click > PivotTable options,
• 05:25 uncheck autofit column widths on update, and say OK.
• 05:29 And now, no matter what I do to my columns when I right-click on my PivotTable and
• 05:35 hit Refresh, my column widths will stay
• 05:39 even if the data goes back to it's original form.
• 05:43 We right-click > Refresh, it stays exactly the way I set it.

Lesson notes are only available for subscribers.