Specify a Number Format for a Pivot Table Field

December 07, 2022 - by Bill Jelen

Problem: In a pivot table, a Values field tends to appear in a General format. This doesn’t always work for me. I might want thousands separators or even to show numbers in thousands. If I change the number format using the settings in the Home tab of the ribbon, the number format is lost after the next pivot table refresh.

Strategy: Microsoft has fixed this long-standing problem, but in a very subtle way.

In the figure below, you must select all of the Revenue cells, including the Grand Total Row and the Grand Total column. If you select the entire range, you can apply any number formatting from the Home tab and that formatting will stick.

This never used to work! It was fixed in Excel 2010 but the fix was not documented. I accidentally discovered the fix in 2015 and even after I discovered the fix, I did not believe it would work. One of the program managers on the Excel team confirmed that the behavior changed recently.

Gotcha: One of the conventions in formatting tables says that you should include a currency symbol on only the first and total rows of a data set. There is no inherent way to do this with a pivot table. However, you can use the numeric formatting attached to the Sum of Revenue field to assign a non-currency format. Then you select the first row of cells and assign a currency format by pressing Ctrl+1 to display the Format Cells dialog. This will work initially, but will be lost when you change the table.

Problem: My manager really wants to track sales of Apples to the midwest.

Strategy: New functionality has been added to Excel in Office 365 as of mid-2018. Right click any cell in the pivot table and choose Format Cell. That formatting will move with the cell, even if you rearrange the pivot table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nick Hillier on Unsplash