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

We can group numbers in Excel Pivot Tables for creating frequency distribution tables by 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 same Interval with sharp steps and proper illustrations.


Download Practice Workbook

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


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

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.

Group by Time in Excel Pivot Tables

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.

Group by Time in Excel Pivot Tables

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

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

Group by Time in Excel Pivot Tables

Then you will see the table like the image below.

Group by Time in Excel Pivot Tables

Select any cell containing time.

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

Group by Time in Excel Pivot Tables

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.

Group by Time in Excel Pivot Tables

Now we have got the sales grouped by hourly.

Group by Time in Excel Pivot Tables

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.

Group by Time in Excel Pivot Tables

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.


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

=FLOOR(E5,"00:15")

Then press the Enter button.

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

Now we got all the rounded times.

Make Group by Time in Excel Pivot Tables

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.

Make Group by Time in Excel Pivot Tables

Select your desired worksheet and press OK.

Make Group by Time in Excel Pivot Tables

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


Similar Readings


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 the same as the previous methods. Here, we’ll use the initial dataset and will 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.

Create Group by Range in an Excel Pivot Table

Choose the worksheet and click OK at this moment.

Create Group by Range in an Excel Pivot Table

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

Create Group by Range in an Excel Pivot Table

Your Pivot Table will look like this then-

Create Group by Range in an Excel Pivot Table

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 Pivot Table by Month in Excel


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 previous grouped table.

Steps:

Select any grouped data.

Then right-click your mouse.

Click Ungroup from the context menu.

Ungroup in Pivot table

Or you can use the option from the ribbon.

Click as follows-

PivotTable Analyze > Ungroup.

Ungroup in Pivot table

The Pivot Table is now ungrouped successfully.


Error and Solution

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 these text to numbers before grouping it in 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.


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo