Pivot tables are my favorite feature in all Excel. We are going to start here and we are going to open a file. Use file, Open, choose Project Files and let us go to Lesson Two and open the O1 pivot table as well. The data set that we are going to be working with on this lesson is about 200 rows. We have Region, Product, Date, Customer, Quantity, Revenue, and Cost of Good Sold and Profit. Great thing about pivot table is we can summarize this data without using any formulas at all. If you can click and drag, you can create a pivot table.

Let us dive right in and create our first pivot table. One of the frustrating things about Excel 2007 is figuring out where they move the old commands from Excel 2003. Now, pivot tables used to be in the Data Menu in Excel 2004. This is the one I cannot figure out. They moved it to the Insert Ribbon in Excel 2007. Choose the Insert Ribbon. Make sure that it has one cell and your data is selected. On the Insert Button, choose the top half of the pivot table icon. This is actually an icon with a drop down at the bottom and a button at the top. We want to use the button at the top in order to create a pivot table.

The first thing Excel asks us about is where is the data located. Provided that you have no blank columns or blank rows, Excel will guess this correctly and you can click okay. In the lower half, you can choose whether you want to create the pivot table on a new worksheet or out to the right on an existing work sheet. I am going to choose new worksheet so click okay. Now, if you have used pivot tables before in Excel 2003, you will notice that this is completely different. One of the things that was very frustrating for anyone who is new to pivot tables in Excel 2003 was they tend to drag fields to the wrong place.

In Excel 2007, Microsoft has changed the paradigm, rather the dragging the fields from the pivot table field list onto the worksheet, they now have you drag fields from the pivot table field list to one of four drop zones right in the pivot table field list. Let us clear our first pivot table. Let us say that we want to create a summary of revenue by region and product. First thing we do is click the region field and see if the region is added to the row labels area of the pivot table. And, we see a unique list of regions in column A of the worksheet. The next thing to do is click the Product Field. Now, because the Product Field contains text, it automatically is added to the row labels area of the pivot field list.

In this case, I would like to have the products going across the top of the report so I am going to take Product Field and drag it from the row label drop zone to the column label drop zone. And, we have a unique list of products going across row four. Now, we need to add one more field to the report and that is where we want to total. In this case, we can either choose to tell the Quantity, Revenue or Profit. Let us choose Revenue. Because my data set includes all numeric values in the revenue field, it is automatically added to the sum values drop zone. Now, if you happen to have a couple of blank sales or worse yet, a little bit of text in the revenue field, it would be added to the row labels or the column labels area and you would have to drag it to some values.

Let us take a look at this. Pivot table is an amazing thing. We just summarize 1200 rows of data without using any formulas at all. If you can click and drag, you can create a pivot table.