In Data Analytics FULL Course 2026 – Part 05, you learn one of the most powerful and time-saving tools used by every data analyst — Pivot Tables.
Until now, you have worked with advanced Excel formulas such as SUMIFS, COUNTIFS, and AVERAGEIFS. While these formulas are extremely important, Pivot Tables allow you to replace complex formulas with a single dynamic table that can answer multiple business questions instantly.
What Is a Pivot Table in Excel?
A Pivot Table is a dynamic summary table created from raw data. Unlike a normal Excel table:
- Normal tables are static
- Pivot Tables are interactive and dynamic
With Pivot Tables, you can:
- Drag and drop fields
- Change calculations instantly
- Add filters, rows, and columns
- Analyze large datasets without writing formulas
This makes Pivot Tables one of the most important tools in data analytics.
Why Pivot Tables Are Important for Data Analysts
Imagine you have raw data with columns like:
- Date
- Region
- Product
- Units Sold
- Sales Amount
Now answer these questions:
- Which region has the highest sales?
- What is the top-selling product?
- How did sales change month-wise?
- What is the average sales per region?
You cannot answer these easily by just looking at raw data.
This is exactly where Pivot Tables are used.
Difference Between Normal Table and Pivot Table
| Normal Table | Pivot Table |
|---|---|
| Static data | Dynamic & interactive |
| Manual formulas needed | Automatic calculations |
| Hard to analyze | Easy drag-and-drop analysis |
| No quick filtering | Built-in filters |
Pivot Tables help you extract insights from raw data efficiently.
How to Create a Pivot Table in Excel
Step 1: Select Your Data
Select the entire dataset (all columns and rows).
Step 2: Insert Pivot Table
Go to:
Insert → Pivot Table
Step 3: Choose Location
You’ll get two options:
- New Worksheet
- Existing Worksheet
Select based on where you want your Pivot Table.
Pivot Table Layout Explained
A Pivot Table has four main areas:
1. Rows
- Used to display data vertically
- Example: Region, Product
2. Columns
- Used to display data horizontally
- Example: Date, Month
3. Values
- Used for numeric calculations
- Example: Sales, Units Sold
4. Filters
- Used to focus on specific data
- Example: Select a specific month or region
These four components allow you to build any report dynamically.
Practical Pivot Table Examples
Example 1: Region-wise Total Sales
- Drag Region → Rows
- Drag Sales Amount → Values
Result:
You instantly get total sales for each region.
Example 2: Average Sales per Region
- Click on Sales Amount (Values)
- Go to Value Field Settings
- Change from SUM to AVERAGE
Now the Pivot Table shows average sales per region.
Example 3: Product-wise Units Sold
- Drag Product → Rows
- Drag Units Sold → Values
This tells you which product sold the most units.
Example 4: Date-wise Product Sales
- Drag Product → Rows
- Drag Date → Columns
- Drag Sales Amount → Values
This gives a time-based sales analysis, which is very useful for trend analysis.
Using Filters in Pivot Tables
Filters help you focus on specific data.
Example:
- Add Date to Filters
- Select January 2024
Now the Pivot Table updates automatically to show only January data.
This replaces multiple SUMIFS or AVERAGEIFS formulas.
Adding Multiple Values in Pivot Tables
You can add:
- Sales Amount
- Units Sold
- Average Sales
All in the same Pivot Table.
This allows you to see:
- What was sold
- How much was sold
- How many units were sold
All in one view.
Formatting & Customizing Pivot Tables
Pivot Tables can be formatted easily:
- Change table styles and colors
- Enable banded rows
- Show or hide row/column headers
- Sort values (ascending/descending)
- Refresh Pivot Table when data updates
This helps in reporting and dashboard creation.
Pivot Tables in Google Sheets
Pivot Tables work almost the same in Google Sheets:
- Select data
- Click Insert → Pivot Table
- Choose new or existing sheet
- Use Rows, Columns, Values, Filters
Additional features in Google Sheets:
- Percentage of row total
- Percentage of column total
- Percentage of grand total
Why Data Analysts Prefer Pivot Tables
Pivot Tables:
- Replace complex Excel formulas
- Save huge amounts of time
- Handle large datasets easily
- Support business decision-making
- Act as a bridge between Excel & SQL concepts
They are used in MIS reports, dashboards, and management presentations.
What’s Coming Next?
In the next lecture, you’ll learn:
- Formatting & reporting
- Charts and visualizations
- Converting Pivot Tables into dashboards
- Business-ready Excel reports