How to Make Group by Same Interval in Excel Pivot Table (2 Methods)

We can group numbers in Excel Pivot Tables to create frequency distribution tables by the same interval. It may help in many cases for analyzing numerical values while working in Excel. For example, we can make a group for how many students scored marks between 50 and 60 and how many scored between 60 and 70, and so on. From this article, you will learn two quick methods to make a group in Excel Pivot Table by the same Interval with sharp steps and proper illustrations.

How to Make Group by Same Interval in Excel Pivot Table: 2 Quick Ways

Let’s get introduced to our dataset first. I have placed a salespersons’ sales for different months and corresponding times.

1. Make Group by Time Intervals in Excel Pivot Tables

At first, we’ll discuss how to group by time in Excel using Pivot Table. We can do this in 2 ways.

1.1 Group by Hour

Firstly, we’ll make group by time on an hourly basis in Pivot Table.

Steps:

Select any data from your dataset.

Then click as follows-

Insert > Pivot Table.

Later, a Pivot Table dialog box will open up.

It will select the data range automatically. You can choose the data range manually too.

Then select the worksheet- New Worksheet/ Existing Worksheet.

Later, press OK.

Now you will get the Pivot Table fields in your selected worksheet.

Drag Time option to Rows field and Sales option to Values field.

Then you will see the table like the image below.

Select any cell containing time.

Right-click your mouse and select Group from the context menu.

Now it will select the Start time and End time automatically from the table.

From the Grouping dialog box select the Hours option.

Press OK.

Now we have got the sales grouped by hourly.

We can change the format of the Sum of values.

Click the dropdown arrow in the Sum of values option in the Pivot Table Fields section.

Then click Number Format from the appeared dialog box.

A dialog box named Format Cells will open up.

Click Currency from the Category option.

Then select the first option from the Negative numbers option.

Click OK and it will go back to the previous dialog box.

Finally, just press OK here.

Now the selected format is applied successfully.

Read More: How to Group Rows in Excel Pivot Table

1.2 Group by Custom Time Intervals

Now you can also group the time by your desired custom time interval. For that, we’ll apply the FLOOR function here. The Excel FLOOR function is used to round a given number down to the nearest specified multiple. We’ll use it to arrange the time for a 15-minute interval which means it will round our times to the nearest multiple of 15 minutes. To apply the FLOOR function we have added a new column named Floor.

Steps:

Type the following formula in Cell F5–

Then press the Enter button.

After that drag down the Fill Handle icon to copy the formula.

Now we have all the rounded times.

Now let’s go forward to our main task.

Select any data of your dataset and click as follows-

Insert > Pivot Table.

Soon after, you will get a Pivot Table dialog box.

Select your desired worksheet and press OK.

Then after appearing the PivotTable Fields drag Floor to Rows field and Sales to Values field.

Then you will get the sales for customized time intervals.

Here also you can change the format like the previous method.

Read more: Pivot Table Custom Grouping

2. Create Group by Ranges in an Excel Pivot Table

In this method, we’ll learn how to make groups for any desired range of values in Excel Pivot Table. It’s pretty much the same as the previous methods. Here, we’ll use the initial dataset and make a group for the sales with a selected range.

Steps:

Create Pivot Table by selecting any cell and clicking as follows-

Insert > Pivot Table.

Choose the New worksheet and click OK at this moment.

Then drag Month to Rows field, Sales to Columns field, and Time to Values Field.

Your Pivot Table will look like this then-

Now click on any sales data.

Right-click your mouse and select Group from the context menu.

Or you can click as follows-

PivotTable Analyze > Group Selection.

Now from the Grouping dialog box choose Starting and Ending value and input interval in the By box.

Then just, click OK.

Now we are done with grouping.

Read more: How to Group Columns in Excel Pivot Table

How to Ungroup in Pivot table?

After making a group in Pivot Table, it’s quite possible to have a question in mind- how to ungroup? No worries! It’s really easy. I’ll show it by ungrouping the previously grouped table.

Steps:

Select any grouped data.

Then right-click your mouse.

Click Ungroup from the context menu.

Or you can use the option from the ribbon.

Click as follows-

PivotTable Analyze > Ungroup.

The Pivot Table is now ungrouped successfully.

Error and Solution

In Excel Pivot Table, we may get the ‘Cannot group selection’ error while creating groups by the same interval.

It may happen if we have cells that contain text instead of numbers in our worksheet. In these cases, we need to go back to the worksheet and remove the text with the appropriate numerical value. For many reasons, numbers are stored as text in a worksheet. In such a case, we need to convert this text to numbers before grouping it in the Pivot Table.

Things to Remember

If you create a group for any Pivot Table then if you create another new Pivot table, up will get the Group option activated each time. So you will have to ungroup if you need them then.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to make a group in Excel Pivot Table by same Interval. Feel free to ask any questions in the comment section and give me feedback.

Further Readings

• How to Group Numbers in Excel Pivot Table
• How to Use Excel Pivot Table to Group by Different Intervals
• How to Rename a Default Group Name in Pivot Table

<< Go Back to Group Pivot Table | Pivot Table in Excel | Learn Excel