Locked lesson.
About this lesson
Learn to control formatting and other options in PivotTables.
Exercise files
Download this lesson’s related exercise files.
Formatting PivotTables.xlsx53.2 KB Formatting PivotTables - Completed.xlsx
54.1 KB
Quick reference
Topic
Formatting PivotTables.
Description
Controlling formatting and other options on PivotTables.
Where/when to use the technique
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 PivotTable with the following setup:
- Rows: Inventory Item
- Columns: Sold By
- Values: Commission
Formatting numeric columns
- Select any cell in column B (the Sum of Commission column)
- Right click, choose “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
- Drag Sold By into the ROWS area, above Inventory Item
- Select B3 and enter “Commission”.
- 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 PivotTable fields.)
Changing report layouts
- Go PivotTable Tools --> Design --> Report Layout --> Show in tabular form
Controlling column widths
- Go back to the source data and update F4 to read 1,000,000,000
- Return to the PivotTable, right click it, choose Refresh, and notice column C expands to fit
- Drag column C slightly wider, right click the PivotTable and refresh
- Notice it resizes again!
- Right click the PivotTable, choose PivotTable Options --> Layout --> “Autofit column widths on update”
- Resize Column C, right click the PivotTable and refresh. It stays the same size
- Return to the source data, set F4 back to 1.35
- Return to the PivotTable, right click it and refresh, and the column width doesn’t change.
- 00:04 In this video we're going to look at some of the different formatting that we can
- 00:07 apply very quickly to a pivot table as well.
- 00:10 Now, you'll notice right off the bat when I look at this particular pivot
- 00:15 table my decimal places are all over the place, and that's really not ideal.
- 00:18 I'd like that to look a little bit better, so in order to fix that,
- 00:22 what I'm gonna do is I'm gonna right click on the pivot table, and
- 00:25 I'm going to go down to field settings.
- 00:28 Now before we go into the actual number formatting, you'll notice that we actually
- 00:31 have the option to change the summarize by setting from sum to something else.
- 00:35 So if I wanted to get, say, a count on the number of adorable kitty cats that
- 00:40 were sold, I could just change this to count, and when I say OK, the pivot
- 00:44 table will re-aggregate and show me how many individual items have been sold.
- 00:49 So that's kind of a neat thing to see.
- 00:52 Again, if I go right click and go to field settings,
- 00:55 I could quite easily set this back to sum, and when i click OK, it'll actually put
- 00:59 the sum back in there to give me the count of how much commissions we actually had.
- 01:04 Now, what I'm going to do at this point, is I'm going to click on Number.
- 01:08 And I'm going to go to Accounting, and I'm gonna change this to not use any
- 01:14 currency indicator, and we'll go with a two decimal place.
- 01:16 You can see the sample of how my number should look.
- 01:19 So when I say OK, we're gonna flip back from the count of commissions to the sum
- 01:23 of commissions, and you'll notice now that my decimal places are all nicely lined up.
- 01:29 Now why would we go through field settings to do this rather than just go to the Home
- 01:32 tab and format this with whatever number style we want?
- 01:35 The answer to that is that when you actually go and control your field setting
- 01:39 things and go through and set up your numbers the way that I just did,
- 01:42 that's now in the pivot table's style.
- 01:45 So as the pivot table expands or shrinks,
- 01:49 all new numbers will actually inherit that particular style for that column.
- 01:52 So that is an important thing because that may not happen if you
- 01:55 are using the home tab to deal with things.
- 01:58 All right, let's look at some more configuration stuff here.
- 02:01 The next piece that I wanna do,
- 02:02 is I wanna move one of the fields on my PivotTables around.
- 02:05 But unfortunately, you'll notice that even though I'm clicking outside and
- 02:08 back in the PivotTable, my field list didn't come up.
- 02:11 And that's because I'd clicked the little red x to dismiss it earlier.
- 02:13 So what I need to do is I actually need to go, and on the PivotTable Analyze tab,
- 02:18 I'm gonna click field list, and that will bring it back.
- 02:22 So now I have my field list back,
- 02:24 I can start playing around with the different fields in different places.
- 02:27 So what I'm going to do is I'm gonna drag sold by.
- 02:30 I'm gonna put it above inventory item.
- 02:34 Oops, I'm gonna try and put it above inventory item.
- 02:36 There we go, not quite, we'll drag inventory item below then.
- 02:39 There we are. You might have to
- 02:41 fight with it a couple times but eventually you'll get there.
- 02:43 So, a couple observations here.
- 02:47 You'll notice that our numbers subtotal above are individual data.
- 02:52 So here's the total for Fred's sales here, or his commissions.
- 02:57 What if I wanted those down below?
- 03:00 Well, to do that I go to the design tab.
- 03:03 And I'm going to subtotals, and I'll say Show Subtotals at the bottom of the group.
- 03:09 And that will actually flip it down.
- 03:10 So it says Fred Total now, and he's showing underneath.
- 03:13 We've also got the ability to expand and collapse these sections.
- 03:17 You'll notice that we now have the little plus minus icons.
- 03:20 We can click on those to collapse an individual person or
- 03:24 click on the little plus to open it up again.
- 03:26 Or we could right click, we could go to group and outline, and
- 03:30 we could hide details, which will actually hide everybody's details,
- 03:34 allowing us to drill into a specific person.
- 03:37 Or again right click and go to Group and
- 03:41 Outline, Show Detail will expand those rows for the entire PivotTable.
- 03:45 So that 's kind of a nice little trick that you might want as well.
- 03:49 What about the ugly column header here where it says Sum of Commission?
- 03:54 It would be much nicer to have that read Commission, so let's try that.
- 03:57 Let's go with it. We'll see.
- 03:58 C-O-M-M-I-S-S-I-O-N.
- 04:01 And Enter.
- 04:03 And it will tell you that the pivot table field name already exists,
- 04:06 which is a little frustrating, but that's true.
- 04:08 It actually shows up over here in the field well list on the side.
- 04:12 But we can fake Excel out with this, because the name needs to be unique.
- 04:16 So if you put a space in front of it and say okay, it'll actually accept it.
- 04:21 And it even renames it in the Values area over here.
- 04:24 So that's kind of a cool little trick too to make things look a little nicer.
- 04:28 One other thing that you may want to do,
- 04:30 is you may want to change the layout of your report.
- 04:32 Right now, everything's nested in column A.
- 04:35 We see Fred and then we indent a little bit to adorable kitty cat.
- 04:38 But we have the ability to actually go to our report layout and
- 04:42 change our report layouts into some different forms.
- 04:45 So in this case here, if we go to Tabular Form, you'll notice that it actually
- 04:49 puts Fred in one column, Inventory Items in the next column, and our Commissions.
- 04:54 And of course, we could change different fields around,
- 04:56 however we'd like to look at it.
- 04:58 If I put my Sold By over here, it looks pretty much like it did before.
- 05:02 I could put it underneath or above Inventory Item, and
- 05:05 it's gonna show a different view, but
- 05:08 regardless, it's a slightly different format that's not nested anymore.
- 05:13 There's obviously more things that you can do with this as well.
- 05:15 One of the things that you may want to do is hit the repeat all item labels,
- 05:19 which would fill Fred all the way down that particular column.
- 05:22 So, that's a few more formatting options to work with inside a pivot table.
- 05:27 One last one that we need to focus on is when you work with a data set,
- 05:32 and you end up with a huge number that ends up in your data, and
- 05:37 you go over and you refresh your Pivot table.
- 05:40 You'll notice that the column width changes, and it does that so
- 05:43 that you can always make sure that everything fits inside.
- 05:46 The problem is, though, when you get everything set the way you want, and
- 05:51 then you refresh your data, it will also shrink back down, and that's not ideal.
- 05:56 So there's a setting to fix that.
- 05:58 If we go into PivotTable Options, and we go to the Layout tab,
- 06:03 we can choose to not autofit the column widths on update.
- 06:07 And at this point, if we go bank and we say, let's widen that up again,
- 06:12 right click and refresh, the column width won't change.
- 06:16 Even when we go back and set our original data back to what it was supposed to be.
- 06:21 And we go over to our pivot table, right click, and
- 06:25 refresh, the column width will stay exactly as we designed it.
Lesson notes are only available for subscribers.