Skip to content
Tutorials Center
My learning

Topics

Lesson 62 of 93 ยท PivotTables and Data Analysis

Grouping, Calculated Fields and Show Values As

Group dates and numbers in a PivotTable, show percent of total, running totals and differences, and add calculated fields and items, with pandas stand-ins.

A basic PivotTable adds up what is in your data. Real questions go further: sales by month instead of by day, age bands instead of single ages, each region's share of the total, the running total through the year, the change from last month. Excel answers these with three features: grouping, Show Values As and calculated fields.

Excel cannot run on this site, so the menu steps are text. The TryIts use pandas (resample, cut, cumsum, pct_change) as stand-ins for the same calculations. They show what the numbers mean; they are not the PivotTable engine. If you have not built a pivot yet, start with PivotTable Basics.

Three ways to go deeper

Group Days into months, ages into bands.
Show Values As Percent of total, running total.
Calculated field A new field from a formula.
1 / 2

Grouping dates

Dates in a pivot usually come in as single days, which is too fine to read. Put the Date field in Rows. Modern Excel often groups it automatically into Months (and Years) when you add it. If not, right-click any date in the pivot and choose Group. Tick Months and Years (or Quarters, Days, Hours...) and press OK. The pane then shows Years and Months as separate fields you can place anywhere.

Keep Years ticked along with Months. If you group only by Months, January 2024 and January 2025 merge into one "Jan" row. To undo, right-click > Ungroup.

Grouping fails with the message "Cannot group that selection" when the date column contains text, blank cells or true errors. Fix the source and refresh. Grouping by days with a Number of days such as 7 gives weekly buckets starting on the first date you choose.

Run it: dates into months

resample("MS") buckets dates by month start. It is the same idea as grouping by Months and Years.

Python Group dates by month (and by month across years)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Grouping numbers and text

Numbers group too. Put Age (or Price) in Rows, right-click > Group, and fill Starting at, Ending at and By (for example 0, 60, 10). You get bands such as 0-9, 10-19 and so on. Counting how many people fall in each band is a quick histogram when you put the same field in Values as a Count.

Text items group by selection: select several row labels (Ctrl+click), right-click > Group. Excel adds a field named Group1 and you can rename the group in the cell. Use this for custom regions, such as grouping three countries into "Nordics".

Python Group numbers into bands, then count

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Show Values As

Right-click a number in Values > Show Values As, or open Value Field Settings > Show Values As. The numbers are recalculated for display; the source stays the same. Useful options:

  • % of Grand Total: each cell divided by the total of the whole pivot.
  • % of Column Total / % of Row Total: shares within each column or row.
  • % of Parent Row Total: shares inside a group, such as a product's share of its category.
  • Running Total In: cumulative sum down a base field (such as Month).
  • Difference From / % Difference From: change against the previous (or a chosen) item.
  • Rank Smallest to Largest / Largest to Smallest.
  • Index: a relative importance score.

Drag the same value field in twice to show the amount and its percent in adjacent columns. Rename the second ("% of total"), because both would otherwise have similar names.

Run it: percent of total, running total and difference

Python % of total, running total and difference from previous

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For "Difference From", you choose a Base field (Month) and a Base item: (previous), (next) or a specific item. The first month has nothing to compare with, so it shows blank, just as pandas shows NaN.

Calculated fields

A calculated field is a formula that works on other fields of the pivot. Select the pivot, then PivotTable Analyze > Fields, Items & Sets > Calculated Field. Give it a name and a formula, for example:

TEXT
Name:     Profit
Formula:  = Revenue - Cost

Name:     Bonus
Formula:  = Revenue * 0.05

Click fields from the list rather than typing them. The new field appears in the pane and can go into Values.

Important limits, because they cause wrong totals:

  • A calculated field calculates after summing each field. = Price * Qty multiplies the sum of Price by the sum of Qty, which is almost never what you want. Add a column Amount = Price*Qty to the source data instead.
  • It cannot use cell references, worksheet functions that need ranges (such as VLOOKUP) or named ranges.
  • Ratios like = Profit / Revenue do work correctly because the sums are divided at each level of the report.

A calculated item adds a new item inside an existing field, such as a "North + South" row. They are fragile (they can make pivots slow and disable some features), and most experts prefer grouping or helper columns in the source.

Run it: why summing first matters

Python A calculated field works on sums, not rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You group the Date field by Months only. What can go wrong?

    Without Years ticked, January 2024 and January 2025 both become "Jan". Tick Months and Years together.
  2. 2. Which Show Values As option adds amounts down the rows cumulatively?

    Running Total In accumulates the values along a base field such as Month.
  3. 3. A calculated field Revenue = Price * Qty gives strange totals. Why?

    Calculated fields work on summed values, not row by row. Create the Amount column in the source data instead.
  4. 4. "Cannot group that selection" usually means that the date column...

    Grouping needs every item to be a date (or number). Clean the source column and refresh.

Summary

  • Right-click > Group turns dates into months, quarters and years, numbers into bands and selected text items into custom groups.
  • Tick Years along with Months so different years are not merged.
  • Show Values As displays percent of total, running totals, differences and ranks without changing the data.
  • Calculated fields compute from summed fields, so add row-level formulas such as Price*Qty to the source data.
  • Prefer grouping and helper columns to calculated items.

Next: Slicers, Timelines and PivotCharts.