Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a Pivot table, 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 the subtotal or grand total cell, the lack of 1000s 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 Pivot Table data field, click any cell in the field and then on the Pivot Table toolbar, click the Field Settings button. In the Pivot Table Field dialog box, verify that you have the proper field selected and then click Number.
Now you can use the controls on the number page of the Format Cells dialog box to change the Number's format. In this case, I'll go with Number, which is a good generic format for any sort of numeric data. My values don't have any numbers to the right of the decimal point, so I will set the number of Decimal places to zero, and because my grand totals and subtotals can go up above a thousand, I will have a 1000 Separator. Negative numbers can just have a minus sign and to stay black. That's fine.
I can click OK. Click OK again, and Excel applies my formatting. You can see here that my subtotal for 2008 for the East region is now formatted with a comma. Makes it much easier to process. You should always change the Pivot Table data fields Number format if you have any values including subtotals and grand totals that go over one thousand. In general, it's a bad idea to use the currency or accounting formats mainly because the currency symbols takes up space within the cells, and can be distracting when you're trying to read the numbers in the body of the Pivot table. If you're summarizing currency values, use a Number format that displays two places to the right of the decimal point.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74950 Viewers
80 Video lessons · 129929 Viewers
52 Video lessons · 64107 Viewers
59 Video lessons · 49902 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.
Your file was successfully uploaded.