Join Curt Frye for an in-depth discussion in this video Formatting data for use in a PivotTable report, part of Excel 2007: Pivot Tables for Data Analysis.
Pivot tables enable you to summarize and reorganize your data dynamically, but you can't summarize just any data set. In most cases, your data needs to be laid out as a data list. So what do I mean by data list? Well, what I mean is that the source data must be laid out as a table with column headers and no extraneous data surrounding the table. That's how I have this data laid out here in my DataPrep workbook. I have columns of data: Year, Quarter, Month, Company, and Revenue, and in each row within the table represents a particular data point.
For example, if I wanted to find out FirmA's sales revenue for January 2008, I can just look here in this row, I see 2008. Data was in Quarter number 1, which is correct. The Month of January, the Company was FirmA and the Revenue was $67,000. All I need to do is create a data list that has an individual measure for each item that I want to represent within the pivot table. It is absolutely vital that you have all of the information for each row: the Year, the Quarter, in this case, the Month, the Company and the Revenue.
Excel can still create the pivot table if you have blank cells within the data list. It just won't know how to reorganize the data when you pivot your pivot table. Say for example, if you had no month in this case for the value in November, then there is no way that Excel could assign it to the month of November and it would just put it in a separate group with blank cells. That doesn't give you any useful information. So make sure that all the data is filled in your data list. This is a data list and the reason that I show you this basic data list is just in case you'll be applying some of what you learned here to Excel 2003 for the PC or perhaps Excel 2008 for the Mac.
In Excel 2007, there is a new construction and that is called an Excel Table. An Excel Table is a much more powerful and versatile tool, especially when it comes to creating pivot tables, and I'll show you why that's the case in a few moments. But first, I'll show you how to create an Excel Table in Excel 2007. To do that you have your data formatted as a list. You click any cell within the data list, and then on the Home tab of the ribbon in the Styles group. You can click Format as Table, and I'll just select the most basic style here, style number one.
In the Format As Table dialog box, the Excel will ask where the data is for your table. It selects the active group automatically, A1 through E49 in this case, which is correct, and my table does have headers. That's the Year, Quarter, Month, Company, and Revenue. Now I can click OK and Excel creates the table. Now, what are some of the advantages of creating an Excel table? Well, first off, Excel, when you create a pivot table, refers to the table in its entirety, as opposed to data list.
If you use a data list as source for a pivot table, Excel says, "Okay, the data starts in cell A1 and it goes down to sell E49," in this case. The problem is that if you want to update the data source when you work from a data list, you need to let Excel know that you've updated the data source by adding rows to it or subtracting rows from it, and Excel can't detect that automatically. If you use an Excel table as the data source for your pivot table, Excel can say, "I'm referring to the data from the table named Table1." Regardless of how many rows it contains, and how you change it either by adding or subtracting rows, Excel knows that it just wants all the data from Table 1.
While I am here, I'll show you, just a few other capabilities of an Excel table. One thing you can do is to filter the data in a table. Say for example, if I wanted to see only the results from January, I can click this filter arrow here at the top of the Month column, clear Select All, select the January box, click OK, and that limits the data, so only the results from January appear. In the same way, if I want to remove the filter, I can Clear the Filter from Month again by clicking the filter arrow and then clicking this menu item, and all the data reappears.
Another great thing about an Excel table is the Totals Row. The Totals Row appears at the bottom. I don't have it turned on, but if I click any cell in the Excel table and then on the Design contextual tab, in Table Style Options, I can click Total Row, and Excel provides a summary of the information in the Excel table. In this case, it gives me a summary of the values in the Revenue column, and it's finding the sum. That's what the value 109 means.
That means that it's finding a sum. And that is an artifact of the =SUBTOTAL formula. You can change the summary operation in the Total row. Say for example, if I wanted to go to Average, I can click Average, and you get the average value from the column, but in this case I do want Sum, so I'll switch it back. Once you have your source data arranged in a data list, you can create a pivot table. I'll cover the other case in the lesson in title Consolidating Data for Multiple Sources, which is found later in this chapter.
- Sorting across data sources to show relative importance
- Adding, removing, and positioning subtotals and grand totals
- Creating conditional formats to highlight subsets of data
- Using color scales to emphasize specific information
- Adding a trendline to a PivotChart report
- Updating and refreshing PivotTable data sources