Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Now we're going to get a chance to create our very own PivotTable and we'll create it from scratch. Before we do, let's just take a quick look at our data. We have orders coming in from different areas of the country. The orders are coming in at different times of the year. We see we are selling different size products, 1 liter bottles, 2 liter bottles and so on, and we can see in Column E, how many bottles we're selling in each order. Now, if you have thousands of rows of data like this, it could be hard to understand what's going on. So the PivotTable will summarize it and make some sense of this.
PivotTables are something that we insert, so we go to the Insert tab and over here at the beginning of the Ribbon bar, click that PivotTable button and from the flyout, choose PivotTable. That brings up the Create PivotTable dialog box. So the first thing is that Excel is correctly guessing where our data are. And you can see the first row we have our Table/Range is correctly listed over here. Now, we want to put the table on a new worksheet. You don't have to, but it's generally a good idea. Now if you look down here in the lower left, we only have one tab in the worksheet anyway.
So let's leave New Worksheet selected and click OK. So now, you can see we still have our data. That's still here and the PivotTable is going to be on Sheet 1. You could rename that if you want. So over here on the left is where the PivotTable will go and over on the right, we have the panel that will let us construct the PivotTable. Also take a look at the Ribbon bar. We have a PivotTable Tools section and we're right now in the Options tab. So what we're going to do is we're going to use the field list here to construct the PivotTable. So let's start off with the row labels and what we want to do is let's take the region and just with your mouse, you see you get the four headed arrow, just drag down into this box here.
So immediately as we start creating the PivotTable, we have our row labels. Some of the other parts of that structures disappeared. Don't worry. It's going to come back in a moment. Second thing we want to do is figure out our column labels. So the column labels will be the size of the products. So take this Size and drag down here into the Column Labels. So now we have the basic structure of our PivotTable. So the row labels are here, column labels are here, and in the middle, this'll be the volume how many bottles are we selling. So let's go over and take Quantity and drag Quantity down here into the Values box.
So now we can see all right the Southeast region is buying over 40,000 or almost 41,000 bottles of the 1 liter size. We can also filter this by month, so let's take Month and drag that down here and into our Report Filter. So now we have that on the top and if you've done this, congratulations, you are now the proud parent of your very first PivotTable.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87469 Viewers
80 Video lessons · 136424 Viewers
59 Video lessons · 55084 Viewers
52 Video lessons · 68947 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.