Formatting PivotTables

Quick reference

• 00:04 Now, that we know how to create pivot tables,
• 00:07 let's make them look a little bit prettier.
• 00:09 We're gonna start by building a new one right here.
• 00:11 So from my table, I'm gonna insert Pivot Table, and
• 00:14 you'll notice that this table is called PetStore.
• 00:17 I'm gonna go and create this on an existing worksheet and
• 00:20 drop it right here into H5.
• 00:22 And then I'm gonna make some nice configuration of my pivot table.
• 00:26 I think I'm gonna add Sold By.
• 00:27 I'm gonna put the inventory item on there, and
• 00:30 then I'm gonna add both the price and an additional measure, the cost.
• 00:35 So I get two items showing up in my values area.
• 00:38 And when I go on and scroll across on this now, you can see that it's not bad, but
• 00:42 it could be better.
• 00:43 There's things I don't like about this pivot table.
• 00:46 I don't like the fact that I've got multiple items in one column here,
• 00:50 both my Sold By and Inventory item are in one place.
• 00:53 And I'd like to break that apart into separate columns.
• 00:56 I'm also not a big fan of the column names.
• 00:58 Sum of Price, Sum of Cost.
• 01:00 I'd rather they just said Price and
• 01:02 Cost like the original column names were in the table.
• 01:05 And the number formatting is not the best either.
• 01:07 So let's see what we can do.
• 01:09 Let's start by going into a special area for
• 01:12 Pivot Tables called Value Field Settings.
• 01:15 And any time I wanna change the format of a column I tend to play in this area.
• 01:20 The first thing you'll notice is that I can assign a custom name to my
• 01:24 column instead of Sum of Price I could go and say let's do say Price.
• 01:29 And we'll say OK.
• 01:31 But unfortunately, we can't.
• 01:34 The field name already exists.
• 01:35 If you have the same field name in this area, it wont let you use it again.
• 01:39 Boy, I've got a trick for that.
• 01:41 We can come to Price and let's go with price space because you know what?
• 01:45 Price space is not the same as Price.
• 01:48 And when I say OK, it will actually allow me to apply that, so that's pretty cool.
• 01:53 Now, the next piece that we wanna look at is the number format.
• 01:57 Don't ever select a set of cells here and go to the home tab and
• 02:01 apply a number format.
• 02:03 Because when your pivot table refreshes and expands,
• 02:05 that number format may not go with it.
• 02:07 Instead you wanna click any cell in this area.
• 02:10 Right-click go to Value Field Settings.
• 02:14 In here there's a Number Format Tab, we're gonna go there and
• 02:17 you'll notice that we have the number formats that you're used to dealing with.
• 02:20 So we could go and say accounting, say put no symbol on it,
• 02:25 drop the decimal places off, and say OK and I'll say OK.
• 02:30 And even though I formatted one cell it formats all of them, so
• 02:33 as the pivot table expands and contracts it's gonna inherit that.
• 02:37 Can I change this right in grid?
• 02:38 Say go to Cost.
• 02:40 Well, not with this name.
• 02:42 But if I put a space after it it'll work.
• 02:46 I can also format numbers by using just the plain, old number format right here.
• 02:51 And this will also allow me to do the same thing and
• 02:54 have a field that will always update.
• 02:57 So never use the stuff from up top.
• 02:58 Always use the number format or the value fields settings area.
• 03:03 The next component I wanna look at is this column.
• 03:05 How can I split this apart?
• 03:07 The key thing you want now is you want to go to Pivot Table Tools Design.
• 03:11 Now, if you don't have this tab,
• 03:12 it's probably because you're not inside your pivot table.
• 03:15 This is a contextual tab as well.
• 03:17 When you're in your pivot, it will show up.
• 03:19 And now we can play around with it.
• 03:21 We have multiple report layouts that we can work with.
• 03:24 Notice we have the ability to show in compact form.
• 03:27 That's the form that you see right now where everything is squished into
• 03:31 one column for all of the row labels.
• 03:33 Let's try this one, tabular.
• 03:36 And notice this we now have Fred and
• 03:38 Jane in their own column with the inventory items.
• 03:42 And the sub tool at the bottom were I like them, so that's pretty cool.
• 03:45 I wanna look at another layout though, Report Layout, Outline Form.
• 03:51 So this is slightly different,
• 03:52 the sub total is moved to the top that's not where I like them.
• 03:56 So I wonder, can I push those down?
• 03:57 Well, sure, Subtotals, show has bottom of group.
• 04:02 And you'll notice now the sub totals show at the bottom.
• 04:05 So what's the difference between this and tabular form?
• 04:09 It's this white space line.
• 04:10 Tabular form doesn't have that, the adorable kitty cat would be showing right
• 04:14 here, outline with subtotals at the bottom looks much better.
• 04:17 Now, the final thing that I want you to recognize about your pivot table is that
• 04:21 if you go through and you make formatting changes to certain things.
• 04:24 Let's say that we want our columns set up like this, and
• 04:28 then I went right click and refresh.
• 04:30 The columns will automatically resize to the value width of the numbers.
• 04:35 Well, that's not exactly where I want to be.
• 04:37 I'd like to have something that sticks around.
• 04:41 Now, the reason is because when you throw in your $1.5 million for
• 04:45 your lovable kitten, Excel figured that it's a bad idea to actually show hash
• 04:49 marks across everything.
• 04:51 So they'll automatically fit the column with ups and
• 04:54 down to show what the vales are.
• 04:56 But I know my numbers, I know they're never gonna get that big and
• 05:00 I really want them to stay with this width because I want control of them.
• 05:04 Every time I refresh I want it to stay this way.
• 05:07 So we have that ability as well.
• 05:09 To do that, we're gonna go right click, we're not gonna use value field settings,
• 05:13 though this is one of the very common areas we go to.
• 05:15 We're gonna use the next one, Pivot Table Options.
• 05:18 And these are two of the big configuration areas you're gonna spend a lot of time in
• 05:22 when working with pivot tables.
• 05:24 The key piece, autofit column widths on update.
• 05:27 We're gonna uncheck that box and say OK.
• 05:29 And now, what you're gonna see?
• 05:31 Right-click, refresh and everything stays in exactly the spot that it's supposed to.

Lesson notes are only available for subscribers.