Value Field Formats

Quick reference

• 00:04 In this video we're gonna look at how to format numbers inside a pivot table in
• 00:08 the values area so that they actually display a little bit better.
• 00:13 You can see here in the pivot table that I've got,
• 00:15 I've got categories down the left and I've got units and sales for
• 00:18 each individual week, so units and sales for week 2 and units and sales for week 3.
• 00:23 Now, one of the challenges that I have with this particular pivot table
• 00:26 though is that I really like my numbers to add up or to line up rather.
• 00:30 And you can see here, I've got $121 followed by 32.50 and
• 00:33 then my decimal points are.
• 00:35 not consistent.
• 00:36 So one of the things that I'd like to do is I'd like to change that.
• 00:39 Now, here's we go about doing that.
• 00:41 We'll right click on one of the cells in that column.
• 00:44 And we'll go down to the third from the bottom which is called value filled
• 00:47 settings.
• 00:49 When you do that, it'll bring you up the value fields setting dialogue.
• 00:52 You'll notice that you can change the name for your column.
• 00:55 You can change your aggregation here.
• 00:56 But we can also go and click on number format.
• 00:59 And when we do that,
• 01:00 it takes it into a very strip down copy of the format sales dialogue box.
• 01:04 My personal favorite is accounting.
• 01:06 I'm gonna take the dollar sign off, though, we don't need that, but
• 01:09 we are gonna force it to two decimal places.
• 01:11 We'll say okay, and we'll say okay.
• 01:14 And if you look at the pivot table, what just happened is, all of the items in
• 01:19 this sales column changed, as well as this column, and this column.
• 01:25 And this column, and so on.
• 01:27 But, it didn't affect anything in the units column.
• 01:30 It's only working for the column that I actually chose.
• 01:33 But, it gets replicated all the way across the pivot table.
• 01:35 That's one reason why it's much, much better than just going and
• 01:39 applying a number format to a specific column.
• 01:42 Now we can do the same thing for units.
• 01:44 Maybe we want them to end off in the single decimal place.
• 01:46 We can right click, value field settings, go in to number formats.
• 01:52 Go down to accounting again maybe.
• 01:55 Say none and drop one decimal place.
• 01:58 And say okay, and okay, and you'll see that all of those convert.
• 02:02 So that's pretty good.
• 02:03 Now, there's a couple of other things though that we not wanna see in our
• 02:07 pivot table.
• 02:08 One of them is errors.
• 02:10 Maybe we've got a strange error somewhere sitting in our data and
• 02:14 we don't wanna have that there.
• 02:15 Now ideally, we'll wanna remove the error from the data and I'll show you why.
• 02:18 I'm gonna pull the sales dollars off of this pivot table to make this
• 02:22 a little more obvious.
• 02:24 You can see that we have an error here and of course, when it sums across,
• 02:27 we also have another error.
• 02:29 If we want to hide errors in our pivot table, we can right click on it.
• 02:33 Say pivot table options right down near the bottom.
• 02:37 We can check the box next to for error values show.
• 02:40 And just leave this blank.
• 02:42 And when we do that, it will hide the errors.
• 02:46 The only problem is that my information now no longer totals.
• 02:51 And it also no longer cross totals.
• 02:52 So that's not ideal.
• 02:54 What we really wanna deal with properly is getting rid of the actual error
• 02:59 which is showing somewhere in one of these columns.
• 03:02 So if I go and take a look here, I can find that it's a divide by zero.
• 03:05 This one should be one, so we'll change that.
• 03:09 Unfilter it.
• 03:11 And go and refresh our data now.
• 03:13 And we'll see that it actually totals across.
• 03:16 Now if you're working with difference from calculations,
• 03:18 you can sometimes can a # null error.
• 03:20 This technique is really good for hiding those off.
• 03:23 You don't really want to hide.
• 03:25 Source errors in your file.
• 03:27 The other thing that you may want to consider is filling in these gaps.
• 03:30 This pivot table looks like it's not complete because there's some holes in it.
• 03:34 Now, it's perfectly valid, but right now those holes should be zeros.
• 03:38 So how do we do that?
• 03:39 Once again we right click, we go into pivot table options, and
• 03:45 we have this for empty cells show.
• 03:47 So let's put in a zero and say okay.
• 03:51 And now you can see it fills it with the correct correct number format as well.
• 03:54 So, that's the real quick options for how to go through and
• 03:57 control the values in your pivot tables.
• 04:00 So most of the settings that you're gonna deal with for
• 04:02 controlling numbers are through value field settings and number format.
• 04:07 If you need to go through and actually control what's showing.
• 04:11 How to hide off values or how to fill empty cells with a value.
• 04:15 This is where you do.
• 04:16 Is in pivot table options, which is down near the bottom of this right click menu.
• 04:21 Pivot table options here.

Lesson notes are only available for subscribers.