From the course: Excel 2010 Essential Training

Creating a basic PivotTable - Microsoft Excel Tutorial

From the course: Excel 2010 Essential Training

Start my 1-month free trial

Creating a basic PivotTable

“

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.

Contents