Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The real power of a PivotTable comes out when you want to rearrange your data dynamically. A task that would take several minutes, at least, if done by hand, takes just a few seconds when you want to summarize your data in a PivotTable. In a PivotTable, the row and column headers provide the basic structure. So, for example, here in the PivotTable, you'll see that I have my company, FirmA and FirmB, my two companies, and then I also have my months, January through December. When you look over in the PivotTable Builder, you'll see that that structure is reflected here.
So, for example, I have the Company field, which provides the values here: FirmA and FirmB, have the Month, providing the values for the Row Labels, and then we have the Values, which is sum of Revenue here in the middle. Changing the position of the row and column headers in a PivotTable Builder changes their organization within the PivotTable. So let's say, for example, that I wanted to arrange my data first by Company and then done by Month and use them both as row label headers. I can pivot the PivotTable by clicking and dragging the Company field header into the Row Labels area.
When I do, and the field header is in a place where I can drop it, you'll see a blue line, and I'll move this slightly to the side so you can see it. There is a blue line above Month, and also the Company header has a green circle with a white Plus sign. That means that I can drop the Field header, and something will change. When I do, Excel arranges the data within the PivotTable, first by Company and then by Month, and then the second Company, FirmB. And when I scroll down using the scroll button on the mouse, you'll see that it goes FirmB and then the Months January through December.
I'll just do a few more quick examples. Let's suppose that I want to keep Company as my Row header and Month, move that to the Column Labels area. To do that, I just drag it and drop it, and now the PivotTable is extremely wide, as opposed to this organization, where it's extremely thin. Let's add one or two more fields to the fray and see how things work. For example, let's go by Year. If I add Year to the Row Labels area, dropping it, once it turns green and I see my blue lines, so it's in position, now I have 2008, FirmA, going by Month, and then FirmB for 2008, and then I have my Total for FirmB, and then the Total for all of 2008.
Then I can go to 2009, and you see the same structure repeated. If I were to move the Company header back over to the Column Labels area, I would have the year 2008, with each of the months here, and then FirmA and FirmB, the two companies, and totals for each of them. If I were interested in looking at my revenue by quarter, I can add the Quarter field up here, again, to the Row area, because that's where my time-based measurements are. I have Year and Month there already, and Quarter is a selection of three months, so I'll drag that down and drop it between Year and Month.
Once again, you see the blue line. I've got my green circle with the Plus sign. I drop it, and now I have my data, which is summarized by quarter. Changing the PivotTable's arrangement shifts the data's emphasis, enabling you to examine the data from different perspectives quickly and easily.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.