Convert Five-Minute Data to One-Minute Intervals in Excel
Question details
The user needs to expand an existing Excel dataset recorded in five-minute increments into a continuous dataset with one-minute increments.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with time-series data that requires higher frequency (one-minute intervals) than the original source data (five-minute intervals) for granular analysis.
- Observed behavior
- The current dataset has four-minute gaps between records that need to be populated with new one-minute timestamps and corresponding calculated or repeated data values.
Before expanding your time-series data, decide how the new one-minute rows should handle data values: whether they should repeat the previous known value, remain blank, or use calculated interpolated values based on your analytical requirements.
Create One-Minute Intervals Using Formulas
You can quickly generate a new column of continuous one-minute timestamps and use lookup formulas to bring in or repeat your data.
This method is highly effective for datasets where you simply want to repeat the last known five-minute value for the subsequent four minutes until the next data point is reached.
In a new blank column, type the starting date and time of your dataset (e.g., in cell D2).
In the cell directly below (D3), enter the formula `=D2 + TIME(0,1,0)`. Press Enter, and then drag the fill handle down to generate as many one-minute intervals as you need.
In the adjacent column (E2), use the XLOOKUP function to map your original data to the new timestamps. For example: `=XLOOKUP(D2, A:A, B:B, "", -1)`.
By setting the match mode in XLOOKUP to '-1' (exact match or next smaller item), Excel will automatically repeat the last known five-minute value for the newly created one-minute rows.

Calculate Missing Values via Linear Interpolation
If you need to estimate the data points for the newly created one-minute intervals rather than repeating them, you can use mathematical interpolation.
Convert Time-Series Data Smoothly in WPS Spreadsheet
WPS Spreadsheet offers powerful formula capabilities including advanced lookup and time functions, making it simple to expand datasets and interpolate missing values efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your five-minute interval data.
- 2. Create a new timestamp column: Select a blank column and enter your start time. In the next row, add `=TIME(0,1,0)` to the previous cell to generate a one-minute interval.
- 3. Drag to fill: Select the cell with the formula and drag the fill handle down to populate your entire desired time range.
- 4. Map your values: Use WPS Spreadsheet's built-in XLOOKUP or VLOOKUP functions to seamlessly map your existing five-minute data to the new timeline.

Frequently Asked Questions
Can I just repeat the five-minute value for the four subsequent one-minute rows?
Yes. When using XLOOKUP to bring data into your new one-minute timestamp column, set the match mode to -1 (exact match or next smaller item). This will repeat the previous five-minute value until the next actual data point is reached.
How do I handle date and time combined in one Excel cell?
Excel treats combined dates and times as serial numbers. You can safely add the TIME(0,1,0) function directly to a cell containing a combined date and time to advance the record by exactly one minute without losing the date context.
What if my dataset is huge and dragging the formula takes too long?
For large datasets, you can use the SEQUENCE function. For example, the formula `=start_time + SEQUENCE(total_minutes, 1, 0, 1/(24*60))` will instantly generate a spill array of one-minute intervals without the need to manually drag the fill handle.




