Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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 enable you to summarize your data in formats that are easy to read, and very helpfully, easy to modify. You can also change your PivotTable's layout, choosing whether to display blank rows below each item and selecting from three report layouts with subtle but important differences that are useful for you to know about. By default, Excel PivotTables have no blank rows after an item ends. In this PivotTable, there is a subtotal here for yearly sales in 2010, but there is no gap between the Summary of 2010, and the last month, December of 2009.
If you want to add a blank row there, you can click any cell in the PivotTable and then on the Design contextual tab click the Blank Rows button and then click Insert Blank Line after Each Item. When you do, Excel inserts the blank line. If you want to get rid of it, again on the Design tab click Blank Rows, and click the Remove Blank Line after Each Item button. As I mentioned before there are three report layout options that display PivotTable data in slightly different ways.
The first layout I'd like to show you is called Outline view. To display a PivotTable in Outline view, go to the Design contextual tab, click the Report Layout button, and then click Show in Outline Form. Outline Form moves each row label over one column so there is a horizontal gap between them. The first entry under each item, in this case Months as part of Years, starts one row below the header. This view makes it easier to pick out divisions within a PivotTable by scanning down the appropriate column. Finally, notice that Outline Form puts subtotals at the top of each group.
Tabular Form is similar to Outline Form, but Excel aligns the first detail row with the header and displays the subtotals at the bottom of each group. To display a PivotTable in tabular form, on the Design contextual tab click Report Layout, and then click Show in Tabular Form. Finally, you can display your data in compact form. For that, click Report Layout, and click Show in Compact Form. This is the most efficient way to display your PivotTable data, but if you're working at a low zoom level or you have a lot of data to work with, it can be hard to pick out individual values.
Now finally, in Excel 2010 there is a new capability that allows you to repeat a column header. It works in Outline view and Tabular view. So I am going to switch back to Outline Form, clicking Report Layout > Show in Outline Form. My goal is to have the year 2009 appear next to each of the months of the year. so January, February, and so on. And it won't make a big difference here because all of the months fit on one screen, but if you had a category for all of the individual values such as weeks in a year didn't fit on a particular screen, then it might be hard for you to remember which year you're working with at one time.
So to repeat the value 2009, click any cell on the PivotTable, click Report Layout, and then click Repeat All Item Labels. When you do, the labels appear in the body of your PivotTable. If you want to get rid of them, click Report Layout and then click Do Not Repeat Item Labels. Modifying PivotTable layouts enables you to present your data as effectively as possible, both for your personal viewing and as part of a presentation. You should take the time to experiment with these options so you can decide what you like best.
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.