Pivot Table Fields are the building blocks of a Pivot Table. They allow you to control how data is displayed, summarized, and analyzed.
When you create a Pivot Table, the PivotTable Fields Pane appears on the right side of the screen.
Two Main Sections of Pivot Table Fields
Field List (Top Section)
This shows all column headers from your data source.
Example:
- Name
- Department
- City
- Sales
- Date
You select fields from here to build your report.
Layout Areas (Bottom Section)
There are four important areas:
Rows
- Displays categories vertically
- Used for grouping data
Example: Drag Department to Rows.
Columns
- Displays categories horizontally
Example: Drag City to Columns.
Values
- Performs calculations
- Automatically summarizes numbers
Example: Drag Sales to Values → Shows Sum of Sales
You can change calculation to:
- Sum
- Count
- Average
- Max
- Min
Filters
- Applies report-level filter
- Controls the entire Pivot Table
Example: Drag Date to Filters to view a specific month.
Example Setup
If you want:
Total Sales by Department and City
- Department → Rows
- City → Columns
- Sales → Values
Excel automatically calculates totals.
Rearranging Fields
You can drag and drop fields between:
- Rows
- Columns
- Values
- Filters
This changes the structure of the report instantly.
Remove a Field
- Uncheck the field from the list
OR - Drag it out of the layout area
Expand and Collapse Data
If you use multiple Row fields:
Example:
- Department
- Employee Name
You can expand (+) or collapse (–) grouped data.
Important Tips
- Text fields usually go to Rows or Filters
- Numeric fields usually go to Values
- Keep field names clear and meaningful
- Avoid blank headers in source data
Why Pivot Table Fields Are Important
Pivot Table Fields allow you to:
- Customize reports
- Analyze data from different angles
- Create dynamic summaries
- Build professional dashboards
Conclusion
Understanding Pivot Table Fields is essential for mastering Pivot Tables. By correctly placing fields in Rows, Columns, Values, and Filters, you can turn raw data into powerful and meaningful reports.