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 dif ferent ways. Trying to accomplish the same task by entering formulas manually would take days. Also, because PivotTables 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 containing a month, department, budget category, and amount, you can easily re-create that same layout in PivotTable form—and you'll have many more analytical options available to you later.

→ For more details on how to use Microsoft Query to pull data from an external database, see "Creating Links to External Databases".