Lesson 6 of 8 · 8 min · Pivot tables and charts

Grouping dates and numbers, and calculated fields

Covers: Microsoft 365 Excel. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Group dates by month, quarter and year
  • Group numbers into bands and text items into custom groups
  • Add a calculated field such as profit or average price
  • Understand why calculated fields are limited
  • Fix the usual "cannot group" errors

In simple words

A pivot table first lists every single value it finds, such as every date in the year. For a report you want bigger buckets: months instead of days, age bands instead of every age. Grouping lets you tell the pivot how to bundle values. A calculated field lets you add a brand new column made from other columns, such as profit = sales minus cost, without touching the source data.

Words to know

  • Grouping: bundling pivot items into bigger sets, such as dates into months.
  • Calculated field: a new pivot field you define with a formula using other fields.
  • Bin: a numeric range used for grouping, such as 0-100.
  • Item: one value inside a field, such as "Pune" in the City field.

Group dates

Put a date field in Rows. In Microsoft 365, Excel often groups dates automatically into Years, Quarters and Months when you drag the field in. If it does not, right-click any date in the pivot and choose Group, then select Months and Years (select both so January 2024 and January 2025 stay apart; choosing only Months merges all Januaries together).

You can group by seconds, minutes, hours, days, months, quarters and years. For Days, choose "Number of days" 7 to group by week (the groups start at the date you set under "Starting at"). To undo, right-click and choose Ungroup.

Typical report: Year > Month in Rows, Region in Columns, Sum of Amount in Values. Use the plus and minus buttons beside the year to expand or collapse its months.

If Group is greyed out or you see "Cannot group that selection", the date column contains blanks, text or non-dates. Fix the source (every cell must be a real date), refresh, and try again. Months of the fiscal year (April to March) are not offered directly: add a helper column Fiscal year to the source, such as =IF(MONTH([@Date])>=4, YEAR([@Date]), YEAR([@Date])-1).

Group numbers into bands

Put a number field such as Age or Amount in Rows and Count of customers in Values. Right-click a row value, choose Group, then set Starting at, Ending at and By (for example 0, 100 and 10 for bands of ten). Excel labels the groups 0-9, 10-19 and so on. This is a quick histogram: add a pivot chart to see the shape.

For uneven bands such as 0-499, 500-999, 1000+, group in the source instead with a lookup table and XLOOKUP approximate match, which is also easier to explain and keep.

Group text items by hand

Select several items in the Rows area (hold Ctrl and click), right-click and choose Group. For example, group Hyderabad and Warangal as "Telangana" and Chennai and Coimbatore as "Tamil Nadu". Excel makes a new field called City2 (rename it). This is fine for a one-off. For a permanent mapping, add a State column to the source via a lookup table so every future report can use it.

Calculated fields

Use a calculated field to compute from other fields at the total level. Click inside the pivot, then PivotTable Analyze > Fields, Items & Sets > Calculated Field. Give it a name and a formula using field names:

Name:     Profit
Formula:  = Sales - Cost
Name:     Average price
Formula:  = Amount / Quantity

Add the new field to Values like any other. For average price, the pivot first sums Amount and sums Quantity for each cell, then divides, which is the correct weighted average. (Averaging each row's price would be wrong.)

Important limits:

  • A calculated field sums its inputs first and then applies the formula, always. It cannot do row-by-row IF logic or use XLOOKUP.
  • It cannot refer to cells outside the pivot, only to other fields.
  • It is not available when the pivot was built on the Data Model; there you write DAX measures (see the Power BI course).
  • If you need row-level calculations, add a column in the source Table (=[@Sales]-[@Cost]) and pivot that column instead. This is usually the simplest and most flexible choice.

A calculated item works inside one field (for example "Pune + Hyderabad" as a new item), but it can slow the pivot and give surprising totals. Most analysts avoid it and use grouping or a source column instead.

Percent change and running totals without formulas

Many "calculated" needs are met by Show Values As (previous lesson): % Difference From (Previous) for month-over-month growth, Running Total In for cumulative totals, and % of Parent Row Total for shares within a group. Use those before writing a calculated field.

Common mistakes

  • Grouping dates by Months only, merging different years.
  • Blank or text cells in the date column, blocking grouping.
  • Averaging an average: use a calculated field (sum divided by sum) instead of averaging row percentages.
  • Expecting a calculated field to evaluate row by row.
  • Typing hand-made groups that are lost when the data changes; map in the source.

Try it yourself

  1. Group a date field by Years and Months and compare with Months only.
  2. Group customer ages into bands of 10 and chart the counts.
  3. Add a calculated field for Profit and another for Margin % (= (Sales - Cost) / Sales).
  4. Add a Fiscal year helper column to the source and use it in a pivot.
  5. Group three cities into a region by hand, then rebuild the same grouping with a lookup column.

Check yourself

Why select both Months and Years when grouping dates?

Selecting only Months combines the same month from different years into one row; adding Years keeps them apart.

A calculated field = Amount / Quantity gives which kind of average?

A correctly weighted one: the pivot sums Amount and Quantity first, then divides.

Why can't I group a date field?

Some cells in the field are blank, text or not real dates; fix the source and refresh.

When should you add a column to the source instead of a calculated field?

When you need row-by-row logic such as IF or a lookup, since calculated fields work only on summed values.

Interview questions

How do you show sales by fiscal quarter when the year starts in April?

Add a helper column in the source for fiscal year and fiscal quarter with formulas based on the month, then pivot on those columns.

What is the difference between a calculated field and a calculated item?

A calculated field is a new field computed from other fields (on summed values). A calculated item is a new item inside an existing field. Calculated items are easy to misuse; prefer grouping or a source column.

How do you create a histogram with a pivot table?

Put the numeric field in Rows and any field in Values as Count, group the rows into bins with Group, then insert a pivot chart (column).

Summary

  • Right-click a date and Group by Months and Years; group numbers into bands with Starting, Ending and By.
  • Hand-group text items for one-offs; use a lookup column in the source for permanent mappings.
  • Calculated fields compute on summed values only; use source columns for row-level logic.
  • Show Values As covers percent of total, differences and running totals without formulas.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.