A long-standing frustration with pivot tables in Microsoft Excel is that any fields that contain numbers must always be manually formatted to add commas and/or remove decimal places. Further, the formatting changes must be made one field at a time in order to ensure that subsequent changes don’t cause the number formatting to vanish when you refresh your pivot table. In this article I’ll show how a single line of programming code can cause Excel to automatically format numbers as you add new fields to a pivot table.
Let’s first create a pivot table, as shown in Figure 1. The underlying data includes three columns: Product, Cases Sold, and Total Sales. To create a pivot table: