How to Convert 15-Minute Data into Hourly Values in Excel
Question details
The user needs a method to efficiently aggregate 15-minute interval time-series data into hourly periods without deleting rows or causing application instability.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Summarizing high-frequency time-series measurements into larger hourly buckets for easier reporting and analysis.
- Observed behavior
- Attempting to manage the data by deleting blank cells in large worksheets causes Excel to become unstable, prompting the need for a non-destructive aggregation method.
Ensure your time-series data is formatted as legitimate Date/Time values rather than plain text, and verify that there are no merged cells within your dataset.
Use a PivotTable with Built-in Time Grouping
The fastest way to aggregate time-series data is by using the built-in PivotTable grouping feature, which requires no formulas.
This method keeps your original data completely intact while providing an automated summary. It prevents the software crashes often caused by mass-deleting blank rows.
Select your entire dataset, navigate to the Insert tab on the ribbon, and click PivotTable. Choose to place it on a New Worksheet.
Drag your Date/Time field into the Rows area, and drag your measurement or performance value field into the Values area.
Right-click on any time entry in the Rows column of your PivotTable and select Group from the context menu.
In the Grouping dialog box, highlight Hours (you can deselect Months or Days if they are automatically selected) and click OK.

Use a Formula Helper Column and PivotTable
If automatic grouping fails due to dataset inconsistencies, manually rounding timestamps with the FLOOR function is a reliable alternative.
Group by Hour Using Power Query
Ideal for large, recurring datasets that need automated cleaning and grouping without manual formula updates.
Easily Group Time-Series Data in WPS Spreadsheet
WPS Spreadsheet provides powerful PivotTable and formula capabilities identical to Microsoft Excel, enabling you to group and aggregate interval data in seconds without software freezing or crashes.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the 15-minute interval data.
- 2. Insert a PivotTable: Highlight your dataset, click the Insert tab, and select PivotTable.
- 3. Group by Hours Automatically: Place your time data in the Rows field, right-click any timestamp cell, choose Group, and select Hours to instantly aggregate the results.

Frequently Asked Questions
Why is the 'Group' option grayed out in my PivotTable?
If the Group option is unavailable, it usually means some cells in your date/time column contain text strings, errors, or blank values instead of true date/time formats. Ensure the entire column is formatted correctly as Date or Time.
Can I calculate the average of my 15-minute data per hour instead of the sum?
Yes. Once your data is grouped by hour in the PivotTable, right-click on any aggregated number in the Values area, select 'Value Field Settings', and change the summarization type from Sum to Average.
What happens to missing 15-minute intervals when grouping to hours?
PivotTables and Power Query handle missing records seamlessly. They will simply aggregate the available data points for that specific hour without causing calculation errors, eliminating the need to manually delete blank rows.
Can I group my data by both days and hours simultaneously?
Yes. When you right-click the time field and select 'Group' in the PivotTable, you can click on multiple time intervals such as Days and Hours at the same time to create a collapsible, hierarchical view of your data.




