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.
Before I get into the details of how to create and manipulate PivotTables, I would like to demonstrate a few of the ways a PivotTable helps you analyze your worksheet data. When you look at your PivotTable, it looks a lot like a regular worksheet with rows and columns of data, labels, subtotals, and grand totals. The difference is that a PivotTable allows you to rearrange your data dynamically within the worksheet. If you click a cell inside of a PivotTable, then you will see that Excel displays the Options and Design tabs on the Ribbon.
These contextual tabs allow you to manipulate your PivotTable either by changing a style, which you can do on the Design tab, or on the Options tab refreshing its data, changing its data source, and so on. What's really exciting about PivotTables is that you can change the structure of your data to emphasize different aspects of that data. PivotTables are divided into several different areas. First, you have the PivotTable Field List which allows you to manipulate the PivotTable and within the field list, you have the number of fields which are data types that you can use.
So you have the Year in this case, the Quarter, which is not displayed, Month, Company name and Revenue. And you can see those fields reflected in the body of the report. You change the arrangement of the fields within your PivotTable using the field area, which is down here at the bottom of the PivotTable Field List task pane. Say for example, you can have your Company data as the column labels and you can see that reflected here, in the body of the PivotTable with FirmA and FirmB. Then the row labels would be the year, in this case 2009, and then the month, starting with January and going down through December.
If you want to change the PivotTable's layout, you can do that by dragging a field name from one area to another. So for example, let's say that you wanted to lay out your data so that it was Company, Year, and Month and each of the Company, Year, and Month labels were in the row area. To make that pivot, you would drag the Company field name to the top of the Row Labels area and then when you release the mouse button the PivotTable updates the data to reflect your new organization. So now, we have all the results for FirmA in 2009 by month and then below the year 2009 we go to 2010 and again by Month, and lower down in the PivotTable you'll see the same result for FirmB.
So I will scroll down using my mouse wheel and below the grand total for FirmA you see the same data for FirmB. You can also filter the data in your PivotTable to focus your analysis. So let's say, for example, as I scroll back up to the top that I wanted only to display the data for FirmB. To create a filter, I can into the PivotTable Field List and then click the Company fields down arrow and then create the filter. In this case, I only want to show the data for FirmB, so I will clear the check box next to FirmA, and then click OK.
When I do, Excel updates the PivotTable so that only the data for FirmB is displayed. If I want to clear that filter, then I can go to the PivotTable Field List, click the Company fields bar, and then click Clear Filter From "Company". When I do, Excel restores the data. You can also change the look of your PivotTable. You can do that by adding or changing a style. So for example, if I click the Design tab I can select any one of the available PivotTable styles.
So if I click the more button in the gallery then, I can click any of the available styles and I will go down and I'll click one that's a bit darker for contrast. I hope his brief demonstration has given you a feel for PivotTables capabilities. In the remaining movies in this course are going to much more detail on how to create and manipulate PivotTables and the data they contain.
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.