Your sales file has hundreds of order rows. Your manager asks for sales by region, orders by category and month-wise closed sales. You can write many formulas, but a Pivot Table can summarize the same data in seconds.

What is a Pivot Table?

A Pivot Table is an Excel tool that quickly summarizes row-based data. It can group, count, total and compare data without writing formulas for every summary.

Simple rule: Use Pivot Tables when you have clean tabular data and need fast summaries.

Pivot Table output example

RegionClosed SalesPending SalesTotal Sales
West₹21,250₹3,000₹24,250
North₹12,000₹10,000₹22,000
Central₹53,850₹12,600₹66,450
Grand Total₹87,100₹25,600₹112,700

Understand Pivot Table areas

RowsMain grouping field, such as Region or Category.
ColumnsSecondary grouping field, such as Status or Month.
ValuesNumeric field to summarize, such as Sales or Quantity.
FiltersReport-level filter, such as Month or Salesperson.
RowsGroup data
ValuesSummarize numbers
RefreshUpdate output

How to create your first Pivot Table

  1. Click anywhere inside your clean data table.
  2. Go to Insert > PivotTable.
  3. Choose where to place the Pivot Table.
  4. Drag Region to Rows.
  5. Drag Sales to Values.
  6. Drag Status to Columns if you want a cross-tab summary.
  7. Refresh the Pivot Table when source data changes.

Pivot workflow

1Clean Data
2Insert Pivot
3Drag Fields
4Refresh
💡
Professional habit: Convert your source range into an Excel Table before creating a Pivot Table. This helps when new rows are added later.
⚠️
Common mistake: Values showing Count instead of Sum. Open Value Field Settings and change the calculation if needed.

Interview question

Question: What are the prerequisites for creating a good Pivot Table?

Answer: The source data should have one clean header row, no blank rows inside the data, consistent data types, and proper number/date formats. Converting the source into an Excel Table is also recommended.

🧪 Practice Lab

Create sales summaries using Pivot Tables

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, cheat sheet, quiz and answer key.

📦 Download Lesson 23 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Create a Pivot Table from Sales Data.
  2. Summarize Total Sales by Region.
  3. Add Status in Columns.
  4. Add Month as a Filter.
  5. Change Values to Count and Average to compare outputs.
  6. Refresh the Pivot Table after changing the source data.

Quick quiz

  1. What is a Pivot Table used for?
  2. What goes into Values?
  3. Why should you refresh a Pivot Table?
  4. Why convert source data into a Table first?

Answers: Summarizing data; numeric fields like Sales; to update output after data changes; safer expanding source range.

FAQ

No. Pivot Tables summarize source data but do not change the original rows.
Excel may treat the field as text or detect blanks. Check source data and change Value Field Settings to Sum.