Pivot Table Examples

Sales Analysis by Region and Product

One of the most common pivot table applications is analyzing sales performance across multiple dimensions.

Scenario: You have transaction data including product information, regional data, and revenue amounts. You want to understand which products sell best in which regions.

Configuration:

Setting	Value
Row Group 1	Product Category
Row Group 2	Customer Type
Column Group	Sales Region
Value 1	Total Amount (Sum)
Value 2	Unit Price (Average)

What this reveals:

Total revenue for each product category in each region
How different customer segments perform within each category
Average transaction size across regions (helpful for pricing analysis)
Whether certain product/region combinations underperform or outperform

Reading the output: Scan the row summaries to see which product categories generate the most total revenue. Compare across columns to identify regional strengths and weaknesses. Expand Row Group 2 to see if B2B or B2C customers drive the results in each category.

Time-Based Performance Tracking

Pivot tables excel at revealing trends over time periods.

Scenario: You're tracking project work and want to analyze team performance across quarters to identify seasonal patterns and resource utilization trends.

Configuration:

Setting	Value
Row Group 1	Project Phase or Department
Row Group 2	Team Member or Resource Type
Column Group	Quarter (or Month)
Value 1	Hours Worked (Sum)
Value 2	Tasks Completed (Count)
Value 3	Efficiency Rating (Average)

What this reveals:

How workload distributes across departments over time
Whether certain phases create bottlenecks in specific quarters
Individual contributor patterns within each department
Efficiency trends—is the team getting faster or slower?

Reading the output: Look for columns (time periods) where Hours Worked is high but Tasks Completed is low—this suggests efficiency issues. Compare Row Group 2 entries within the same department to identify top performers and those who might need support.

Customer Behavior Analysis

Understanding how different customer segments interact with your products and channels informs marketing and inventory decisions.

Scenario: You want to analyze purchasing patterns across customer types, product preferences, and sales channels.

Configuration:

Setting	Value
Row Group 1	Customer Segment or Type
Row Group 2	Product Category
Column Group	Purchase Channel (Online, In-Store, Phone)
Value 1	Purchase Amount (Sum)
Value 2	Transaction Count (Count)
Value 3	Average Order Value (Average)

What this reveals:

Which customer segments prefer which channels
Product category preferences by customer type
Whether online customers spend more or less per transaction than in-store customers
Volume patterns—many small purchases vs. few large purchases

Reading the output: High Transaction Count with low Average Order Value suggests a segment that makes frequent small purchases—consider subscription models or bulk discounts. Look for segment/channel mismatches where you might be under-serving a customer type's preferred channel.

Operational Efficiency Metrics

Identify bottlenecks, optimize resource allocation, and improve process performance.

Scenario: You're analyzing operational data to find efficiency opportunities across processes, shifts, and time periods.

Configuration:

Setting	Value
Row Group 1	Process or Department
Row Group 2	Status or Priority Level
Column Group	Time Period or Shift
Value 1	Processing Time (Average)
Value 2	Volume Handled (Sum)
Value 3	Error Rate (Average)

What this reveals:

Which processes are slowest and when
How priority levels affect processing time
Shift-by-shift performance comparison
Correlation between volume and error rates

Reading the output: Look for intersections where Processing Time (Average) spikes—these are your bottlenecks. If Error Rate increases when Volume Handled is high, you may have capacity issues. Compare shifts to identify best practices that faster shifts might share with slower ones.

Inventory and Stock Analysis

Scenario: You manage inventory across multiple locations and need to track stock levels, turnover, and reorder patterns.

Configuration:

Setting	Value
Row Group 1	Product Category
Row Group 2	Product SKU
Column Group	Warehouse Location
Value 1	Current Stock (Sum)
Value 2	Units Sold This Period (Sum)
Value 3	Days of Supply (Average)

What this reveals:

Stock distribution across locations
Which products move fastest at each location
Potential overstock situations (high stock, low sales)
Potential stockout risks (low days of supply)

Financial Period Comparison

Scenario: You need to compare financial performance across time periods for budgeting and forecasting.

Configuration:

Setting	Value
Row Group 1	Department or Cost Center
Row Group 2	Expense Category
Column Group	Fiscal Period (Month or Quarter)
Value 1	Actual Spend (Sum)
Value 2	Budget Amount (Sum)
Value 3	Variance (Average)

What this reveals:

Spending patterns over time by department
Budget adherence by category
Which expense types drive over/under-spending
Seasonal spending patterns

Configuration Tips Across All Examples

Start with the question, not the data. Define what you're trying to learn before selecting fields. "Which regions underperform?" leads to a different configuration than "Which products need more inventory?"

Build incrementally. Start with Row Group 1, Column Group, and one Value field. Verify the output makes sense. Then add complexity—Row Group 2, additional values—one element at a time.

Test with real data. Sample or test data might not reveal issues like too many unique values, unexpected nulls, or sparse intersections. Always verify with production data before deploying.

Document your configuration. When you create a pivot table that answers an important business question, document what it shows and how to interpret it. Future users (including future you) will thank you.