Pivot Table Data Crunching Authors: Jelen B.,  Alexander M. Published year: 2003 Pages: 16-17/140

### When Should You Use a Pivot Table?

Large datasets, ever-changing impromptu data requests , and multilayered reporting are absolute productivity killers if you have to tackle them by hand. Going hand-to-hand combat with one of these is not only time consuming, but it opens up the possibility of an untold amount of errors in your analysis. So how do you recognize when to use a pivot table before it's too late?

Generally, a pivot table would serve you well in any of the following situations:

• You have a large amount of transactional data that has become increasingly difficult to analyze and summarize in a meaningful way.

• You need to find relationships and groupings within your data.

• You need to find a list of unique values for one field in your data.

• You need to find data trends using various time periods.

• You anticipate frequent requests for changes to your data analysis.

• You need to create subtotals that will frequently include new additions.

• You need to organize your data into a format that's easy to chart.

### The Anatomy of a Pivot Table

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.

 Pivot Table Data Crunching Authors: Jelen B.,  Alexander M. Published year: 2003 Pages: 16-17/140