🚀 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.