Calculation Issues

How to Convert 15-Minute Data into Hourly Values in Excel

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 869 views

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.

How to Convert 15-Minute Data into Hourly Values in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Insert a PivotTable

Select your entire dataset, navigate to the Insert tab on the ribbon, and click PivotTable. Choose to place it on a New Worksheet.

2
Configure the PivotTable Fields

Drag your Date/Time field into the Rows area, and drag your measurement or performance value field into the Values area.

3
Group by Hour

Right-click on any time entry in the Rows column of your PivotTable and select Group from the context menu.

4
Select Hourly Intervals

In the Grouping dialog box, highlight Hours (you can deselect Months or Days if they are automatically selected) and click OK.

Use a PivotTable with Built-in Time Grouping
Pro Tip: By default, the Values area will Sum your data. If you need the hourly average instead, right-click the Values column, select Value Field Settings, and choose Average.
Efficient Data Aggregation

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. 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the 15-minute interval data.
  2. 2. Insert a PivotTable: Highlight your dataset, click the Insert tab, and select PivotTable.
  3. 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.
Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Robust PivotTable features with built-in time grouping for instant hourly analysis.Lightweight architecture handles massive datasets smoothly without freezing.
microsoft office alternative - wps office

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.