Quick, clean, and to the point

How to format numbers in a pivot table

The moment you add numbers to your pivot table, you'll want to adjust the formatting. You could just select the numbers in the pivot table itself, and format normally, but there's a better way.

Let's take a look.

By default, a pivot table uses general formatting. 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. This is especially true when you add

Let's undo this format, and look 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 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 button and apply the format you want.

The second way to set number formatting is to right-click on a value directly in the pivot table, and select Format Cells from the menu. You can then 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. However, this only works when the pivot table does not contain column labels

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.


Related shortcuts

Dave Bruns