Number Format

In Lesson 48 I already told you how to add and remove fields from ” PivotTable” with the “PivotTable Field List”.
But there are a number of other options too.
The first is the number format.
Instead of doing it through the “Home” tab in the “Ribbon”, you can use the drop-down menu shown below.
This prevents complications if we have to change or add data in our Value Area
Just click the downward pointing arrow next to the field in the Values area.
In this case, it is next to the sum of sales field.
And choose “Value Field Settings” from the drop-down menu.

In the “Field Settings” that appears, click the “Number Format” button at the bottom left.
This opens the “Format Cells” dialog.

I choose Currency on the left of the dialog.

For “Decimals Places” I choose 2.
In the “Symbol” you can select the Euro character.

I can also enter a format for negative numbers.

Click OK when you are satisfied.

And click OK again, in the “Field Settings” dialog box.

All values related to the ‘sum of sales’ field in our ” PivotTable” now have the correct format:

List Field Options

When we click the button at the top of the “PivotTable Field List”, we can choose different formats for our list from a drop-down menu.

By default, the top format is selected, there are four more formats below.

You can change this setting by choosing a different format from the drop-down menu.

The last two options can be useful when all the fields have been added, and you only want to replace some fields.

To move the fields in the list, place the mouse pointer over the field, and click and drag it to the position you want.
If you want to move the field list back to its original location, click and drag it over the right edge of the Excel window.
Release the mouse button when the list is in place.

If you want to hide the list click the X at the top right, or click the “Field List”

in the “Options” tab in the “PivotTable Tools” in the “Ribbon”.

If you want this list, click again on the “Field List” in the “Options” tab.

 
A final option in our field list, is at the bottom of the list.

When we check the “Defer Layout Update”option, when we add, delete or move certain fields in our “PivotTable”, the updates will not be done until we click the “Update” button.

This is useful when dealing with very large and complex PivotTables, especially with external data sources. Such updates take quite some time, so it is useful if we can swich them off.

Featured Courses

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