Pivot Table Data Crunching for Microsoft Office Excel 2007
| Because the anatomy of a pivot table is what gives it its flexibility and, indeed, its ultimate functionality, it would be difficult to truly understand pivot tables without understanding their basic structure. A pivot table is composed of four areas. The data you place in these areas define both the utility and the appearance of the pivot table. Keeping in mind that you will go through the process of creating a pivot table in the next chapter, let's prepare by taking a closer look at the four areas and the functionality around them. Data Area
The data area is shown in Figure 1.5. It is a large rectangular area below and to the right of the headings. In this example, the data area contains a sum of the Revenue field. Figure 1.5. The heart of the pivot table is the data area. This area will typically include a total of one or more numeric fields.
The data area is the area that calculates. This area is required to have at least one field and one calculation on that field in it. The data fields that you would drop here would be things you would want to measure or calculate. The data area might include Sum of Revenue, Count of Units, or Average of Price. It is possible to have many fields in the data area. You might include Sum of Quantity, Sum of Revenue, and Average of Price. It is also possible to have the same field dropped in the data area twice, but with different calculations. A marketing manager might want to see Minimum of Price, Average Price, and Maximum of Price. Row Area
The row area is shown in Figure 1.6. It is composed of the headings that go down the left side of the pivot table. Figure 1.6. The headings down the left side make up the row area of the pivot table.
Dropping a field into the row area will display the unique values from that field down the rows of the left side of the pivot table. The row area typically has at least one field, although it is possible to have no fields. The example earlier in the chapter where you needed to produce a one-line report of credits is an example where there are no row fields. The types of data fields you would drop here are things you want to group and categorizefor example, Products, Names, and Locations. Column Area
The column area is composed of headings that stretch across the top of columns in a pivot table. The pivot table in Figure 1.7 has the month field in the column area. Figure 1.7. The column area stretches across the top of the columns. In this example, it contains the unique list of months in your dataset.
Dropping fields into the column area would display your items in a column-oriented perspective. The column area is ideal to show trending over time. The types of data fields you would drop here are things you want to trend or show side by sidefor example, Months, Periods, and Years. Page Area
The page area is an optional set of one or more drop-downs at the top of the pivot table. In Figure 1.8, the page area contains the Region field, and the pivot table is set to show all regions. Figure 1.8. Page fields are great for quickly filtering a report. The Region drop-down in cell B1 allows you to print this report for one particular region manager.
Dropping fields into the page area would allow you to filter the data items in your fields. The page area is optional and comes in handy when you need to filter your results dynamically. The types of data fields you would drop here are things you would want to isolate and focus onfor example, Regions, Line of Business, and Employees. |