On the Developer ribbon, run the RefreshPivotTableFromWorksheet subroutine. Now the
new city appears in the data summary, as shown in Figure 6-14.
Figure 6-14. Rochester data displayed after RefreshPivotTableFromWorksheet is run
Applying Formatting to a PivotTable Report
You will probably find that some of the default formatting Excel applies to your PivotTable
reports needs some modification??”things such as the general number format, the table formatting
without lines, the default naming of calculated fields to ???Sum of field name,??? and its
handling of null or blank entries.
In the Download section for this book on the Apress web site, find the file named
PivotTable02_Formatting.xlsm, and open it.
Blank Data Records
To see the effect of blank records on a PivotTable report, let??™s make Sheet1 active and remove
the data for Reading, PA??™s tank top sales. The Quantity and Sales Total values are 0, but we
want to make them blank as though no data were added (as shown in Figure 6-15).
CHAPTER 6 n PIVOTTABLES 238
Figure 6-15. Blank data for Reading, PA tank top sales
1. Activate the worksheet containing the PivotTable report.
2. Refresh the data (either through the UI or the RefreshPivotTableFromWorksheet procedure).
Figure 6-16 shows Excel 2007??™s default behavior when we have blank values in a
PivotTable.
Figure 6-16. Blank values display as blank on PivotTable report
3. Drag the Sum of Qty label back up to the field selection list in the PivotTable Field List.
Pages:
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233