All language subtitles for 5. Applying Number Formatting to PivotTable Data

In this lesson, we're going to talk about no formatting in pivot tables because it probably doesn't

work quite the way you might expect.

Now when we think about no formatting in Excel, we're all very used to selecting the numbers that we

want to apply no formatting to going to the home tab and then choosing a format from the dropdown.

Now, applying no formatting in this way will still work, but it's definitely not the best method to

use.

The reason being as your pivot table grows, as you start to add more data and particularly as you start

to pivot or move your fields around, Excel can become quite confused about the number formatting that

it's using.

So a much better way to avoid these types of issues is to apply no formatting in a slightly different

way.

And this method also means that you don't have to select all of the numbers that you want to apply formatting

to.

All we need to do is click somewhere in the column that contains numbers, right click the mouse, and

then we can go down to value field settings from here.

Right at the bottom, we have a number format button and we can choose which formatting we want to apply.

So for these values, I'm going to use currency formatting and I'm going to take those decimal places

down to zero.

Notice that I can also choose here how I want my negative numbers to display.

So I know that I do have some negative values in this dataset, so I'm going to choose to display them

with brackets around the outside.

Let's click on OK and OK.

Again, notice that no formatting has applied to all of the values in that total profit column.

And you can see there my negative values really clearly now with this method.

It doesn't apply no formatting to all cells that contain numbers.

If I also want this column of data to have no formatting, I would need to repeat the same process.

Now, aside from right clicking and selecting value field settings, we can also do this from the reverse.

So if I go up to Pivot Table Analyze in the active field group, I could click on field settings and

then I can get to my number formatting from there.

So let's apply the same formatting.

We're going to go for currency.

Take those decimal places down to zero and set the negative numbers to display like this.

Let's click on OK and OK again.

So now it doesn't matter how much data gets added to my pivot table.

It doesn't matter how I choose to arrange these fields that no formatting is going to remain consistent.

Whichever sell that data is present in.