Formatting numbers in a PivotTable

In this tip, I will explain how you can format numbers in a PivotTable, and why you need to format like this.

Let me first tell you what definitely should NOT do, when you format numbers in a PivotTable.
In this example, I created a pivot table where I want to format numbers as currency.
What you definitely should not do is, select these figures and select the “Number” format in the format box.

Why not? It works, alright!

That’s right, as long as you don’t rotate or add fields, as in example below.
As you can see, in the image below, the added values of the “Province” field should not have the currency format.

So, you should definitely not do this.
I will tell you how to do it now.
Right-click a cell containing a value in the PivotTable and select “Value Field Settings” from the drop-down menu.

In the dialog box that appears, click the “Number Format” button.

In the next dialog box, click the “Currency” category and possibly change the format of the cell. Increase or decrease the decimal digits and specify a few other options.
You can do what you want.
Click the OK button.
Click the OK button again, to close the first dialog box.

This gives all of our values as currency.

Even if we add a field, such as “Province”, we see that the values of these cells are formatted as “currency”.  This was certainly not the case when the numbers were formatted in the “Number Format” dialog box under the “Home” tab.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats