How PivotTable and PivotChart Reports Work
PivotTables and PivotCharts are powerful tools for automatically summarizing and analyzing data without ever having to add a formula or function. As the name implies, you start with a list in table format, snap the rows and columns into position on a grid, and end up with a sorted, grouped, summarized, totaled, and subtotaled report. PivotTable reports are best for cross-tabulating lists—the more categories, the better. You can reduce a list of thousands of items to a single line, showing totals by category or quarter. Or you can create complex, multilevel groupings that show total sales by employee, grouped by product category and by quarter. You can hide or show detail for each group with a quick double-click. You can change the view or grouping in literally seconds, just by dragging items on or off the sheet and moving them between row, column, and page fields.
Start with a list that contains multiple fields, and then use Excel's PivotTable Wizard to set up a blank PivotTable page with just a few clicks. Instead of sorting your list and entering formulas and functions, you drag fields around on the PivotTable page to create a new view of your list—Excel groups the data and adds summary formulas automatically. PivotCharts are the visual equivalent of PivotTables, letting you create killer charts just as quickly, by dragging fields on a chart layout page.
→ For more details on how to create and work with lists, see Chapter 25, "Working with Lists and Databases,".
→ PivotCharts add a new dimension to PivotTable reports, see "Creating and Editing PivotCharts".
If you used PivotTables in Excel 2000, you'll barely notice the difference in Excel 2002 (although you'll probably like the usability tweaks). If you tried to create or edit a PivotTable in Excel 97 and gave up in frustration, try again. The process of creating and editing PivotTables is dramatically easier in Excel 2000/2002 than in Excel 97, especially when it comes to changing a PivotTable on-the-fly. PivotCharts were introduced in Excel 2000.
Unlike subtotals and outlines, which modify the structure of your list to display summaries, PivotTables and PivotCharts create new, independent elements in your workbook. When you add or edit data in a list, the changes show up in your PivotTables and PivotCharts as well; because they're separate elements, you can easily change the structure of a PivotTable or PivotChart, too, and your changes won't mess up the data in the underlying list. Using interactive Web components, you can also make PivotTables available to other people via a Web browser.
Figure 27.1 shows the four main drop zones on a blank PivotTable page. The PivotTable toolbar includes buttons for every field in your list. Use row fields and column fields to define how you want Excel to group your list. Data items define which fields contain the information you want to summarize. Page fields let you further refine your view by displaying a separate PivotTable for each item in a group, as though the table were on its own virtual page. You can use multiple row fields, column fields, or both, and you can specify which summary action you want Excel to perform on data items—the sum, average, or count of all related values, for instance.
Figure 27.1. Drag field buttons from the toolbar and drop them on the layout to build a PivotTable on-the-fly.
What can you do with a PivotTable? The number of uses is limited only by your imagination. Despite their dramatically different structures, for example, each of the following four PivotTables started with the same list of information about publicly traded stocks. In its raw form, with its grand total of 37,400 separate data points, the list is a prescription for information overload. Each of the 2,200 rows contains 17 data fields for an individual publicly traded company, including its name, ticker symbol, and industry category, the exchange on which it trades, its high and low stock price for the past year, and financial measurements such as net profit margin and return on equity.
Figure 27.2 shows a simple PivotTable that lets you see at a glance how many companies are in each industry category, along with the average increase in stock price from companies in that category over the past year. This PivotTable consists of a single row field and two data items.
Figure 27.2. With no column fields and only one row field, this PivotTable quickly counts the number of companies in each category and calculates the average price change for the year.
In Figure 27.3, more detail is added, displaying individual statistics for each company, and grouping the detail rows in alphabetical order by industry name. For this PivotTable, the data is arranged in report format, similar to the banded database reports Access and other database management programs produce. Note that this PivotTable includes four data items instead of two, and a slew of Excel formatting options are used to make the report more readable—changing fonts and font sizes, aligning type and adding background shading, and standardizing the number of decimal points in each column.
Figure 27.3. To hide gridlines and group-related items in bands such as these, choose a report format instead of the default table layout.
→ For details on how to create similar reports from an Access database, see "Building Great Forms and Reports".
To slice the data even more finely and add an extra analytical dimension, you can drag more buttons from the PivotTable toolbar to the row and column fields. Each row in the PivotTable is grouped using unique values in two categories, and there are two column headings as well, one for each unique value in the "Split in Last Year" column field. (To make the PivotTable easier to read, the column headings were renamed from Yes and No to Split and No Split.) At the intersection of each row and column in the PivotTable, Excel counts the number of companies and calculates the average income per employee for all rows that match the row and column fields.
The resulting PivotTable, shown in Figure 27.4, is a concise and crystal-clear cross- tabulation, giving you a side-by-side analysis of the number of stocks that split in the past year versus those that didn't, broken down by industry category and exchange.
Figure 27.4. Add a column field to quickly compare related data points. Notice that the worksheet pane is frozen to keep headings visible when scrolling, just as with an ordinary worksheet.
There are literally hundreds of options in even a modestly complex PivotTable, but a PivotTable doesn't have to be large or complex to be effective. The PivotTable in Figure 27.5, for example, neatly summarizes all 37,400 data points in just a few rows and columns.
Figure 27.5. Notice the grand totals under the rows in this PivotTable. Use the page field in the top-left corner to filter the entire list.
To produce this example, we used two column fields, two row fields, and one page field—a drop-down list that lets us filter the records in the entire table. Choosing (All) from the page field shows a summary of all data in the list; by selecting a different entry from the drop-down list, you can show the same breakdown for each industry name. Select one category at a time to flip through a series of otherwise identical PivotTables that focus on each category.
The layout Excel produced automatically included totals for each row and column; we kept only the grand total at the bottom of the PivotTable. We had to modify other default settings as well, including changing the default formula to calculate the average of our data items. To make the headings and totals easier to read, we did some rewording, and then changed fonts and alignment, added shading, and wrapped text.