Share this Page URL

Chapter 30. Using PivotTables and PivotC... > When Should You Use a PivotTable? - Pg. 784

Using PivotTables and PivotCharts 784 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. When Should You Use a PivotTable? PivotTables have several advantages over other worksheet models. Using the PivotTable Wizard, it's easy to create a PivotTable that summarizes all or part of a list in dozens of different ways. Trying to accomplish the same task by entering formulas manually would take days. Also, because Pivot- Tables and PivotCharts do not change your existing data or its arrangement on the worksheet, you can freely experiment with different PivotTable layouts. Use the Undo button to roll back any changes you make in a PivotTable layout. If you want to start over, you can delete the PivotTable page and run the wizard again. PivotTables are the correct choice when all your data is in a list or in an external database you can query from Excel. PivotTables are not appropriate for structured worksheet models that include data- entry cells, subtotals, and summary rows. A PivotTable won't do much good on an annual budget worksheet, for example, because it already includes rows, columns, and subtotals. On the other hand, if you enter the raw data in a list (or import it from an external database), with each row