Pivot Table Fields (Rows, Columns, Values, Filters)0%

Sorting, Filtering and Summarizing Pivot Data

Pivot Table Fields (Rows, Columns, Values, Filters)

Beginner12 min read•Updated: Jul 11, 2026

Pivot Table Fields (Rows, Columns, Values, Filters)

The true agility of Pivot Tables lies in the Four Quadrants of the PivotTable Fields task pane. By dragging, reordering, and nesting fields across these zones, you can restructure massive reports in seconds without modifying the underlying data.

1. The Four Pivot Table Quadrants Explained

Visual Architecture Blueprint

+-----------------------------------------------------------------------------------+
| 1. FILTERS (Top-Left)                                                            |
| Acts as a global report-level filter. Lets users filter the entire Pivot Table by |
| a higher-level category (e.g., Year = 2026 or Country = "India").                 |
+-----------------------------------------------------------------------------------+
| 2. COLUMNS (Top-Right)                                                           |
| Arranges the unique values of the selected field horizontally across column      |
| headers (e.g., Months: Jan, Feb, Mar, or Product Categories).                    |
+-----------------------------------------------------------------------------------+
| 3. ROWS (Bottom-Left)                                                             |
| Arranges unique values vertically down the left side of the table (e.g., Sales Rep|
| names, Departments, Customer IDs).                                               |
+-----------------------------------------------------------------------------------+
| 4. VALUES (Bottom-Right)                                                          |
| Contains the numeric fields to calculate and aggregate (e.g., Sum of Revenue,    |
| Average Unit Price, Count of Orders).                                            |
+-----------------------------------------------------------------------------------+

2. Multi-Level Hierarchical Grouping

You are not limited to one field per quadrant. Dragging multiple fields into the Rows or Columns areas establishes visual reporting hierarchies:

Example: Nested Row Hierarchy

1
Drag Department into Rows.
2
Drag Sub-Category into Rows directly beneath Department.
3
Drag Employee Name into Rows below Sub-Category.

Result: Excel creates expandable and collapsible tree levels (with + and - buttons) allowing managers to drill down from high-level departmental spending down to individual employee expenses.

3. Customizing Value Field Settings

By default, numeric fields placed into the Values quadrant default to Sum of [Field]. To modify how the data is aggregated or displayed:

1
Click the field inside the Values box.
2
Select Value Field Settings... from the popup menu.
3
In the dialog box, you have two key tabs:

Summarize Values By: Switch aggregation from Sum to Count, Average, Max, Min, or Product.
Show Values As: Express values as comparative metrics rather than raw dollars:
% of Grand Total: Calculates each category's percentage contribution to overall business revenue.
% of Column Total / % of Row Total: Segment contributions.
Running Total In: Cumulative year-to-date tracking.
Difference From: Variance against previous period or benchmark.

1
Click Number Format in the bottom-left corner to apply Currency, Accounting, or Decimal formatting directly across all Pivot Table cells simultaneously!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which Pivot Table quadrant controls the global, top-level filtering for the entire report?

Question 2

What happens when you place two fields (e.g., Region and Salesperson) into the 'Rows' area?

Question 3

Where do you change a Pivot Table calculation from 'Sum of Sales' to 'Average of Sales'?

Question 4

Which option under the 'Show Values As' tab allows you to see what percentage each product contributes to overall company revenue?

Question 5

Why should you format numbers using the 'Number Format' button inside Value Field Settings rather than standard Home tab formatting?

Next Lesson

Sorting, Filtering and Summarizing Pivot Data

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating Your First Pivot Table	Sorting, Filtering and Summarizing Pivot Data

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Sorting, Filtering and Summarizing Pivot Data

Pivot Table Fields (Rows, Columns, Values, Filters)

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Pivot Table Fields (Rows, Columns, Values, Filters)

1. The Four Pivot Table Quadrants Explained

Visual Architecture Blueprint

+-----------------------------------------------------------------------------------+
| 1. FILTERS (Top-Left)                                                            |
| Acts as a global report-level filter. Lets users filter the entire Pivot Table by |
| a higher-level category (e.g., Year = 2026 or Country = "India").                 |
+-----------------------------------------------------------------------------------+
| 2. COLUMNS (Top-Right)                                                           |
| Arranges the unique values of the selected field horizontally across column      |
| headers (e.g., Months: Jan, Feb, Mar, or Product Categories).                    |
+-----------------------------------------------------------------------------------+
| 3. ROWS (Bottom-Left)                                                             |
| Arranges unique values vertically down the left side of the table (e.g., Sales Rep|
| names, Departments, Customer IDs).                                               |
+-----------------------------------------------------------------------------------+
| 4. VALUES (Bottom-Right)                                                          |
| Contains the numeric fields to calculate and aggregate (e.g., Sum of Revenue,    |
| Average Unit Price, Count of Orders).                                            |
+-----------------------------------------------------------------------------------+

2. Multi-Level Hierarchical Grouping

You are not limited to one field per quadrant. Dragging multiple fields into the Rows or Columns areas establishes visual reporting hierarchies:

Example: Nested Row Hierarchy

1
Drag Department into Rows.
2
Drag Sub-Category into Rows directly beneath Department.
3
Drag Employee Name into Rows below Sub-Category.

Result: Excel creates expandable and collapsible tree levels (with + and - buttons) allowing managers to drill down from high-level departmental spending down to individual employee expenses.

3. Customizing Value Field Settings

By default, numeric fields placed into the Values quadrant default to Sum of [Field]. To modify how the data is aggregated or displayed:

1
Click the field inside the Values box.
2
Select Value Field Settings... from the popup menu.
3
In the dialog box, you have two key tabs:

Summarize Values By: Switch aggregation from Sum to Count, Average, Max, Min, or Product.
Show Values As: Express values as comparative metrics rather than raw dollars:
% of Grand Total: Calculates each category's percentage contribution to overall business revenue.
% of Column Total / % of Row Total: Segment contributions.
Running Total In: Cumulative year-to-date tracking.
Difference From: Variance against previous period or benchmark.

1
Click Number Format in the bottom-left corner to apply Currency, Accounting, or Decimal formatting directly across all Pivot Table cells simultaneously!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which Pivot Table quadrant controls the global, top-level filtering for the entire report?

Question 2

What happens when you place two fields (e.g., Region and Salesperson) into the 'Rows' area?

Question 3

Where do you change a Pivot Table calculation from 'Sum of Sales' to 'Average of Sales'?

Question 4

Which option under the 'Show Values As' tab allows you to see what percentage each product contributes to overall company revenue?

Question 5

Why should you format numbers using the 'Number Format' button inside Value Field Settings rather than standard Home tab formatting?

Next Lesson

Sorting, Filtering and Summarizing Pivot Data

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating Your First Pivot Table	Sorting, Filtering and Summarizing Pivot Data

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next