Pivot Table in Excel for Beginners: Step by Step Tutorial

A pivot table in Excel for beginners is simply a summary you build by dragging fields: click inside your data, go to Insert, PivotTable, then drag a category (such as Branch) into Rows and an amount (such as Sales) into Values. Excel groups and totals the data for you without changing the source, and you can rearrange the report in seconds. The two rules beginners forget: keep the source as a clean list with one header row, and press Refresh after the data changes.

Before you start: prepare the data

A pivot table is only as good as the list behind it. Check five things:

1. One header row, with a different name for every column and no blank headers.
2. No blank rows, subtotal rows or merged cells inside the data.
3. Amounts are real numbers. If the pivot shows “Count of Amount” instead of “Sum of Amount”, some amounts are probably text or blank.
4. Dates are real dates, not text.
5. Ideally, the data is an Excel table: click inside it, press Ctrl+T and name it, say, Sales.

Our example: a distributor’s Sales table with columns Date, Branch (Lucknow, Kanpur, Varanasi), Salesperson, Category and Amount, about 1,200 rows from April to October 2026.

Pivot table in Excel for beginners: step by step

1. Click any cell inside the Sales table.
2. Go to Insert, PivotTable. Confirm the table as source, choose New Worksheet and click OK.
3. The PivotTable Fields pane opens on the right with Filters, Columns, Rows and Values.
4. Drag Branch to Rows. Each branch appears once.
5. Drag Amount to Values. You now see “Sum of Amount” for each branch and a grand total.
6. Drag Date to Columns. Recent versions group dates into years, quarters and months automatically. If yours does not, right-click a date in the pivot, choose Group, select Months and Years, and click OK.
7. Right-click an amount, choose Number Format, and pick Currency with the ₹ English (India) symbol and 0 decimals for lakh-style grouping.

You have a branch-by-month sales report. Drag Category under Branch in Rows for more detail, or swap Rows and Columns for a different view. Nothing in the source changes.

Pivot table examples for Indian offices

• Salary sheet: Department in Rows; Sum of Gross, Sum of Net and Count of Emp ID in Values. Compare with last month before releasing salaries.
• Attendance log (one row per employee per day): Employee in Rows, Status in Columns, Count of Status in Values. Present, absent and leave days per person in one view.
• Fee receipts: Class in Rows, Month in Columns, Sum of Amount in Values.
• GST invoice register: Month in Rows, GST Rate in Columns, and Taxable Value, CGST, SGST and IGST in Values, a quick monthly summary for your accountant.

Useful pivot settings beginners should know

Change the calculation. Click the arrow next to “Sum of Amount” in Values and choose Value Field Settings. Summarize Values By offers Sum, Count, Average, Max and Min. Show Values As offers % of Grand Total, % of Column Total, Difference From (month-on-month change) and Running Total. Drag Amount into Values twice, set one to Sum and the other to % of Grand Total, and you see each branch’s amount and share side by side.

Filters and slicers. Drag Salesperson to Filters to view one person at a time. For button-style filters, click inside the pivot and choose PivotTable Analyze, Insert Slicer, and tick Category. A Timeline (PivotTable Analyze, Insert Timeline) does the same for dates.

Indian financial-year quarters. Excel’s quarters treat January to March as Q1. For April to June as Q1, add a column to the source table: ="Q"&ROUNDUP((MOD(MONTH([@Date])-4,12)+1)/3,0). April gives Q1, October Q3, and January to March Q4. Refresh and use this field instead of Date.

Drill down. Double-click any value and Excel lists the exact rows behind it on a new sheet. It is the fastest way to answer “why is Kanpur’s September figure so high?” Delete that sheet afterwards.

Use a pivot figure in a formula. Type = and click a pivot cell. Excel writes a GETPIVOTDATA formula that keeps pointing to the right figure even if the layout changes.

Common beginner mistakes

• Forgetting to refresh. Pivots do not update themselves. With a plain range as source, new rows below it are left out even after refresh; use PivotTable Analyze, Change Data Source, or better, use a table.
• Text amounts. “5000/-” is text and will not sum. Clean the data first.
• Typing over pivot cells. Edit the source, then refresh.
• Merged headings and subtotal rows inside the source.

Frequently asked questions

How do I create a pivot table in Excel for beginners?

Click inside your data, go to Insert, PivotTable, choose New Worksheet, then drag a category field to Rows and a number field to Values. Excel groups and totals automatically, and you can drag more fields to Columns or Filters.

Why is my pivot table not updating?

Pivot tables do not update automatically. Right-click and choose Refresh, or use Data, Refresh All. If new rows still do not appear, your source is a fixed range; change the data source or convert the data to an Excel table.

Why does my pivot show Count instead of Sum?

Usually because some cells in the amount column are text or blank. Convert the amounts to real numbers, refresh, and change the field to Sum in Value Field Settings.

Can I make a pivot table in Google Sheets?

Yes. Google Sheets has pivot tables under Insert, Pivot table, with Rows, Columns, Values and Filters. The menus differ slightly from Excel, but the same clean-data rules apply.

Quick quiz

Sources

• Microsoft Support: Create a PivotTable to analyze worksheet data; Group or ungroup data in a PivotTable; Use slicers to filter data
• Microsoft Support: GETPIVOTDATA function
• Google Docs Editors Help: Create and use pivot tables

Turn registers into reports with the Excel and AI for Office Work certificate course.

This page is for education and general information, not legal advice for a specific case. Law as on 29 October 2026.