Data Import & Export

Convert Five-Minute Data to One-Minute Intervals in Excel

Steven ParkerSteven Parker Oct 28, 2026 869 views

Question details

The user needs to expand an existing Excel dataset recorded in five-minute increments into a continuous dataset with one-minute increments.

How to Convert Five-Minute Data to One-Minute Intervals in Excel
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 you start

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.

Solution 1Recommended

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.

1
Generate the first timestamp

In a new blank column, type the starting date and time of your dataset (e.g., in cell D2).

2
Add one-minute increments

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.

3
Lookup existing data

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)`.

4
Repeat previous values

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.

Create One-Minute Intervals Using Formulas
Formula Tip: If you are using an older version of Excel that does not support XLOOKUP, you can use VLOOKUP with the range_lookup argument set to TRUE (approximate match) to achieve the same repeating value effect.
Easy Data Management

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. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your five-minute interval data.
  2. 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. 3. Drag to fill: Select the cell with the formula and drag the fill handle down to populate your entire desired time range.
  4. 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.
Full support for advanced lookup functions like XLOOKUP and VLOOKUPHigh compatibility with Microsoft Excel (.xlsx) formatsLightweight and fast for processing large time-series datasetsFamiliar user interface for seamless data manipulation
microsoft office alternative - wps office

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.