GoSkills
Help Sign up Share
Back to course

Value Field Formats

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
199.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
Login to download
  • 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.

Calculated Items
05m:51s
Preserving Cell Formats on Update
03m:41s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement