Excel Pivot Tables Tutorial: Build, Group, Slice and Refresh

At Narmada Traders, the Monday meeting always starts with the same questions. Which area sold most last week? Which category is growing? Who are the top five retailers? The accountant used to answer each with a fresh set of SUMIFS formulas, and every new question meant another half hour.

Then she dragged "Area" into one box and "Amount" into another, and a summary appeared. She dragged "Category" across the top, and it became a grid.

The invoice list is the source data. The boxes she dragged into are the pivot table field areas. The grid that appeared is the pivot table, and changing the question by moving fields is pivoting.

An Excel pivot table is an interactive summary table that groups, totals, counts or averages the rows of a source table based on fields you place in its Rows, Columns, Values and Filters areas.

This tutorial uses the ten-invoice Sales Table from the formulas and functions tutorial, cleaned with the steps in data cleaning in Excel. Clean data matters more for pivots than anywhere else, because every spelling variant becomes its own row.

How do you create a pivot table in Excel?

1. Click any cell inside the Sales Table.
2. Go to Insert > PivotTable. Excel suggests the Table name as the range.
3. Choose New Worksheet and click OK. An empty pivot and the PivotTable Fields pane appear.
4. Tick or drag Area into Rows.
5. Drag Amount into Values. Excel shows Sum of Amount.

That complete pivot table answers "sales by area" without a formula.

Always build pivots on an Excel Table (Ctrl+T), not a fixed range, so new invoices are picked up on refresh.

What are the four areas of a pivot table?

Drag Category into Columns and the summary becomes a cross-tab:

Each field you drag decides one direction of the summary. Swapping Area and Category flips the grid in a second.

Blank cells mean there were no sales for that pair. To show zeros instead, right-click the pivot, open PivotTable Options, and set For empty cells show to 0.

Changing how values are summarised

Sum, Count, Average and more

Numbers default to Sum. Text fields placed in Values default to Count. To change it, click the field in the Values area, choose Value Field Settings, and pick Count, Average, Max or Min.

Put Amount in Values twice, set the second one to Count, and you get sales and number of invoices side by side. Vijay Nagar shows ₹12,860 from 3 invoices.

Show Values As percentages

In Value Field Settings, the Show Values As tab changes what the number means without changing the data. % of Grand Total shows each area's share:

Other useful options are % of Row Total (each category's share within an area), Difference From (change versus a previous month) and Rank Largest to Smallest.

Sort and filter inside the pivot

Right-click any value and choose Sort > Sort Largest to Smallest to rank areas by sales, as in the table above. For a top-five list, open the Row Labels dropdown and choose Value Filters > Top 10, then change 10 to 5.

How do you group dates in a pivot table?

Put Date in Rows. In current Microsoft 365, Excel often groups dates into Years, Quarters and Months automatically. If it does not, right-click any date and choose Group, then select the levels you want, such as Months and Years.

With the five April days in the sample, grouping by day gives:

Grouping works only when every cell in the Date column is a real date. One text date such as "15.04.2026" makes Group fail. The text and date functions tutorial shows how to convert them.

Calendar quarters start in January. For India's April to March financial year, add a helper column in the source, such as =YEAR([@Date])+(MONTH([@Date])>=4), and put that in Rows instead.

Slicers, timelines and refresh

Slicers and timelines

A slicer is a set of clickable buttons that filters the pivot. Click inside the pivot, go to PivotTable Analyze > Insert Slicer, and tick Area. Managers can then click "Palasia" instead of opening dropdowns. A timeline (Insert Timeline) does the same for dates with a sliding bar.

One slicer can control several pivots built from the same source. Right-click the slicer, choose Report Connections, and tick the pivots it should filter. This is the core of the one-page report in the charts and dashboard tutorial.

Why your pivot does not show new data

A pivot table does not read the source directly. It reads a saved snapshot called the pivot cache. When invoices are added or changed, the pivot keeps showing the old snapshot until you refresh.

New rows reach the pivot only through Refresh. Building on a Table makes sure Refresh includes them.

• Refresh one pivot: right-click it and choose Refresh, or press Alt+F5.
• Refresh every pivot and query: Data > Refresh All, or Ctrl+Alt+F5.
• Refresh on open: PivotTable Options > Data > Refresh data when opening the file.

A VBA macro can also refresh everything as part of a weekly report job.

Calculated fields and distinct counts

A calculated field adds a new measure from existing fields. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field, name it Avg Rate and enter =Amount/Qty. Excel divides the sums, so Staples shows 24100 / 85 = ₹283.53 per unit, a weighted average.

To count distinct retailers rather than invoices, tick Add this data to the Data Model when you create the pivot. Value Field Settings then offers Distinct Count, which is unavailable in a normal pivot.

Pulling a pivot value into a formula

Type = and click a pivot cell, and Excel writes a GETPIVOTDATA formula. It keeps pointing at the right value even when the pivot's layout changes:

=GETPIVOTDATA("Amount", $A$3, "Area", "Rajwada")
→ 11640

Formula-based pivots in Microsoft 365

Current Microsoft 365 builds also include GROUPBY and PIVOTBY, which return pivot-style summaries from a formula and update without refresh. =PIVOTBY(Sales[Area], Sales[Category], Sales[Amount], SUM) returns the same cross-tab as above. They are not in Excel 2016 or 2019, so classic pivot tables remain the version-safe choice.

The same pivot in pandas

pandas.pivot_table mirrors Excel's pivot. index is Rows, columns is Columns, values and aggfunc are Values, and margins=True adds grand totals.

import pandas as pd

sales = pd.DataFrame({
    "Area": ["Vijay Nagar", "Palasia", "Rajwada", "Vijay Nagar", "Bhawarkuan",
             "Palasia", "Sudama Nagar", "Rajwada", "Bhawarkuan", "Vijay Nagar"],
    "Category": ["Staples", "Staples", "Oil", "Beverages", "Staples",
                 "Staples", "Oil", "Beverages", "Staples", "Oil"],
    "Amount": [5040, 4800, 6960, 2600, 4600, 6300, 3480, 4680, 3360, 5220],
})

grid = pd.pivot_table(sales, index="Area", columns="Category", values="Amount",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Grand Total")
print(grid)

share = sales.groupby("Area")["Amount"].sum()            # Show Values As % of Grand Total
print((share / share.sum() * 100).round(1).sort_values(ascending=False))

The pandas group-by and pivot tables tutorial explains each argument. In Power BI, the same idea appears as a matrix visual, covered in dashboard design with Power BI.

Common pivot table mistakes

• Building on a fixed range. New rows below A1:I11 are ignored. Use a Table.
• Forgetting to refresh. The pivot shows old numbers and nobody notices.
• Blank or merged header cells. Excel refuses to create a pivot when a header is empty.
• Dirty categories. "Palasia" and "Palasia " become two rows. Clean first.
• Averaging averages. A calculated field divides sums; averaging per-row rates gives a different, usually wrong, figure.

Debugshala teaches Excel pivot tables in its BI Tools course built around business reporting and its Data Analytics course in Indore, both with a free demo class.

Key takeaways

• A pivot table summarises data by dragging fields into Rows, Columns, Values and Filters.
• Always build on a clean Excel Table so refresh picks up new rows.
• Value Field Settings switch Sum, Count and Average; Show Values As gives percentages and ranks.
• Group real dates into months or quarters; use a helper column for the Indian financial year.
• Slicers make pivots clickable, and Refresh (Alt+F5) is needed after every source change.