Tutorials›Excel›Pivot Tables
🟢 Free Demo
Excel TutorialTopic 9 of 31

Pivot Tables

Summarise, group, and analyse thousands of rows of data in seconds using Excel Pivot Tables.

✅ What You Will Learn

›How to create a Pivot Table from raw data
›How to use Rows, Columns, Values, and Filters areas
›How to group dates by month and quarter
›How to change the summary type (Sum, Count, Average, %)
›How to refresh a Pivot Table when source data changes

A Pivot Table is the single most powerful feature in Excel for data analysis. It lets you instantly summarise thousands of rows by any combination of categories — without writing a single formula. You drag fields into areas and Excel builds the summary automatically.

The four areas of a Pivot Table are: Rows (categories on the left), Columns (categories across the top), Values (the numbers being summarised), and Filters (dropdowns to include or exclude data). By rearranging fields between these areas, you can explore data from any angle in seconds.

Pivot Tables work on a snapshot of your data. When the source data changes, right-click the Pivot Table and select Refresh — or set it to refresh automatically when the file opens.

📋 Raw sales data — source for Pivot Table examples

DateRegionSalespersonCategoryAmount (₹)Units
Jan-26NorthRahulElectronics450003
Jan-26SouthPriyaAccessories850012
Feb-26NorthAnjaliElectronics620004
Feb-26EastVikasAccessories42006
Mar-26SouthPriyaElectronics380002
Mar-26NorthRahulAccessories68009

Examples

Example 1Create a Pivot Table — region vs category sales breakdown
Steps:
1. Click anywhere in your data range
2. Insert tab → PivotTable → OK (creates new sheet)
3. Drag "Region" to Rows area
4. Drag "Category" to Columns area
5. Drag "Amount (₹)" to Values area (auto-sums)
OUTPUT
Region   | Electronics | Accessories | Grand Total
North    |   107000    |    6800     |   113800
South    |    38000    |    8500     |    46500
East     |      -      |    4200     |     4200
💡

This summary that would take hours to build manually is created in under 30 seconds with a Pivot Table.

Example 2Show values as % of column total
Right-click any value in the Pivot Table
→ "Show Values As"
→ "% of Column Total"
OUTPUT
North Electronics: 73.8% of all Electronics sales
💡

Show Values As lets you switch between Sum, %, Running Total, Rank, and more — without changing the underlying data.

📌 Key Points to Remember

  • ✓Data must have headers in the first row and no blank rows or columns within the range
  • ✓Dates in the Values area can be grouped: right-click a date → Group → choose Month, Quarter, Year
  • ✓Refresh the Pivot Table after adding rows to source data: right-click → Refresh (or Alt+F5)
  • ✓The Values area defaults to Sum for numbers and Count for text — change with "Value Field Settings"
  • ✓Pivot Tables do not update automatically — changes to source data require a manual or scheduled refresh

🏢 Real-World Application

Every MIS report in Indian corporates starts with a Pivot Table. A typical weekly sales report uses a Pivot Table to break down revenue by region, product category, and salesperson from a raw export of 5,000+ order rows. What analysts used to spend hours on — manually filtering and copy-pasting into a summary table — now takes five minutes with a Pivot Table.

⚠️ Common Mistakes to Avoid

WRONGData has blank rows or merged cells — Pivot Table skips them or errors
FIXBefore creating a Pivot Table, press Ctrl+End to find the last used cell. Remove any blank rows, unmerge all cells, and ensure every column has a header.
WRONGDates not grouping properly by month
FIXDates must be stored as real Excel dates (not text). Check by selecting a date cell — if the formula bar shows a date and the cell is right-aligned, it is a real date. If left-aligned, it is text and must be converted first.
WRONGEditing values inside a Pivot Table directly
FIXPivot Tables are read-only summaries — you cannot type in them. Make all edits to the source data, then refresh the Pivot Table.
✏️Test Yourself

In a Pivot Table, if you drag a text field (like "Product Name") to the Values area, what does Excel default to?

❓ Frequently Asked Questions

How do I add new rows of data and have the Pivot Table pick them up?

The safest approach is to format your source data as an Excel Table (Ctrl+T) before creating the Pivot Table. Tables expand automatically, so new rows are included on the next Refresh.

Can a Pivot Table summarise data from multiple sheets?

Not directly from the Pivot Table wizard. Use Power Query to combine the sheets into one table first, then create a single Pivot Table from that combined data.

How do I remove the "Grand Total" row from a Pivot Table?

Click anywhere in the Pivot Table → Design tab → Grand Totals → choose "Off for Rows and Columns" or selectively for rows or columns only.

✏️ Practice Exercise

Download or create a dataset with at least 50 rows containing: Date, Region, Product, Category, Sales Amount, and Units. Build a Pivot Table that shows: (1) Total sales by Region and Category, (2) Monthly trend (group dates by month), (3) Top 5 products by sales using a filter. Then change the values to show % of row total.

← PreviousDate FunctionsNext →Pivot Charts
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.