Menu
Excel Tutorial
Excel Pivot Tables
PivotTable is a powerful tool in Excel. It organizes, summarizes, and analyzes your data to reveal its meanings.
Imagine an Excel worksheet of sales figures for a company with thousands of rows of data. Everyone has questions about what the data means. PivotTable reports make it all so easy to compare, reveal and analyze the data by displaying different views of data and turning data into information that makes sense.
Creating a Pivot Table
- Open the workbook where you want to create the PivotTable report.
- On the Data menu, click PivotTable and PivotChart Report.
- In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under What kind of report do you want to create?
- Follow the instructions in step 2 of the wizard.
- Follow the instructions in step 3 of the wizard, and then decide whether to lay out the report onscreen or in the wizard.
- Usually you can lay out the report onscreen, and this method is recommended. Use the wizard to lay out the report only if you expect retrieval from a large external data source to be slow, or you need to set page fields to retrieve data one page at a time. If you aren't sure, try laying out the report onscreen. You can return to the wizard if necessary.
- Do one of the following:
Lay out the report onscreen
- From the PivotTable Field List window, drag the fields with data that you want to display in rows to the drop area labeled Drop Row Fields Here.
- If you don't see the field list, click within the outlines of the PivotTable drop areas, and make sure Show Field Listis pressed in.
- Drag fields with data that you want to display across columns to the drop area labeled Drop Column Fields Here.
- Drag fields that contain the data that you want to summarize to the area labeled Drop Data Items Here.
- If you add more than one data field, arrange these fields in the order you want: Right-click a data field, point to Order on the shortcut menu, and use the commands on the Order menu to move the field.
- Drag fields that you want to use as page fields to the area labeled Drop Page Fields Here.
- To rearrange fields, drag them from one area to another.
- To remove a field, drag it out of the PivotTable report.
- To hide the drop area outlines, click a cell outside the PivotTable report.
Lay out the report in the wizard
- If you've exited from the wizard, click PivotTable and PivotChart Report on the Data menu to return to it.
- In step 3 of the wizard, click Layout.
- From the group of field buttons on the right, drag the fields that you want onto the ROW and COLUMN areas in the diagram.
- Drag the fields that contain the data that you want to summarize onto the DATA area.
- Drag fields that you want to use as page fields onto the PAGE area.
- If you want Excel to retrieve data one page at a time, so you can work with large amounts of source data, double-click the page field, click Advanced, click Query external data source as you select each page field item, and then click OK twice. (This option is unavailable for some types of source data, including OLAP databases and Office Data Connections.)
- To rearrange fields, drag them from one area to another. Some fields can only be used in some of the areas; if you drop a field in an area where it can't be used, the field won't appear in the area.
- To remove a field, drag it out of the diagram.
- When you are satisfied with the layout, click OK, and then click Finish.