What is a Pivot Table? #
A Pivot Table is a tool used to summarize, analyze, and explore data quickly.
It turns raw data into meaningful insights (totals, counts, averages).
| Product | Region | Sales |
|---|---|---|
| A | East | 100 |
| B | West | 200 |
| A | West | 300 |
How to Create a Pivot Table #
- Select your dataset
- Go to Insert → PivotTable
- Choose location (New Sheet recommended)
- Click OK
Pivot Table Fields #
You’ll see 4 areas:
| Area | Purpose |
|---|---|
| Rows | Categories (e.g., Product) |
| Columns | Grouping (e.g., Region) |
| Values | Calculations (Sum, Count) |
| Filters | Apply filters |
Example Setup #
- Rows → Product
- Columns → Region
- Values → Sales (Sum)
utput:
| Product | East | West |
|---|---|---|
| A | 100 | 300 |
| B | 0 | 200 |
Key Features #
- Change calculation (Sum → Average)
- Sort & filter
- Group data (dates, categories)
Pivot Charts #
What is a Pivot Chart? #
A Pivot Chart is a visual representation of a Pivot Table.
Automatically updates when Pivot Table changes
How to Create #
- Click Pivot Table
- Go to Insert → PivotChart
- Choose chart type (Bar, Line, Pie)
Example #
- Sales by Region → Bar Chart
- Monthly Sales → Line Chart
Benefits #
- Interactive
- Dynamic updates
- Easy visualization
Slicers #
What are Slicers? #
Slicers are visual filters for Pivot Tables/Charts.
They allow you to filter data with buttons
How to Add Slicer #
- Select Pivot Table
- Go to Insert → Slicer
- Choose field (e.g., Region)
Example #
Click “East” → shows only East data
Benefits #
- User-friendly filtering
- Interactive dashboards
- No need for dropdown filters
Timelines #
What is a Timeline? #
A Timeline is a date-based filter for Pivot Tables.
Used for filtering by:
- Year
- Quarter
- Month
How to Add Timeline #
- Select Pivot Table
- Go to Insert → Timeline
- Select date field
Example #
Filter sales by:
- 2024
- Q1
- January
Benefits #
- Easy time-based analysis
- Visual filtering
- Great for dashboards
Data Validation #
What is Data Validation? #
Data Validation is used to control what users can enter in a cell.
Prevents incorrect data entry
Types of Validation #
| Type | Example |
|---|---|
| List | Dropdown menu |
| Number | Only numbers allowed |
| Date | Only valid dates |
| Custom | Custom rules |
How to Apply #
- Select cells
- Go to Data → Data Validation
- Choose rule
- Click OK
Example: Dropdown List #
- Source:
East, West, North
User selects from dropdown
Example: Number Restriction #
- Allow: Whole Number
- Between: 1 to 100
Benefits #
- Improves data accuracy
- Standardizes input
- Reduces errors
Combined Real-World Scenario #
Sales Dashboard:
- Pivot Table → Sales summary
- Pivot Chart → Visual graph
- Slicer → Filter by Region
- Timeline → Filter by Date
- Data Validation → Controlled inputs
Table #
| Feature | Purpose |
|---|---|
| Pivot Table | Summarize data |
| Pivot Chart | Visualize data |
| Slicer | Interactive filtering |
| Timeline | Date filtering |
| Data Validation | Control input |