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.
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
PIVOT TABLE TOOLS – 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 are 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
Unlike most formulas, Pivot Tables do not automatically update when the underlying data changes.
To Refresh the Pivot Table:
PIVOT TABLE TOOLS – ANALYZE >> Data group >> Refresh
Changing the Design of the Pivot Table
To Change the Style:
PIVOT TABLE TOOLS – DESIGN >> Pivot Table Styles group
Pivot Table Options
To Change the Pivot Table Options:
PIVOT TABLE TOOLS-ANALYZE >> Pivot Table 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!