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.
Excel PivotTables display your data in as much or as little details you prefer. Pivoting and filtering the PivotTable changes how the individual data elements are displayed, but you can also position the subtotals and grand totals for rows and columns to summarize your data as desired. For example, in this PivotTable I currently have both Subtotals and Grand Totals turned on so you'll see that for FirmA the subtotal for the year 2009 was 963, for FirmB the same subtotal for that year was 924, and we have a grand total for that year of 1887, and you'll see a subtotal for each of the firms and the grand total for 2010.
And then at the bottom of the PivotTable, which I displayed by scrolling down using my mouse's scroll wheel, you'll see that we have a grand total for FirmA of 1924, FirmB of 1729, and a grand total for both of 3683. Now, I'd like to show you how to change how subtotals and grand totals are displayed, so I'll scroll back up so you can see my change more clearly. Now after clicking any cell inside the PivotTable I can go up to the Design contextual tab on the Ribbon and then in the Layout group click the Subtotals button and then I can select the option that I want for subtotals.
At present I'm showing all subtotals at the top of the group, so I'll change it now to the bottom by clicking Show all Subtotals at Bottom of Group. And when I do, Excel simply moves the subtotal to the bottom of the group, below the individual data lines. So I have the year and then individual months and their totals, the grand total for both firms for a given month, and then the subtotals and grand totals as displayed here. If you want to turn off subtotals entirely then you can click the Subtotals button and click Do Not Show Subtotals.
And putting subtotals at the top or bottom the group is simply a matter of personal preference. Personally I prefer to have subtotals at the bottom of the group because I like to see the individual data points and see what they add out to at the end, but if you prefer to see the total and then the details, perfectly fine. Just select the option you want. Grand totals on the other hand summarize all of the values in a row or column. You can't reposition them but you can turn them on or off for rows or columns or both. So let's say that I want to turn them off for rows.
Just click any cell in the PivotTable and then on the Design contextual tab click Grand Totals and then I'm going to turn them on for columns only, in other words from them off for rows. So On for Columns Only, and you'll see that the grand totals appear at the bottom. Now if I scroll back up and then click Grand Totals and turn them On for Rows Only then you'll see the grand totals for each of the rows and then if you scroll down you see that they've been turned off for columns. Or if you prefer, you can click the Grand Totals button and turn them off entirely.
To bring them back, just go to Grand Totals and then On for Rows and Columns. If you change the summary operation that a PivotTable uses to display its data, then Excel applies that change to the Subtotal and Grand Total fields. I'll show you how to do that in another movie later on in this chapter. Subtotals and grand totals provide easy- to-read summaries of the values in your PivotTable's rows and columns. If you find those extra rows and columns are a distraction, or if you just want to emphasize the values in the body of your data area, you can hide them quite easily.
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.