Locked lesson.

Upgrade

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:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Formatting Pivot Tables.xlsx
20.1 KB Formatting Pivot Tables - Completed.xlsx
33.8 KB Formatting PivotTables - Extra Practice.xlsx
55.3 KB

Quick reference

Topic

Formatting PivotTables.

Description

Controlling formatting and other options on PivotTables.

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 PivotTable with the following setup:
    • Rows: DRG Definition, Provider State
    • Values: Total Medical Payment

Formatting numeric columns

  • Select any cell in column B (the Sum of Total Medical Payments column)
  • 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
  • Drag DRG Definition into the ROWS area, and Provider State below it
  • Select B3 and enter “Total Medical Payment”
  • 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 tabular form

Controlling column widths

  • Go back to the source data and update L4 to read 10,000,006,511
  • 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, uncheck the box next to “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 L4 back to 6,511
  • Return to the PivotTable, right click it and refresh, and the column width doesn’t change
Login to download
  • 00:03 We can obviously do a lot of formatting the PivotTables as well. So we're going to look at that right now.
  • 00:08 And of course to do that we need a PivotTable. Let's knock up a quick one off of this particular set of data here.
  • 00:13 Go to Insert go to PivotTable
  • 00:17 and we'll put it on a new worksheet.
  • 00:20 What I'm going to do is I'm going to drag the DRG Definition on to Rows, I'm going to drag the provider State onto Rows and I'm going to drag
  • 00:29 the Total Medical Payments on to Values and there we go I've got a nice quickly built PivotTable.
  • 00:36 Now what kind of formatting can we do with this? Well right now we can see
  • 00:39 the decimal places are all over the place in our Values column here and that's no good.
  • 00:43 So let's go fix that. If we right click choose Value Field Settings, you're going to spend a lot of time in this dialog.
  • 00:51 Down on the bottom left there's a number format button which takes you to the format cells dialog stripped down to just show numbers.
  • 00:58 I'm going to click on accounting, change the symbol to none and drop off the decimal places
  • 01:04 and say OK.
  • 01:06 And when I say OK to that
  • 01:08 I get a nice consistent looking set of medical payments which is great.
  • 01:14 Now I don't really like the title of this particular one here: the Sum Total of Medical Payments is no fun I'd rather it said
  • 01:22 Total Medical Payments so if I want to do that I can do this in two ways. I can right click and go into Value Field Settings
  • 01:30 and I can change it here
  • 01:33 or if I'm feeling particularly lazy I can actually just go straight into the cell and say you know what? Let's go with Total Medical Payment.
  • 01:40 Knock that off and say OK. Oh and it comes back with an error. That's
  • 01:44 frustrating. The reason it's giving me an error is because Total Medical Payment is the name of the
  • 01:48 field in here. It needs to have something unique. So we say we can't do that but check this trick out.
  • 01:54 If I put a space after this and hit Enter.
  • 01:58 I just fooled it. It now thinks that it's different but it looks the same to me so that's a quick trick that you might want to use.
  • 02:06 Some other things we can do to this table. Remember if we click outside the table
  • 02:10 we're looking at a regular Ribbon here but as soon as we click inside our
  • 02:13 Pivot Table our PivotTable Tools tab comes up and in here there's a Design tab.
  • 02:19 And on that we can change the report layout. Maybe we want to look at this as more of a list
  • 02:23 format, we can choose to show it in tabular form.
  • 02:28 When we do that we can see that we've got our records, we've got a subtotal here,
  • 02:32 in this particular case because it's subtotalling in like every other row
  • 02:36 here for the most of them, I don't really want those. I'm going to go to to Subtotals and I'm going to turn them off.
  • 02:41 Do Not Show Subtotals and away they go.
  • 02:44 Of course now I've got gaps in my data. What this means is that it's the same as the line above. This is both going to say Extracranial
  • 02:51 Procedures this is just for Illinois, this one's for Washington, same thing happening down here.
  • 02:55 Well it would sure be nice if I could fill those in with some real headers so that it actually said what it was. We can do that.
  • 03:02 Starting in Excel 2010 we got the ability
  • 03:05 to actually Repeat All the Item Labels in our fields. So we can do that and there we go we've got a nice looking list.
  • 03:13 Now the other thing that's really important to understand here is the way that tables refresh. If you've gone in and set everything up so you got
  • 03:21 exactly where you want it and say that we want to set this column up to about this width.
  • 03:26 That's where we want it and then we right click and we refresh our data
  • 03:31 and the column would shrink again. Well that's really irritating.
  • 03:35 If I just had this named as say Payment
  • 03:41 and I right click and refresh my data.
  • 03:44 Now lets say I go over to my listing over here
  • 03:47 and I'm going to go and just increase the value in this cell.
  • 03:54 So this will be nice and long.
  • 03:58 When I refresh my list you'll see that this column again expands.
  • 04:02 So if I've set it up to where I want it,that's the maximum value it's ever going to be and I refresh
  • 04:08 it keeps on knocking it down it drives me crazy.
  • 04:11 So here's the thing we can do. We right click and we say PivotTable Options
  • 04:16 if we uncheck the Autofit column widths
  • 04:20 and at this point in time open that up and we refresh it
  • 04:26 it doesn't change.
  • 04:28 The nice thing to is that even if the values become lower,
  • 04:34 go back and get rid of this one here,
  • 04:36 go back to our PivotTable and refresh
  • 04:40 even though there's been a change to the source data it still doesn't change. So
  • 04:44 all that hard work that you've done on getting those column widths exactly
  • 04:48 right doesn't get lost. So again that is in PivotTable Options
  • 04:53 and it's the Autofit column widths on update.
  • 04:57 And that's the way you can make your basic formatting happen on your PivotTable.

Lesson notes are only available for subscribers.

Gift this course