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.
PivotTables behave a bit differently than other Excel workbook elements, so you'll need to use a few slightly different techniques to select, move, copy and delete them within your workbook. You can rename a PivotTable by clicking any cell on the PivotTable and then on the Options contractual tab on the Ribbon in the PivotTable group at the far left corner of the ribbon, you can type a new name for the PivotTable. So in this case, I will call it Firm Revenue and press Enter. This name will help you and other users interpret formulas that draw on this PivotTable's data.
If you want to clear all the fields, filters, and formats from PivotTable, you can do so again on the Options contractual tab. Click the Clear button and then click Clear All. Doing so doesn't delete the PivotTable; instead what it does is remove all the fields from the Row area, Column area, Report Filter area and the Values area, so that you can rebuild it from the ground up. I'll put my fields back. So we have Year, Month, Company and Revenue.
If you want to create a second PivotTable from the same data range, you can do that by copying and pasting the PivotTable. So again, with any PivotTable cell selected, on the Options contextual tab click the Select button and then click Entire PivotTable. Now press Ctrl+C to copy the PivotTable and then select where you want to paste it, in this case we'll go to the PTPaste worksheet. Cell A1 is selected. Now I will press Ctrl+V and when I do, Excel pastes in the PivotTable.
Now the two PivotTables work off the same data, but they are independent of each other. So for example, if I were to take the Month field out of the Row labels area on this version of the PivotTable, and then switch back to the original PivotTable, which is on the PT worksheet, you'll see that this PivotTable is still in its original configuration and I'll press the Escape key to remove the selection marquee from around the PivotTable. If you want to copy a particular PivotTable arrangement to another worksheet but instead of creating a new PivotTable do so as a data, then you can do that by selecting the entire PivotTable and again you do that by clicking any cell in the PivotTable and on the Options tab click Select > Entire PivotTable and then press Ctrl+C. That copies the PivotTable to the clipboard.
Now go to another worksheet and in this case I will go to DataPaste and then with cell A1 selected, on the Home tab I'll click the Paste button's down arrow and then click the first icon in the paste values row. When I do, Excel pastes in the contents of the PivotTable, but this time instead of creating a PivotTable, it only paste the data. If you want to delete a PivotTable, which I could do by switching back to the PTPaste worksheet and then clicking any cell in this PivotTable, I can delete the PivotTable by clicking Options then Select > Entire PivotTable and pressing the Delete key.
With these techniques in mind you'll be able to manage your PivotTables effectively.
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.