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:
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.