Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
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.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
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.