Locked lesson.
About this lesson
Various ways to display numbers in the values area of a PivotTable.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Value Field Formats.xlsx199.8 KB Value Field Formats - Completed.xlsx
200.5 KB
Quick reference
Value Field Formats
Various ways to display numbers in the values area of a PivotTable.
Where/when to use the technique
When you need to change the way values are displayed.
Instructions
Changing number formats
- To change a number format
- Right-click a cell in the PivotTable values area
- Choose Value Field Settings > Numbers
- Difference From in columns
- Choose your number format > OK
- All values for the current column (only) will be changed
Showing values instead of blanks
- To fill blank cells:
- Right-click the PivotTable
- Choose PivotTable Options
- Check the box next to “For empty values show:”
- Place a value (like 0) in the box
Hiding error values
- To hide errors:
- Right-click the PivotTable
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)
- Be aware:
- This hides all errors, including subtotals and grand totals (it doesn’t fix them)
- If the error is in your data source, you need to clean the data
- 00:04 In this video,
- 00:05 we're going to look at controlling the display of the values in the values area.
- 00:09 So this area of the pivot table right here.
- 00:12 Now, one of the challenges you'll see here is we've got a few different
- 00:14 things going on.
- 00:15 We've got some #DIV/0 errors, we've got some blanks and
- 00:18 we have inconsistent formatting for our decimal places in these columns.
- 00:23 And that's the part that I'm going to deal with first,
- 00:24 because this is the thing that would drive you kind of crazy.
- 00:27 We can see that we have whole numbers showing all the way down the first
- 00:29 Units column.
- 00:30 In the second Units column,
- 00:32 we have a 66.5, and that's really not the way we want to see things.
- 00:36 So I want to change this so
- 00:37 that it actually shows up with a consistent format.
- 00:40 Now, what a lot of people will do is they'll highlight a set of numbers and
- 00:42 they'll just hit it with a comma style at the top.
- 00:44 And this is absolutely not the way you want to approach this because sometimes
- 00:47 when you refresh your pivot table, those number formats don't stick.
- 00:51 So the way that we want to do this is click a single cell inside the column,
- 00:55 right-click and choose Value Field Settings.
- 00:58 Now of course, you can rename your column here, change your aggregations and
- 01:02 what not, but you can also choose to go into the number format.
- 01:05 And when you do that,
- 01:06 it brings up a very specific dialog page from the Format Cells dialog.
- 01:10 And in here, I'm going to change this to a number with thousand separators and
- 01:14 I'm going to drop the decimals off it.
- 01:16 And when I say OK and OK,
- 01:18 you'll notice that it actually rescales every single column for Units and
- 01:23 puts it in a consistent format all the way across, so that's pretty cool.
- 01:27 The nice thing about this, when the pivot table refreshes, whether it shrinks or
- 01:31 grows, you will always get this exactly right for the Units column.
- 01:34 But notice that Sales did not have that applied.
- 01:36 And that's because we might want Sales to look different.
- 01:39 So I'm going to show you another way to do this.
- 01:41 And that's just to right-click and go to, not Format Cells, this one won't work for
- 01:45 us, that's similar to the comma style, but Number Format.
- 01:49 Number Format launches us straight into this page where we can now say, hey,
- 01:52 this is going to be a Currency.
- 01:54 We'll drop the dollar sign off, and let's put it with two decimals.
- 01:58 And now we can say OK,
- 01:59 and every one of those columns goes into a consistent format.
- 02:02 The next thing I want to look at is these errors.
- 02:04 And I want to show you what's going on in this one.
- 02:07 And to do this, I'm actually going to pull Sales off the pivot table right now,
- 02:10 just to show you exactly what's happening here.
- 02:12 Notice that we have some kind of an error that's showing up in our Coolers and
- 02:16 Ciders here.
- 02:16 It's causing some cross-totaling errors as well.
- 02:19 Now I do have some options for my pivot table, I can right-click and
- 02:22 go to PivotTable Options.
- 02:24 And in here I can say for error values, show and leave this blank.
- 02:29 And at that point, it'll actually replace all of the errors with blanks.
- 02:32 The problem is, it's no longer cross-totaling properly.
- 02:36 And the reason for this is because the error is actually in my source data,
- 02:39 and that's a bad thing.
- 02:41 So if I come back here, and I go and take a look at my Units column, and
- 02:45 I'm just going to go and make this a little bit bigger here and scroll down.
- 02:48 We can see that somewhere near the bottom, we have a DIV/0 error in here.
- 02:53 So I'm going to go and filter my source data.
- 02:56 And I'm going to try and treat this in the source data right away.
- 02:59 If we take a look at it, it's got 1 divided by 0, that's kind of weird.
- 03:02 Let's get rid of that, just say Enter.
- 03:05 Bring it back to a value of 1, we'll now clear the filter off of this.
- 03:08 I'm going to go back to my report, right-click and Refresh it.
- 03:13 Now, there are times when you'll get a #DIV/0 error because you've divided one
- 03:17 column by another in a calculated column, so this is really useful for doing that.
- 03:21 You can also, when you're working with difference from fields,
- 03:23 get a #NULL error or things like that.
- 03:25 So this is really good for suppressing those.
- 03:27 But if your error is in your source of data, you don't really want to mask it
- 03:30 here, you want actually treat it right at the source data.
- 03:33 The next thing I want to focus on is these holes that are in the pivot table.
- 03:36 They look like they're empty, I'd really rather put something in there.
- 03:39 So I'm going to go right-click, PivotTable Options, and I'm going to choose For
- 03:43 empty cells show and we're going to put in a 0.
- 03:45 As a matter of fact, I could also put in a 0 for
- 03:48 my error values if I wanted to do that as well.
- 03:51 And now when we say OK, boom, just like that.
- 03:54 It's all filled in nicely, and
- 03:55 I've got a nice pivot table that's actually displaying properly.
- 03:58 I'm now going to go and grab my Amount, put it back on the pivot table.
- 04:02 Of course, it doesn't stick with the settings that I set before.
- 04:05 So I'm going to have to go and reproduce those.
- 04:08 So we'll go right-click, Number Format.
- 04:10 Set this again to Currency.
- 04:12 But the key thing I want you to recognize here is that all of the holes that were
- 04:15 showing up in here, as well,
- 04:17 are all now filled because that pivot table option we set was actually global.
Lesson notes are only available for subscribers.