Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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 thousand separators makes the values harder for humans to process. You can make your data easier to read by changing the data field's number format. To change the number format of a PivotTable data field, right-click any cell in the field, and then click Number Format.
When you do, the Format Cells dialog box appears and you can set the format for yourselves. In this case we have a number. So I'll click the Number category and then I can apply settings for these numbers. In this case all the values I am working with are whole numbers so I can set decimal places to 0 and some of my totals go over a thousand, so I will check the Use 1000 Separator box. In the United States, the 1000 separator is a comma.
But, if you're in Europe, you might find that they use a period instead of a comma. The 1000 separator that appears will depend on your local Windows settings. I don't need to make any other changes so I can click OK. And when I do, you will notice that the numbers in the body of the PivotTable didn't change. 140 is still 140 and so on. But the Grand Total for 2009 is 1,887 and the 1000 separator, in this case the comma, appears, making the number easier to read.
You should always change the PivotTable data field's number format if you have any values, including Subtotals and Grand Totals that could go over 1000. In general, it's a bad idea to use the currency or accounting formats mainly because the currency symbols take up space within the cells and can be distracting when you're trying to read numbers in the body of the PivotTable. If you are summarizing currency values, use a number format that displays two places to the right of the decimal point so you can include cents in addition to dollars.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91397 Viewers
80 Video lessons · 138268 Viewers
59 Video lessons · 57101 Viewers
52 Video lessons · 70758 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.