Let’s take a look. There are two ways you can format values in a pivot table. The first way is to select cells directly in the pivot table and apply a format manually. For example, we can select the sales values in this pivot table and apply a currency format with no decimal places. In general, this works pretty well, and later versions of Excel are pretty good at extending this format as the table grows when new data is added. However, this method becomes awkward when pivot tables get bigger, and it has a major weakness: As you try out various table layouts, and apply different value formatting, Excel can become confused and not apply the same formatting as expected to all like values. Let’s undo this format, and look for a better way to apply number formats. A much better way to set number formatting is to apply it to the field itself. You can do this in three ways. The first way is to click the field drop-down menu, and choose Value field settings. Then, in the Value Field Settings dialog box, click the Number Format option and apply the format you like. The second way to set number formatting is to right-click on a value directly in the pivot table, and select Value field settings from the menu. This brings you to the same dialog box, and then you can set the Number format as before. The third way set number formatting is to double-click directly on the value field name in the pivot table, then click Number Format. Note, however, this only works when you don’t have column labels in your pivot table. By setting the number format directly on the Value Field itself, you guarantee that all instances of the field will always use that number format, no matter how big the pivot table becomes.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
