Creating a PivotTable report

show more Creating a PivotTable report provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2007: Pivot Tables for Data Analysis show less
please wait ...

Creating a PivotTable report

Pivot Tables are powerful and flexible Excel analysis tools. A Pivot Table let's you rearrange, sort and filter a data set on the fly so you can analyze it from several different perspectives with a minimum of effort. In this lesson, I will show you how to create a Pivot Table from a data list that's stored in the same workbook. So as you can see here in my workbook, I have created an Excel table, which is just a more advanced form of a data list. When I am ready to create the Pivot Table I click any cell in the Excel table, and then on the Insert tab of the ribbon I can click the Pivot Table button.

Excel then displays the Create Pivot Table dialog box and I can verify the data source. In this case, it is Table1 and I do want the Pivot Table to appear on a new worksheet. I could put it on an existing worksheet if I had another one in this workbook, I don't. I just have one, which is called Sheet2. But I usually put my Pivot Table on a completely new worksheet for a couple of reasons. The first is that I don't want the Pivot Table on the same worksheet as the source data because the page gets crowded and you have a hard time distinguishing one number from another.

So I always put it on a new worksheet, but if there is an existing worksheet in your workbook that is blank and you want to put it there, that's perfectly fine. So I verified my settings and I can click OK. So, Excel just created the Pivot Table and it is on a new worksheet called Sheet1. When you create a Pivot Table, Excel displays the Pivot Table field list, and this is a Task Pane that contains controls you can use to manipulate the contents of your Pivot Table. I have the five fields here. I have Year, Quarter, Month, Company and Revenue, and these fields correspond to the columns from the data source that I used.

I can now add those fields to the Pivot Table to organize my data. So let's say that I wanted to go by Year, and I added Year as a Row Header, then I will add Month also as a Row Header. And notice that when I dragged the Month Header below the Year Header, Excel created this organization here where Year is the top-level and Month is the second level and it's repeated here. If I were to switch that order, say to put Year below Month, then you would see January broken down by 2008 and 2009, February 2008, 2009 and so on.

But if I switch it back to Year first and then Month I get this organization, which to me it seems more natural. Now for my Column label, I will bring down Company. Put that here so I have FirmA and FirmB, and now I can drag my Revenue to the Values area and it will populate the data area of the Pivot Table. Now one thing you will notice about the default Pivot Table that Excel creates. It has its subtotals at the top of each group. Say for example, for 2008 you have the subtotal 963 for FirmA, 924 for FirmB, and 1887, which is the total of those two values.

I prefer for the subtotals to appear at the bottom of the group. I will show you how to make this change later but it's something that I always do so I wanted to throw it in here. To display your subtotals at the bottom of a group, click any cell on the Pivot Table and then on the Design Contextual tab, in the Layout group, click Subtotals>Show all Subtotals at Bottom of Group. Then you have 2008 and blanks here, values for the month, total for each row giving you January for FirmA and FirmB, and then you have the subtotals for 2008.

For me, that just makes a lot more sense. Now that you have learned how to create a Pivot Table, you can move forward and discover how to rearrange your data dynamically.

Creating a PivotTable report
Video duration: 3m 49s 2h 45m Intermediate


Creating a PivotTable report provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2007: Pivot Tables for Data Analysis

please wait ...