Join Curt Frye for an in-depth discussion in this video Introducing PivotTable reports, part of Excel for Mac 2011 Essential Training.
- View Offline
Before I get into the details of how to create and manipulate PivotTables, I'd like to demonstrate a few of the ways PivotTables help you analyze your worksheet data. A PivotTable looks a lot like a regular worksheet, with rows and columns of data, labels, subtotals, and grand totals, but what PivotTables enable you to do is to change the structure of your data to emphasize different aspects of that data. So let's get a sense of the lay of the land here. Let's take a look at the PivotTable. You see that I have the data in here, and I have the total for each row here in this column for the Grand Total, and each row has data for, in this case a specific month of the year.
I've 2008, broken down into January, February, March, and so on, and then I have a subtotal for 2008. And sales are divided between two firms, two of my customers: FirmA and FirmB. If I scroll down, you'll see that I have a subtotal for each of these two columns for the year 2008, and then I have the same for 2009, with the subtotal here, and then the grand total for each of these two columns and a grand total for the entire PivotTable. I'll scroll back up.
On the right side, you'll see that I have what's called the PivotTable Builder. The PivotTable Builder is a dialog that you can use to manipulate the PivotTable. If for any reason you don't see it- and I will hide it now intentionally by clicking the Close box - if you want to see it, you can click the PivotTable contextual tab on the Ribbon, and over here in the View group, click the Builder button, and the PivotTable Builder comes back. So now, what are some things that you can do with the PivotTable? Well, you can reorganize your data. And what I'd like to do is focus on the data in this cell here, the value 128, which represents revenue generated from FirmA in March of 2008.
And to identify that cell as we pivot, I'm going to mark it in yellow. So now, let's say that I want to reorganize the data in my PivotTable. If I want to organize by Year, by Month, and then by Company, I can drag the Company field header below Year and Month, and when I release it, you'll see that I have 2008, January, and then FirmA and FirmB, and the value 128, which corresponded to March 2008 for FirmA, has moved down. And you'll see that this structure mirrors the structure here in the PivotTable Builder.
I go by Year, by Month and by Company, and that structure is indicated here. Now let's say that I want to go by Year and Company in the rows and Month in the column labels. If I change that, the PivotTable pivots, technical name for how the PivotTable changes, and you can see that the value 128, again, March, 2008 for FirmA, has changed position, but it is still in the same place relative to its values, March, 2008, and FirmA. And that's exactly the same thing that happens with every other value.
The only thing that has changed is your perspective on the data. This PivotTable tends to emphasize monthly sales, so that means that you can look through and look it for May, and 2008, for January, February and so on, and if you prefer vertical to horizontal, well, you can reorganize the data that way too. I will put month down here, just for a moment, and then I'll put Year and Company in the Column Labels area, and when I do that, you get a different organization of the data that can provide a different perspective.
I hope this brief demonstration has given you a feel for PivotTable's capabilities. In the remaining movies in this chapter, I'll go into more detail on how to create and manipulate PivotTables, and the data they contain. The real benefit of using PivotTables is that you can, in seconds, reorganize and gain new perspectives on your data in ways that would take at least several minutes of cutting and pasting without them.
- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks