Locked lesson.
About this lesson
Learn to control formatting and other options in PivotTables.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Formatting PivotTables.xlsx51.1 KB Formatting PivotTables - Completed.xlsx
58.3 KB
Quick reference
Formatting Pivot Tables
Controlling formatting and other options on Pivot Tables.
When to use
Having your data in tabular format is all very well, but we need to format it to display it in the most readable and approachable way.
Instructions
- Create a Pivot Table with at least one field in each of the ROWS, COLUMNS and VALUES area
Formatting numeric columns
- Select any cell in the worksheet that represents a value from the VALUES area
- Right click, choose “Value Field Settings”, and click the Number Format box at the bottom
- Choose Accounting, with no symbol and no decimals, then click OK until you return to the Pivot
- Select the header for the VALUES field in the worksheet and change the name to match the name of the original column
- After you receive the error, try again but add a space at the end. (Notice it renames the item in the values area of the Pivot Table fields.)
Changing report layouts
- Click in the Pivot Table, go Pivot Table Tools --> Design --> Report Layout --> Show in tabular form
- Go Pivot Table Tools --> Design --> Report Layout --> Show in outline form
- Go to Pivot Table Tools --> Design --> Subtotals --> Show all Subtotals at Bottom of Group
Controlling column widths
- Go back to the source data and update one of the values to a very large number
- Return to the Pivot Table, right click it, choose Refresh, and notice the columns expand to fit
- Drag the columns slightly wider, right click the Pivot Table and refresh
- Notice it resizes again!
- Right click the Pivot Table, choose Pivot Table Options, uncheck the box next to “Autofit column widths on update”
- Resize the columns again, right click the Pivot Table and refresh. It stays the same size
- Return to the source data, set the cell you changed back to its original value
- Return to the Pivot Table, right click it and refresh, and the column width doesn’t change.
- 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.