What Is a Pivot Table
Pages: 1, 2, 3, 4, 5, 6

  1. Choose PivotTable for the type of report.

    You can also create a pivot chart and pivot table in one step by selecting "PivotChart report (with PivotTable report)." The end of this chapter explains pivot charts.

  2. Click Next to continue.

    In the final step, you can choose a location for the pivot table.

  3. Select "New worksheet" to create a new worksheet for your pivot table, which is usually the easiest.

    Pivot tables are fairly complex creations, so the easiest way to manage them (and keep them separate from the rest of your data) is to pop them into a new worksheet. Alternatively, you can choose "Existing worksheet" to insert your pivot table on a worksheet that's already in your workbook. In this case, you need to specify the cell reference for the top-left corner of the pivot table. If there's data under this cell or to the right of it, Excel may overwrite it as it generates the pivot table. Usually, the best approach is to place a new pivot table on a separate worksheet.

    The last step of the wizard also gives you the chance to set some miscellaneous options (by clicking the Options button) or start laying out your pivot table (by clicking the Layout button). Stay away from these options for now. You'll find that it's easier to organize a pivot table on your worksheet after you create it.

Figure 6. This worksheet shows a newly created pivot table that doesn't yet contain any information. When you select a region inside the pivot table, Excel displays the PivotTable toolbar and shows the PivotTable Field List (as shown on the right), which contains a list of all the columns in the data that you're summarizing.

Understanding Pivot Table Regions

To build a pivot table, you simply drag columns from the PivotTable Field List on the right side of the Excel window, and drop them into one of the regions on the pivot table. As you work, Excel generates the pivot table, updating it dynamically as you add, rearrange, or remove columns.

NOTE: Excel refers to all your source data's columns as fields.

To understand how to fill up a pivot table with data, you need to know how each region works. Altogether, a pivot table includes four regions:

  • Data Items. These are the fields that you want to subtotal. Usually, this is the numeric information you use to build averages and totals.

  • Column Fields. Often, you want to subdivide your data in more than one way at once. Like row fields, column fields create groups. Excel adds one column for each group, according to the field you choose. For example, if you use Category for a row field and Ship Country for a column field, you'll wind up with a table that divides sales figures into rows by product category, and then divides each category row into columns by country.

It really doesn't matter whether you use a field for row grouping or column grouping. The pivot table shows the same data either way, but one approach may be more readable than another. For example, if you have a field with extremely long names, it will probably work better as a row field than as a column field (where it would stretch out the width of the whole column).

Also, consider how many different groups you want to create. For example, if you want your pivot table to compare sales by category and country, and your list features 5 categories and 20 countries, you'll probably be best off if you use the country field as a row field and the category field as a column field. That keeps the table long and narrow, which is easier to read and print.

Pages: 1, 2, 3, 4, 5, 6

Next Pagearrow