How to group data by half an hour or 15 minutes in an Excel pivot table?

In this article, the user will learn how to group data in half an hour or 15 minutes in an Excel pivot table. It allows users to summarize and analyze certain data based on different ranges of values, instead of individual values. This article contains a single example that uses the pivot table option available in the insert table, to group the available hour data of an Excel pivot table. In the initial steps, the table is converted into a pivot table, and further, the obtained table data is grouped according to the user's requirement.

Let’s explore the article with an example.

Example 1: To group data in a pivot table by half an hour or 15 minutes in Excel by using the pivot table option

Step 1

To understand the process of grouping the data in half an hour or 15 minutes, a user needs to first consider the below provided Excel sheet. This Excel sheet contains three columns. The first column contains date fields, the second column contains record fields, and the third column contains data fields for the period. Users can change the cell formatting and syntax according to requirements.

Step 2

In this step will first create a helper column. To do this, first go to the F2 cell, and then create a column header for the values. By simply writing “calculated half time” to F1 cell. After that go to the F2 cell, and type formula “=MOD(INT(E2/(1/48))*(1/48),1)”. The correct way to do this is described below −

Explanation

• E2− Refers to the value in cell E2.

• 1/48− Represents fraction 1 divided by 48.

• INT(E2/(1/48))− Divides the value in cell E2 by 1/48 and rounds down to the nearest integer.

• (INT(E2/(1/48))*(1/48))− Multiplies the result of above step by 1/48.

• MOD((INT(E2/(1/48))*(1/48)), 1)− Calculates the remainder when dividing the result of above provided steps.

In summary, the formula calculates the fractional part of the value in cell E2 when divided by 1/48. The result will be a decimal value between 0 and 1

Step 3

Press the “Enter” key to obtain the formula value. The generated result value is 0. Consider the below-provided cell value, as depicted below −

Step 4

Drag the obtained results to 10, rows, this step will copy the formula for F2 to F10, rows. After the displayed values are shown in the general data type, the user needs to convert the column value to the time data type. To do so, click on the below-provided arrow. This will display the list of options, such as number, currency, and many others. From the provided list of options, select the option with the value, “Time”. Consider below given image snapshot for the proper reference.

Step 5

Drag the F2 cell to the F10 cell, this step will copy the same formula to different cell values. Now, observe the values of column F, the data appears in the time format. Now, users need to create a separate pivot table to solve the provided task.

Step 6

To do so, go to the “Insert” tab, and click on the “Tables” option. Among the displayed list of menu options, select the first option for “Pivot Table”. After that select the option for “From Table/Range”.

Step 7

The above step will display a “PivotTable from table or range” dialog box, as depicted below. In the appeared dialog box, under the label, table/range select the table data values, and select the option for “Existing Worksheet”. In the location option, the user needs to provide the location where the user wants to place the pivot table. After that click on the “OK” button.

Step 8

The above step will display the dialog box for “Pivot Table Fields”. In the rows section, drag the data values for date and half time. In the values cell column, drag the field data values for the sum of records.

Step 9

The above step will generate a pivot table as given below. This pivot table contains separate header for month name, along with required time, and also displays the sum of records.

Step 10

In case the user wants to display data for 15 minutes, write the formula for “=FLOOR(C2,TIME(0,15,0))”, in the above-mentioned formula for the helper column, and repeat processing for steps 3 to step 9.

Conclusion

After going through all the provided steps, the user will be able to generate a pivot table from the provided normal table in Excel and can also be able to group the data into half an hour or 15 minutes by using a pivot table. This example describes a simple way to solve the required task.