Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables allow you to interactively analyze data along two or more dimensions. Let's take a look at our Profit Margin by product. Click once on the Products table. Then go up to the Create tab and then click on the More Forms button. Drop it down, and choose PivotTable at the bottom. In the Show/Hide group on the left-hand side, click on Field Lists until you get a PivotTable Field List on the right. You have a screen with several zones for you to drag your fields. Click on Product and drag it to the left- hand zone, where it says Drop Row Fields here.
This will group all of our Olive Oils by Olive type. Pick up Size and drag it where it says Drop Column Fields here. This will now show you all the different sizes of each bottle, and drag Profit into the center area. You can now see the Profit Margin for each bottle size in the grid. Also drag Profit over to this Grand Total, and we can see our Total Profit. Now when I do that, it does open up a Subtotal and a Grand Total, which we don't need, so come up here to the Hide Details button, and that part closes.
Now not only is this easier to compare that in the product list, but you can also see which size is profitable, the 128 ounce bottle, but you can also see which oil itself: the Picholine and the Picual. If you want to hide different rows or columns, drop down the Header and turn on and off the check marks that you want to see, and click OK. This arrow here will turn Blue to show you that you have a filter applied. Click on the Blue arrow again. Click on All, and they will all come back again.
Now PivotTables are interactive. You can pick fields up and drag them around to different places. We're going to show you how we came to the sum of the profit. Click on Price and pick it up and drag it next to Sum of Profit, and it will now add Sum of Price under each size. Do the same thing with cost. Drag it up and put it between Price and Profit, and now I can see, for each of my olive oils, how much we charge altogether, how much they cost altogether and their Profit Margin.
If you want make a PivotTable that sourced by information from several different tables, first make a query pulling those particular fields into one place. Then use that query as the source of the PivotTable. As you can see, PivotTables group your data into a grid, allowing you to analyze your information along two or more dimensions.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70791 Viewers
80 Video lessons · 127762 Viewers
52 Video lessons · 62606 Viewers
59 Video lessons · 48326 Viewers
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.