Excel Pivot Tables -- Divide Numerical Column Data into Ranges

Question

I have an Excel spreadsheet with a column called "Time Elapsed" that stores the number of days it took to complete a task.

I would like to make a pivot table out of this spreadsheet where I divide the "Time Elapsed" column into ranges, e.g.,

how many tasks took 0 to 4 days to complete
how many tasks took 5 to 9 days
how many took 10 to 14 days
how many took 15+ days

Do I have to create new columns in my spreadsheet dedicated to each interval (0 to 4, 5 to 9, etc.) or can I use some feature of pivot tables to separate my one "Time Elapsed" column into intervals?

Answer 1

You can use the "Group" feature after you've built your pivot table. Assuming your "time elapsed" is in your pivot table row labels, select the values 0, 1, 2, 3, 4. Right-click, and you'll see the "Group" feature. (It is also available as "Group selection" under the "Options" tab up on the ribbon.)

And, once you've "Grouped" the data, you can even edit the default "Group1" group name in the formula bar to something useful like "0 to 4 days".

Repeat as needed for your other groups.

You'll notice that Excel added another row label called "time elapsed2" as soon as you created your first group. All the usual "Field Settings" options are available. And double-clicking each of your new groups will collapse or expand the detail, as desired.