Join Curt Frye for an in-depth discussion in this video Changing the data field number format, part of Excel for Mac 2016: Pivot Tables in Depth.
- [Voiceover] When you create a PivotTable, Excel displays the values in the data fields without any formatting. When you look at numbers in the hundreds and below, the lack of commas and other formatting doesn't really matter. But when you add those values in a subtotal or grand total cell, the lack of thousands separators makes the values harder for humans to process. You can make your data easier to read by changing the data fields' number formats. I'll show you how do to do that. And I'll work in my sample file called DataFormat. That is an Excel workbook that you can find in the Chapter 4 folder of the Exercise Files archive.
This PivotTable contains numerical data. It's revenue for number of hotel rooms across time. And as you can see, the numbers are here, but they're kind of hard to read. So what I'd like to do is to format the data as an accounting format, so that I have dollar signs, which indicates that it is a currency value, and also use thousands separators, so that I have a comma between the thousands digit and the hundreds digit, and also, for any larger values in the millions.
To do that, I'll click any cell in the data area of the main PivotTable. Then I'll go to the Analyze contextual tab on the ribbon. Make sure that Sum of Revenue is the active field, as you can see here in the Active Field box. And then click Field Settings. That displays the PivotTable Field dialogue box, and it is set to Sum of Revenue. I want to change the field's formatting, so I'll go to the bottom-left corner of the dialogue box and click Number. That displays the Number page of the Format Cells dialogue box.
I'll go to the Accounting category and click it from the Category list. And now I can work with my data in a number of ways. The two changes I can make are the number of digits to the right of the decimal point. In this case, it's a currency value, and it has two by default. In this case, all of my values are whole numbers, or whole dollars, so I'll click the down arrow on the spin control to reduce that to zero. If I were working with foreign currency, then I could click the Symbol controls down arrow and use any number of other currency symbols.
This we have dollars for English in Canada. And English is the language of that particular local setting. But I could also go down and use British Pounds and Scottish Gaelic, English, Welsh, Chinese Yuan, Japanese Yen, and so on. But in this case, I'll stay with American dollars, so I'll click away from the list, and click OK. I'm back in the PivotTable Field dialogue box, so I'll click OK, and Excel applies my changes.
You should always change the PivotTable data fields' number format if you have any values, including subtotals and grand totals, that go over 1,000. Doing so makes your data easier to read, especially in summary.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Creating a Recommended PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats