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 - usually a comma - 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, you click any cell in the field, like I've done here. I'm in the body of the worksheet, and then on the PivotTable contextual tab. you click the Settings button. That displays the PivotTable field dialog.
Within there, you can see that the field name is Sum of Revenue. That means that we are summarizing revenue. And the operation we're using is the Sum. We are finding an arithmetic sum. In this case, I don't want to change the summary operation; instead, I want to change the number and its format, so I click the Number button, which opens the number page of the Format Cells dialog. And now I can use the formatting controls that we've seen before. In this case, I will click Number, and because I don't have any decimal values, I will decrease the decimal places to 0. I could have just typed in 0, but I wanted to use the spin control, and I want to have commas, which are called thousandths separators, so I'll check that box.
If you're in Europe, especially France, they use periods as their thousandths separator, and depending upon your local installation, Excel will change to use your local convention. And I won't change the negative number style. I'll just have it with a minus instead of in red. Everything looks good. I can click OK, click OK again, and Excel has changed the formatting. You don't see any changes here in the body of the worksheet because none of the values are over 1,000, but here, in my Grand Total cell, you'll see that the value 1887 is now written 1,887.
So, my number format change has taken effect. You should always change the PivotTable data field's number format if you have any values, including subtotals or grand totals, that go over 1,000. 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 the numbers in the body of the PivotTable. 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 · 73926 Viewers
80 Video lessons · 129436 Viewers
52 Video lessons · 63780 Viewers
59 Video lessons · 49545 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.