How to Use Pivot Table Fields in Excel: Step-by-Step Guide

In Excel, you use Pivot Table Fields to drag and drop data fields into areas like Rows, Columns, Values, and Filters to organize and summarize your data. This lets you quickly analyze large data sets by grouping, counting, or summing values without changing the original data.

Syntax

A pivot table field is a data column from your source table that you add to one of four areas in the Pivot Table Fields pane:

• Rows: Fields placed here become row labels.
• Columns: Fields placed here become column labels.
• Values: Fields placed here are summarized (sum, count, average).
• Filters: Fields placed here let you filter the whole pivot table.

You drag fields from the field list into these areas to build your pivot table layout.

text

Pivot Table Fields Pane:
- Rows: Drag fields here to group data by rows
- Columns: Drag fields here to group data by columns
- Values: Drag numeric fields here to summarize data
- Filters: Drag fields here to filter the entire pivot table

Example

This example shows how to create a pivot table from sales data and use fields to summarize total sales by product and region.

text

Source Data:
| Product | Region  | Sales |
|---------|---------|-------|
| Apple   | East    | 100   |
| Banana  | West    | 150   |
| Apple   | West    | 200   |
| Banana  | East    | 120   |

Steps:
1. Select the data range.
2. Insert > PivotTable.
3. In Pivot Table Fields pane:
   - Drag 'Product' to Rows.
   - Drag 'Region' to Columns.
   - Drag 'Sales' to Values (default sum).

Resulting Pivot Table:
| Product | East | West | Grand Total |
|---------|------|------|-------------|
| Apple   | 100  | 200  | 300         |
| Banana  | 120  | 150  | 270         |
| Grand Total | 220 | 350 | 570       |

Output

| Product | East | West | Grand Total |
|---------|------|------|-------------|
| Apple   | 100  | 200  | 300         |
| Banana  | 120  | 150  | 270         |
| Grand Total | 220 | 350 | 570       |

Common Pitfalls

• Dragging non-numeric fields into the Values area can default to counting instead of summing.
• Not refreshing the pivot table after updating source data causes outdated results.
• Confusing Rows and Columns areas can lead to unexpected layout.
• Using filters incorrectly may hide data unintentionally.

text

Wrong:
- Dragging 'Product' to Values area results in count of products.

Right:
- Drag numeric field like 'Sales' to Values area for sum calculation.

Quick Reference

Pivot Table Field Area	Purpose	Example Use
Rows	Groups data by rows	Product names
Columns	Groups data by columns	Regions like East, West
Values	Summarizes data (sum, count)	Sum of Sales
Filters	Filters entire pivot table	Filter by Year or Category

Key Takeaways

Drag fields into Rows, Columns, Values, and Filters areas to build your pivot table.

Use numeric fields in Values area to summarize data by sum, count, or average.

Refresh pivot tables after changing source data to update results.

Place categorical fields in Rows or Columns to organize data layout.

Use Filters area to quickly narrow down data shown in the pivot table.