Pivot Tables allow you to summarize large quantities of data quickly and easily. Read further to find out how to add multiple Pivot Tables to the same worksheet tab.
Create another PivotTable
Select the source table, choose Insert > PivotTable, and select Existing Worksheet. Choose a blank destination with enough room for the new PivotTable to grow without overlapping other content. You can also place it on a new worksheet when space is limited.
Changing Field Properties
The Field Properties for Column Labels and Row Labels are different from those for Values.
To Change Field Properties:
- Double click on the Label in the Pivot Table area or
- Click on the Label in the Field List and select Field Properties
Values
Format data in the Value Field Settings
PivotTable Analyze >> Active Field group >> Field Settings
Number Format
Sorting Pivot Table Data
To Sort Pivot Table Data:
- Click in the column to be sorted
- Click (A - Z) or (Z - A)
Note: If you want to sort by Subtotals instead of individual Totals you need to collapse those fields before you sort, then expand them again.
Viewing Detail
To View the Detail Behind a Number on the Pivot Table:
- Double Click on a number
- This will create a new sheet in the workbook with the detail records from the database
Note: Remember that each time you do this it creates a new sheet. When you are done with these sheets you should delete them to reduce redundant data in your workbook.
Expanding/Collapsing Fields
If there is more than one field in the Row/Column area, you can collapse and expand the categories.
To Expand/Collapse Fields:
- Click ( - ) to collapse the fields
- Click ( + ) to expand the fields
Refreshing Pivot Table Data
Refresh behavior depends on the Excel version, data source, and settings. Check that the PivotTable reflects current source data, and use Refresh when needed.
To Refresh the Pivot Table:
PivotTable Analyze >> Data group >> Refresh
Changing the Design of the Pivot Table
To Change the Style:
Design >> PivotTable Styles group
Pivot Table Options
To Change the Pivot Table Options:
PivotTable Analyze >> PivotTable group >> Options
Learn More About Excel
Master Excel with the leading Excel training courses in NYC and our premier Excel Bootcamp. Sign up and start creating smart and efficient spreadsheets today!