PivotTable Filters and Grouping

You can filter a PivotTable to show only some items (for example one region), and group items into bigger buckets: dates into months, quarters or years, numbers into ranges such as 0-9, 10-19.

Grouping needs a helper value. For dates, MONTH(date) extracts the month. For numbers, a bucket is INT(value/10)*10, which turns 14 into 10 and 27 into 20. Then summarise by that helper, exactly as a PivotTable does after grouping.

Exampleexcel

Open in editor »

Quick check

What does grouping dates by month do in a PivotTable?

Combines all dates in the same month into one rowDeletes old datesSorts months alphabetically

Your turn: write the code

Make a helper in B2:B5 that puts each age in a bucket of ten: =INT(A2/10)*10.

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.