Part V: Analyzing Data with Excel

Copying a Pivot Table

A pivot table is very flexible, but it does have some limitations. For example, you can’t add new rows or columns, change any of the calculated values, or enter formulas within the pivot table. If you want to manipulate a pivot table in ways not normally permitted, make a copy of it.

To copy a pivot table, select the entire table and choose Home Clipboard Copy (or, press Ctrl+C). Then select a new worksheet and choose Home Clipboard Paste Paste Values. The pivot table formatting is not copied — even if you repeat the operation and use the Formats option in the Paste Special dialog box.

To copy the pivot table and its formatting, use the Office Clipboard to paste. If the Office Clipboard is not displayed, click the dialog box launcher in the bottom right of the Home Clipboard group.

The contents of the pivot table are copied to the new location so that you can do whatever you like to them.

Note that the copied information is not a pivot table, and it is no longer linked to the source data. If the source data changes, your copied pivot table will not reflect these changes.

More Pivot Table Examples

To demonstrate the flexibility of this feature, I created some additional pivot tables. The examples use the bank account data and answer the questions posed earlier in this chapter (see “A pivot table example”).

Question 1

What is the daily total new deposit amount for each branch?

Figure 34.13 shows the pivot table that answers this question.

•The Branch field is in the Column Labels section.

•The Date field is in the Row Labels section.

•The Amount field is in the Values section and is summarized by Sum.

Note that the pivot table can also be sorted by any column. For example, you can sort the Grand Total column in descending order to find out which day of the month had the largest amount of new funds. To sort, just right-click any cell in the column to sort and choose Sort from the shortcut menu.

708

Chapter 34: Introducing Pivot Tables

FIGURE 34.13

This pivot table shows daily totals for each branch.

Question 2

Which day of the week accounts for the most deposits?

Figure 34.14 shows the pivot table that answers this question.

•The Weekday field is in the Row Labels section.

•The Amount field is in the Values section and is summarized by Sum.

I added conditional formatting data bars to make it easier to see how the days compare.

Cross-Reference

See Chapter 20 for more information about conditional formatting. n

709

Part V: Analyzing Data with Excel

FIGURE 34.14

This pivot table shows totals by day of the week.

Question 3

How many accounts were opened at each branch, broken down by account type?

Figure 34.15 shows a pivot table that answers this question.

•The AcctType field is in the Column Labels section.

•The Branch field is in the Row Labels section.

•The Amount field is in the Values section and is summarized by Count.

The most common summary function used in pivot tables is Sum. In this case, I changed the summary function to Count. To change the summary function to Count, right-click any cell in the Value area and choose Summarize Data By Count from the shortcut menu.

FIGURE 34.15

This pivot table uses the Count function to summarize the data.

Question 4

What’s the dollar distribution of the different account types?

Figure 34.16 shows a pivot table that answers this question. For example, 253 of the new accounts were for an amount of $5,000 or less.

710

Chapter 34: Introducing Pivot Tables

FIGURE 34.16

This pivot table counts the number of accounts that fall into each value range.

This pivot table is unusual because it uses only one field: Amount.

•The Amount field is in the Row Labels section (grouped).

•The Amount field is also in the Values section and is summarized by Count.

•A third instance of the Amount field is the Values section, summarized by Percent of Total.

When I initially added the Amount field to the Row Labels section, the pivot table showed a row for each unique dollar amount. I right-clicked one of the Row Labels and chose Group from the shortcut menu. Then I used the Grouping dialog box to set up bins of $5,000 increments.

The second instance of the Amount field (in the Values section) is summarized by Count. I rightclicked a value and chose Summarize Data By Count from the shortcut menu.

I added another instance of Amount to the Values section, and I set it up to display the percentage. I right-clicked a value in column C and chose Show Values As % of Grand Total. This option is also available in the Show Values As tab of the Value Field Settings dialog box.

Question 5

What types of accounts do tellers open most often?

Figure 34.17 shows that the most common account opened by tellers is a Checking account.

•The AcctType field is in the Row Labels section.

•The OpenedBy field is in the Report Filters section.

711

Part V: Analyzing Data with Excel

•The Amount field is in the Values section (summarized by Count).

•A second instance of the Amount field is in the Values section (summarized by % of Total).

This pivot table uses the OpenedBy field as a Report Filter and is showing the data only for Tellers. I sorted the data so that the largest value is at the top, and I also used conditional formatting to display data bars for the percentages.

Cross-Reference

See Chapter 20 for more information about conditional formatting. n

FIGURE 34.17

This pivot table uses a Report Filter to show only the Teller data.

Question 6

How does the Central branch compare with the other two branches?

Figure 34.18 shows a pivot table that sheds some light on this rather vague question. It simply shows how the Central branch compares with the other two branches combined.

•The AcctType field is in the Row Labels section.

•The Branch field is in the Column Labels section.

•The Amount field is in the Values section.

I grouped the North County and Westside branches together and named the group Other. The pivot table shows the amount, by account type. I also created a pivot chart for good measure. See Chapter 35 for more information about pivot charts.

712

Chapter 34: Introducing Pivot Tables

FIGURE 34.18

This pivot table (and pivot chart) compares the Central branch with the other two branches combined.

Question 7

In which branch do tellers open the most checking accounts for new customers?

Figure 34.19 shows a pivot table that answers this question. At the Central branch, tellers opened 23 checking accounts for new customers.

•The Customer field is in the Report Filters section.

•The OpenedBy field is in the Report Filters section.

•The AcctType field is in the Report Filters section.

•The Branch field is in the Row Labels section.

•The Amount field is in the Values section, summarized by Count.

This pivot table uses three Report Filters. The Customer field is filtered to show only New, the OpenedBy field is filtered to show only Teller, and the AcctType field is filtered to show only Checking.

713