More Examples of Pivot Tables

0) Introduction:
1) Analyzing the Car-Sales Database
2) Analyzing the Books Database
4) Summary:


In this lecture we continue examining examples of pivot tables. Including
looking at more complicated pivot tables with multiple selections in rows
and the use of page fields.


1) Analyzing The Car-Sales Database

Examine the database on the Cars worksheet for this lecture. This data records
whether or not (Yes or No) over 300 different families (of a size characterized
as either Small or Large and of family income/salary characterized as either
High or Low) decided to purchase a Station Wagon. Imagine that we want to use
this information to determine what characteristics make a family more or less
likely to purchase a station wagon.

Following the steps from the previous lecture, let's create a pivot table with
this data. Drop the "Salary" first and "Family Size" second into the Row
Labels of the PivotTable itself or the PivotTable Field List. Notice these
labels appear in this order. left to right in two columns on the left of the
Pivot table (once two icons are in the Row Labels, you can change their order
by dragging and dropping them).

Drop the "Station Wagon?" into the Column Labels AND into the Sigma Values
areas of the of the PivotTable itself or the PivotTable Field List. Because the
data items are non-numeric, Excel defaults to count these items: how many No
and Yes entries appear.

It is difficult to compare these counts directly, so instead we want to report 
these counts as percents. To do so we right-click the data items, and click
"Value Field Settings..." There we click the "Show values as" and in the "Show
values as" box we clik the "% of row" entry.

Looking at the pivot table, we see that for high salaried families, if the 
family size is large, 75% did (and 25% didn't) buy a station wagon.  And, for
high salaried families, if the family size is small, 7% did (and 93% didn't)
buy a station wagon. Overall, the High Total row (produced because we have 2
criteria in the rows: Salary first and then Family Size) shows 56% bought
(and 44% didn't buy) a station wagon.

Looking at the pivot table, we see that for low salaried families, if the 
family size is large, 73% did (and 27% didn't) buy a station wagon.  And, for
high salaried families, if the family size is small, 4% did (and 96% didn't)
buy a station wagon. Overall, the Low Total row (produced because we have 2
criteria in the rows: Salary first and then Family Size) shows 59% bought
(and 41% didn't buy) a station wagon.

So, although the totals are about the same, each near 50%, we can see that
whether a family has a low or high salary, they are about equally likely to
buy or not buy a station wagon depending just on whether or not they have a
large family size.

Let's analyze the data another way, dragging/dropping the Family Size in front
to the Salary (done as the second pivot table on the worksheet). Notice now
instead of High and Low totals (for Salary) the pivot table shows Large and
Small totals (for Family Size). The Large Total row shows 74% bought (and 26%
didn't buy) a station wagon. Similarly, the Small Total row shows 6% bought
(and 94% didn't buy) a station wagon.

So, here we have a stronger correlation between family size and station wagon
purchases. If the family size is large, independent of their income, 3/4 buy a
station wagon; and if the family size is small, independent of their income,
less than 1/10 buy a station wagon.

I have created pivot charts for both pivot tables. The top is shown on Car
Chart1 and the bottom one is shown on Car Chart2. 

Finally, in the second pivot table, drag and drop Salary from the "Choose
fields to add to report" section on the top of the PivotTable Field List into
the "Drop Page Fields Here" in the Pivot table itself, or move the Salary icon
from the Row Labels Section to the Report Filter section in the PivotTable
Field List. This simplifies the pivot table, as it now has just one selection
for the row (Family Size). We can recalculate the data inside the pivot table
by selecting which "page" to base the calculations on (High Salary, Low Salary,
or both) in the filter activated by the disclosure triangle for Salary. Also
see how the pivot chart changes what it displays when filtering Salary.

In fact, we can manipulate the pivot chart directly (which also changes the
pivot table it came from). Go to the Cars Chart1 worksheet. Click on the
chart and the PivotTable Field List will appear. Then hover over Family Size
(in the "Choose fileds to add to report" window) and click the disclose
triangle, at which point we can filter these values: let's choose to display
information for only Large family sizes. Click OK and watch the chart change to
show High and Low salaries, but display only the values for Large families (see
the legend on the bottom).

After doing so, if we go back to the Cars tab, we will see a condensed Pivot
table, with Family Size filtered to include only large familes. We can unfilter
this category and it will change both on this pivot table and the pivot chart
associated with it.



2) Analyzing The Books Database

In the books database, let's create pivot table for the amount of money spent
on books in different categories by the years they were bought. Because this
database has so many columns, and fills what we can see on the worksheet, let's
create the pivot table on its own worksheet.

We drop Category into the Row Labels and (the year) Bought into the Column
Labels. Typically we put the item with the most values in the row (here there
are more categories than years bought). We drop cost into the Sigman Values,
and specify a field setting ot sum for the summary (the default is count,
because not all cells contain numbers: some cells are blank) and a number as
Currency with no decimal digits.

Notice that the years 1984 and 1998 have very few entries, and (blank) has
none. So, we will use the filter for Bought to eliminate those columns.
Likewise, there is a Category that is (blank), so we use the filter for
Category to elimate that row.

Let's make a pivot chart of this data. The heights if each category show the
cost of the books purchased (the various years show in different colors), with
the total height indicating on which category of books the most money was
spent.

In this database most book purchases were in the General category, followed by
Programming and Theory. Also by inspection, 2002 was a year a lot of money was
spent on Programming and Theory books. Note that I specified an Alignment on
the X-axis to be vertical for the Category names.

It is easy to see a breakdown on the chart by each year Bought. Click on the
chart and the PivotTable Field List will appear. Notice that a funnel already
appears beside the year Bought checked box in the "Choose fileds to add to
report" window). Hover over Bought and click the disclosure triangle, at which
point we can refilter these values. Click Select All twice (to select none) and
then click any particular year, say 2002, and click OK. Watch the chart change
to show the categories and total purchases for just that year.

To make this pivot table a bit more useful, we can put the Publisher icon/label
as a Page Field/Report Filter. Its default is (All), but we can select
individual publishers (and arbitrary combinations) to see whether a publisher
(or publishers) mostly publishes books in some category. For example, if we
click the Books Chart chart, we can click on Publisher (in the "Choose fields
to add to report" in the upper box) and click the disclose triangle to allow us
to filter by publisher. We can specify Addison-Wesley, click OK, and see that
most books from that publisher are Programming and then Language books.

Likewise, we could have selected just this publisher on the pivot chart to see
the results graphically, just as we did for year Bought above.

Finally, also on the Books worksheet, note that the Grand Total on the bottom
row shows the amount spent during each year; the Grand Total on the right
column shows the amount spent on books in each category. If we wanted to see
this data as percentages, we could either use specify the options row % or
col %.

As one last change, click on the Books Chart tab and the pivot chart and 
interchange Category and Bought. Now the chart nicely shows how much money
was spent on these books each year (but the legend is huge, since it contains
all the categories: too big to display so it is truncated). To not see this
breakdown, click the checked box category, to make all these categories
disappear, leaving behind just the total amount spent each year.


4) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel,

Feel more comfortable creating and maniuplating pivot charts and their pivot
tables, including manipulating the table by viewing and manipulating the chart.