How to Create an Excel Pivot Table
Pivot tables are a useful way of analyzing large sets of data in Excel, helping you organize information into summaries and reports. Excel 2013 has a tool that creates recommended tables to suit your data. Once you've chosen a pivot table, you can manipulate it to fit your requirements or to change the way it analyzes and presents information.
Check the data in your worksheet. A pivot table works only if you have column or table headings and no blank rows or columns, although you can have empty cells.
Select any cell in the range of data on which to base the table. Choose "Insert" on the menu bar and then "Recommended PivotTables." Depending on the data in your worksheet, Excel suggests various summaries using headings, labels and formulae. Scroll through the options -- you can preview a table by selecting it -- and choose the one that best suits your needs. Select "OK." Excel inserts the table into a new worksheet and opens the PivotTable Field pane on the right of the sheet.
Use the options on the PivotTable Field pane to make changes to your table. Check or uncheck fields in the PivotTable Fields list to add or remove them from the summary. You can also drag them down into the Filters, Columns, Rows or Values areas to change the data in a table or the way in which it is shown. To remove fields from these areas, uncheck them or drag them to the side. Once you're happy with the layout, select "Update."
Tips & Warnings
- If you want to create your own table rather than use a recommended template, select "PivotTable" from the Insert tab.
- You can also use pivot tables to analyze data from multiple or external sources.
- Earlier versions of Excel included a PivotChart Wizard. If you used this in the past and would like to use it in Excel 2013, press and hold down the "Alt," "D" and "P" keys simultaneously.
- If you want to delete a pivot table, highlight the whole table and press the “Delete” button. You can also delete a table by deleting its worksheet.
- To create a pivot chart, or a chart and a table at the same time, select "PivotChart" on the Insert tab and then "PivotChart" or "PivotChart & PivotTable."
- If you check fields in the PivotTable Fields pane to add them to a table, Excel makes an educated guess on where they should go. This may not put them where you want -- you may find it easier to drag them to the right area.
- Pivot tables do not automatically update when you change their underlying data. To refresh a table, click on one of its cells, select "Analyze" from the PivotTable Tools tab and then "Refresh." To update multiple tables, select "Refresh All."