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.xlsx42.1 KB Formatting PivotTables - Completed.xlsx
49.3 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 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 PivotTable fields.)
Changing report layouts
- Click in the PivotTable, go PivotTable Tools --> Design --> Report Layout --> Show in tabular form
- Go PivotTable Tools --> Design --> Report Layout --> Show in outline form
- Go to PivotTable 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 PivotTable, right click it, choose Refresh, and notice the columns expand to fit
- Drag the columns slightly wider, right click the PivotTable and refresh
- Notice it resizes again!
- Right click the PivotTable, choose PivotTable Options, uncheck the box next to “Autofit column widths on update”
- Resize the columns again, right click the PivotTable 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 PivotTable, right click it and refresh, and the column width doesn’t change.
- 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.