Value Field Formats

Quick reference

• 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.