If you’ve ever worked with large amounts of data in Microsoft Excel, you know how overwhelming it can be. Rows and columns can quickly become difficult to analyze, especially when you’re trying to find patterns, totals, or trends. This is where Pivot Tables and Pivot Charts come in.
A Pivot Table allows you to summarize, organize, and analyze data in just a few clicks—without using complicated formulas. Meanwhile, a Pivot Chart helps you visualize that data so you can easily understand insights at a glance.
In this complete guide, you’ll learn step-by-step how to create and use Pivot Tables and Pivot Charts in Excel, even if you’re a beginner.
What is a Pivot Table in Excel?
A Pivot Table is a powerful tool in Excel that lets you quickly summarize large datasets. Instead of manually calculating totals or averages, Excel does the work for you.
With a Pivot Table, you can:
- Calculate totals, averages, and counts
- Group data by categories (like dates, products, or regions)
- Filter and sort data easily
- Create reports without writing formulas
For example, if you have sales data, you can instantly see total sales by month, product, or location.
What is a Pivot Chart?
A Pivot Chart is a visual representation of your Pivot Table data. It automatically updates whenever your Pivot Table changes.
Instead of looking at numbers, you can use charts like:
- Column charts
- Line charts
- Pie charts
This makes it easier to present your data in reports or presentations.
Preparing Your Data
Before creating a Pivot Table, make sure your data is properly organized:
- Each column should have a header (e.g., Date, Product, Sales)
- No empty rows or columns
- Data should be in a table format
Example:
| Date | Product | Region | Sales |
| Jan 1 | A | North | 100 |
| Jan 2 | B | South | 150 |
Clean data ensures your Pivot Table works correctly.
Step-by-Step: How to Create a Pivot Table
Step 1: Select Your Data
Click anywhere inside your dataset.
Step 2: Insert Pivot Table
Go to the Insert tab in Excel, then click PivotTable.
Step 3: Choose Data Source
A dialog box will appear. Excel usually auto-selects your data range. Choose:
- New Worksheet (recommended), or
- Existing Worksheet
Click OK.
Step 4: Build Your Pivot Table
You’ll now see the Pivot Table Fields panel.
You can drag fields into four areas:
- Rows – categories (e.g., Product)
- Columns – additional grouping (e.g., Region)
- Values – numbers to calculate (e.g., Sales)
- Filters – to filter data (e.g., Date)
Example:
- Drag Product → Rows
- Drag Region → Columns
- Drag Sales → Values
Excel will instantly calculate total sales by product and region.
Customizing Your Pivot Table
Change Value Calculation
By default, Excel uses SUM. You can change it:
- Click the dropdown in Values
- Select Value Field Settings
- Choose options like:
- Sum
- Count
- Average
Sort Data
Click the dropdown next to Row Labels and choose:
- Sort A to Z
- Sort Largest to Smallest
Filter Data
Use the Filters section to display only specific data (e.g., only January sales).
Grouping Data in Pivot Tables
Grouping helps organize data into categories.
Example: Group Dates by Month
- Right-click any date in the Pivot Table
- Click Group
- Select Months (and Years if needed)
Example: Group Numbers
You can group numbers into ranges (e.g., sales from 0–100, 101–500).
How to Create a Pivot Chart
Once your Pivot Table is ready, creating a chart is easy.
Step 1: Select Your Pivot Table
Click anywhere inside the Pivot Table.
Step 2: Insert Chart
Go to Insert tab → Choose a chart type.
Step 3: Choose Chart Style
Pick a chart that fits your data:
- Column chart for comparisons
- Line chart for trends
- Pie chart for proportions
Excel will automatically create a Pivot Chart linked to your Pivot Table.
Customizing Your Pivot Chart
You can make your chart more attractive and easier to understand:
- Change chart title
- Add data labels
- Modify colors and styles
- Filter data directly from the chart
Best of all, when you update your Pivot Table, the chart updates automatically.
Refreshing Pivot Table Data
If your original data changes, your Pivot Table won’t update automatically.
To refresh:
- Click anywhere in the Pivot Table
- Go to PivotTable Analyze tab
- Click Refresh
Your Pivot Chart will also update instantly.
Common Mistakes to Avoid
Here are a few things beginners often overlook:
- Missing column headers
- Including blank rows in data
- Not refreshing after updating data
- Using inconsistent data formats
Fixing these issues ensures your Pivot Tables work smoothly.
Practical Example: Sales Report
Imagine you run a small business and want to analyze sales.
With a Pivot Table, you can:
- See total sales per product
- Compare sales across regions
- Track monthly performance
With a Pivot Chart, you can present this data visually to your team or clients.
Why Pivot Tables and Charts Are Important
Learning Pivot Tables and Pivot Charts in Microsoft Excel is a must-have skill, especially for students, office workers, and business owners.
They help you:
- Save time
- Avoid complex formulas
- Make better decisions using data
- Create professional reports
Final Thoughts
Pivot Tables and Pivot Charts are among the most powerful features in Excel, yet they’re surprisingly easy to use once you understand the basics. Instead of spending hours analyzing data manually, you can generate insights in seconds.
Start practicing with your own data, experiment with different layouts, and soon you’ll be able to create dynamic reports like a pro.
Read Also: Creating and Using Tables in Excel: A Complete Beginner’s Guide