Grouping Dates and Numbers in Pivot Tables0%

Grouping Dates and Numbers in Pivot Tables

Beginner12 min read•Updated: Jul 11, 2026

Grouping Dates and Numbers in Pivot Tables

Raw transactional data typically contains granular timestamps (e.g., '2026-03-14 09:22:15') or continuous numerical values (e.g., ages from 18 to 75). Trying to analyze this without aggregation results in thousands of unreadable rows. Grouping allows you to roll dates up into Years, Quarters, and Months, or bin numerical scores into structured frequency brackets.

1. Grouping Date Fields (Years, Quarters, Months)

When you drag a date field into the Rows area of a modern Pivot Table, Excel often auto-groups it. If not, or if you wish to adjust the intervals:

1
Right-click any date cell in the Pivot Table.
2
Select Group... from the contextual menu.
3
In the Grouping dialog box:

Starting at / Ending at: Set boundary dates.
By: Highlight one or more time horizons:
Seconds / Minutes / Hours (For call centers & web logs)
Days (Set Number of days: 7 for weekly tracking!)
Months / Quarters / Years (For fiscal reporting)

1
Click OK.

1Nested Date Hierarchy in Pivot Rows:
2- 2026
├── Qtr 1
│   ├── Jan: $120,000
│   ├── Feb: $145,000
│   └── Mar: $160,000
└── Qtr 2 ...

2. Grouping Numeric Ranges into Custom Bins

Beyond dates, grouping works on numeric values such as Age, Salary, or Order Value:

Example: Customer Demographics by Age Bracket

Suppose you have customer ages from 18 to 82:

1
Drag Age into the Rows area.
2
Drag Customer ID into the Values area (Count of Customer ID).
3
Right-click any age number in the table and choose Group....
4
In the Grouping dialog:

Starting at: '18'
Ending at: '80'
By: '10' (Creates 10-year brackets)

1
Click OK.

Resulting Brackets:

'<18'
'18-27'
'28-37'
'38-47'
'48-57'
'58-67'
'>=68'

3. Ungrouping Data

If you need to return to raw transaction dates or individual numbers:

Right-click any grouped cell in the Pivot Table.
Select Ungroup.
Excel instantly restores the granular line-item records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How do you open the Grouping dialog box for a date or numeric field in a Pivot Table?

Question 2

How can you group daily sales transactions into clean weekly (7-day) intervals in a Pivot Table?

Question 3

When grouping numbers (such as customer age or income), what do the 'Starting at', 'Ending at', and 'By' fields define?

Question 4

What happens if a date column contains even a single blank cell or text entry when you attempt to group by Months/Years?

Question 5

How do you revert a grouped Pivot Table field back to its raw, unaggregated state?

Next Lesson

Calculated Fields and Calculated Items

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE)	Calculated Fields and Calculated Items

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Calculated Fields and Calculated Items

Grouping Dates and Numbers in Pivot Tables

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Grouping Dates and Numbers in Pivot Tables

1. Grouping Date Fields (Years, Quarters, Months)

When you drag a date field into the Rows area of a modern Pivot Table, Excel often auto-groups it. If not, or if you wish to adjust the intervals:

1
Right-click any date cell in the Pivot Table.
2
Select Group... from the contextual menu.
3
In the Grouping dialog box:

Starting at / Ending at: Set boundary dates.
By: Highlight one or more time horizons:
Seconds / Minutes / Hours (For call centers & web logs)
Days (Set Number of days: 7 for weekly tracking!)
Months / Quarters / Years (For fiscal reporting)

1
Click OK.

1Nested Date Hierarchy in Pivot Rows:
2- 2026
├── Qtr 1
│   ├── Jan: $120,000
│   ├── Feb: $145,000
│   └── Mar: $160,000
└── Qtr 2 ...

2. Grouping Numeric Ranges into Custom Bins

Beyond dates, grouping works on numeric values such as Age, Salary, or Order Value:

Example: Customer Demographics by Age Bracket

Suppose you have customer ages from 18 to 82:

1
Drag Age into the Rows area.
2
Drag Customer ID into the Values area (Count of Customer ID).
3
Right-click any age number in the table and choose Group....
4
In the Grouping dialog:

Starting at: '18'
Ending at: '80'
By: '10' (Creates 10-year brackets)

1
Click OK.

Resulting Brackets:

'<18'
'18-27'
'28-37'
'38-47'
'48-57'
'58-67'
'>=68'

3. Ungrouping Data

If you need to return to raw transaction dates or individual numbers:

Right-click any grouped cell in the Pivot Table.
Select Ungroup.
Excel instantly restores the granular line-item records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How do you open the Grouping dialog box for a date or numeric field in a Pivot Table?

Question 2

How can you group daily sales transactions into clean weekly (7-day) intervals in a Pivot Table?

Question 3

When grouping numbers (such as customer age or income), what do the 'Starting at', 'Ending at', and 'By' fields define?

Question 4

What happens if a date column contains even a single blank cell or text entry when you attempt to group by Months/Years?

Question 5

How do you revert a grouped Pivot Table field back to its raw, unaggregated state?

Next Lesson

Calculated Fields and Calculated Items

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Array Formulas & Dynamic Arrays (FILTER, SORT, UNIQUE)	Calculated Fields and Calculated Items

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next