🚀 Data Analyst Roadmap — Part 9
📊 Excel — Level 8: PivotTables, PivotCharts & Interactive Analysis
Now that you understand Excel formulas and dynamic functions, it's time to learn one of the most important Excel features for Data Analysts: PivotTables.
A PivotTable allows you to take a large dataset and quickly summarize it without writing complicated formulas.
For example, imagine you have 50,000 sales transactions. Your manager asks: "Show me total sales by region, product category, and month." Doing this manually would take a lot of time. With a PivotTable, you can summarize the data in seconds.
1️⃣ What Is a PivotTable?
A PivotTable is an Excel tool that lets you summarize, group, compare and analyze large datasets.
Raw data example:
Order ID | Date | Region | Product | Sales | Profit
1001 | Jan | North | Laptop | 80,000 | 12,000
1002 | Jan | South | Mouse | 2,000 | 500
Instead of manually calculating totals, create a PivotTable.
2️⃣ Creating a PivotTable
First select your dataset.
Then: Insert → PivotTable → Usually select New Worksheet → OK.
You'll see four main areas: Rows, Columns, Values, Filters. These four areas are the foundation.
3️⃣ Understand the Rows Area
Rows determines what you want to group by.
Drag Region → Rows → You get North, South, West grouped.
4️⃣ Understand the Values Area
Values contains the calculation. Drag Sales → Values → Sum of Sales.
Region | Total Sales → North 155,000, South 92,000, West 5,000.
Now you've answered: "How much did each region sell?"
5️⃣ Understand the Columns Area
Allows you to compare categories horizontally. Region → Rows, Product → Columns, Sales → Values → You get Region x Product matrix.
6️⃣ Understand the Filters Area
Lets you filter entire PivotTable.
Region → Rows, Sales → Values, Year → Filters → Select 2026 to see only 2026 results.
7️⃣ The Four PivotTable Areas
Rows → What do I want to group by?
Columns → What do I want to compare across?
Values → What calculation do I want?
Filters → What do I want to filter?
8️⃣ Change the Calculation
Right-click value → Value Field Settings → Choose Sum, Count, Average, Max, Min, etc. e.g., "What is average sales per order?" → Change to Average.
9️⃣ Sum vs Count in PivotTables
Sum of Sales = 100,000, Count = 3, Average = 33,333.33.
Always make sure aggregation matches business question.
🔟 Show Values as % of Total
Right-click Sales values → Show Values As → % of Grand Total → North 50%, South 30%, West 20%.
Useful for contribution analysis.
1️⃣1️⃣ Group Dates in PivotTables
Right-click a date → Group → Years, Quarters, Months, Days.
Makes time-based analysis easier.
1️⃣2️⃣ Analyze Monthly Sales
Order Date → Rows, Sales → Values, Group by Months → Jan 120K, Feb 145K, Mar 170K etc.
1️⃣3️⃣ Analyze Sales by Region and Month
Rows → Region, Columns → Month, Values → Sales → Matrix to identify best/worst region and trends.
1️⃣4️⃣ Sorting PivotTable Results
Sort Largest → Smallest to make best performers stand out.
1️⃣5️⃣ Top 10 Analysis
Use Value Filters → Top 10 to show top 10 customers/products/regions.
1️⃣6️⃣ Slicers
Slicers make PivotTables interactive.
4August 27, 2026 2.1K 9
