Microsoft Office Tutorials and References
In Depth Information
Even if the report shown in Figure 2-1 contains interesting information, it has a problem: it lacks
a format for numbers. In Excel worksheets, the formatting of numbers is one of the functions of
the worksheet itself. So, to format the numbers properly, you select the data area of the report
and choose a proper formatting. If you follow this procedure in a PivotTable, the first result is
not very appealing, as you can see in Figure 2-2.
FIguRE 2-2 Wrong display of numbers if format strings are applied to the PivotTable.
Because you applied the formatting after you created the PivotTable, none of the columns
were large enough to accommodate the new representation of numbers, which now contain
dots and commas, resulting in larger columns. You can solve this easily by resizing all the
columns. Nevertheless, if you decide to change the measure displayed and use a different
one (for example, ListPrice), you probably need a different format and different column
sizes, and you probably have to resize the entire worksheet.
The correct procedure to follow is to use the PivotTable field settings to define a number
format for the OrderQty column. To perform this, you can right-click inside a cell containing the
OrderQty value and, from the menu, choose Value Field Settings, as you can see in Figure 2-3.
FIguRE 2-3 The Value Field Settings menu.
This option opens the Value Field Settings dialog box, shown in Figure 2-4, which contains
many options. We are interested, for now, only in the number format, which you can view
by clicking the Number Format button.