Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The real power of a pivot table comes out when you use it to rearrange your data dynamically. A task that would take several minutes if done by hand, takes just a few seconds when you summarize your data in a pivot table. When you look at a pivot table, you realize that the row and column headers provide the structure for the pivot table data. So, here we have a year as our Row Labels along with month, January, February and so on. And for the Column Labels, we have the two companies, Firm A and Firm B, and you see that we have that structure reflected here in the Pivot Table Field area.
So how do you rearrange the data? You rearrange the data by dragging the field headers in the Pivot Table Field List field area to change the arrangement of the data. So, for example, if I wanted to put Company at the top of the Row Labels, I would drag it here to the Row Labels area and you can just barely see a gray line that appears when you move the pivot table field into a position where it can be dropped. So, I'll drop it and you'll see how Excel changes the pivot table's data arrangement.
So let's take that analysis one step further. Let's say that we wanted to leave Company and Year as Row Label headers, but move the Month to the Column Label, so that the data is spread out a little bit more. There you see the new arrangement and in this case, it really emphasizes the data on a monthly basis as opposed to yearly basis. You can compare one March to the previous March for both Firm A and Firm B. Now, here is an interesting trick that works well when you have a large pivot table, or you have a pivot table that is summarizing quite a bit of data.
If you notice that your performance is lagging, in other words that it takes some time for Excel to update your pivot table whenever you pivot it, that means you either have a slow connection to the data source or there is just too much and Excel needs to work hard to update your pivot table. If that's the case, you can select the Defer Layout Update checkbox. Make your changes. Notice that the changes do not appear in the pivot table yet. Then I'll take Year up to here. When you click Update, Excel updates your pivot table with the changes that you made.
Changing the pivot table's data arrangement shifts its 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.