If you are trying to resample time series in Excel, then you can follow this article. Sometimes you may need to redistribute the time spaces of your dataset to resample it, to represent it differently. You may want to extract a small portion of a large dataset or want to get the values for a particular time period that is not showing directly in your dataset. So, in this article, we will discuss the possible ways to resample the time series of your dataset easily.

**Table of Contents**Expand

## How to Resample Time Series in Excel: 3 Examples

Here, we have the following time series and the gap between time is **0.1**, starting from **0 **to **10**. With respect to these time periods, we have kept records of the population of a species. We will use this dataset for most of the cases in the following examples to resample this time series.

For creating this article, we have used ** Microsoft 365** version. However, you can use any other version at your convenience.

__Example-1__: Resampling a High-Frequency Time Series Data to a Low-Frequency Time Series Data in Excel

As we introduced earlier that the time duration of our dataset is between **0 **to **10 **and the gap between each time period is **0.1**. So, we will convert this high-frequency time series distribution to a low-frequency distribution data series. In our resampled time series data the gap between each time period will be **1**.

To formulate this new dataset, here we are considering the starting row number of our main dataset which is **5**, and the gap between each row will be **10**.

** Steps**:

- Link up the value of cell
**D8**with the**Start Row**column.

`=E4`

- For having the
*second-row number*, use the following formula in cell**D9**.

`=D8+$E$5`

Here, **D8 **is the previous cell value, and **$E$5 **is the step value which will be added to the *previous row number* to give us the *next row number*.

- Press
**ENTER**and drag down the**Fill Handle**tool.

In this way, we gathered the row numbers from which we will extract the* time series* data and their corresponding *population.*

- For gaining the time values insert the following formula in cell
**E8**, press**ENTER**, and finally, use the**AutoFill**feature.

`=INDIRECT(ADDRESS(D8,2))`

** **__Formula Breakdown__

**ADDRESS(D8,2) →**becomes**ADDRESS(5,2) → The ADDRESS function**gives the address of a cell at the intersection point of**Row 5**and**Column 2**.**Output → “$B$5”**

**INDIRECT(ADDRESS(D8,2)) →**becomes**INDIRECT(“$B$5”) → The INDIRECT function**returns the value of cell**“$B$5”****Output → 0**

- Now we will get the population values by using the following formula in cell
**F8**, pressing**ENTER**, and finally, using the**AutoFill**feature.

`=INDIRECT(ADDRESS(D8,3))`

__Formula Breakdown__

**ADDRESS(D8,3) →**becomes**ADDRESS(5,3) →**gives the address of a cell at the intersection point of**Row 5**and**Column 3**.**Output → “$C$5”**

**INDIRECT(ADDRESS(D8,3)) →**becomes**INDIRECT(“$C$5”) →**returns the value of cell**“$C$5”****Output → 0**

__Example-2__: Extracting a Sub-Sample from a Larger Sample Data to Resample Time Series

In the following time series data, we have starting time from **6:00 AM, **and then the time period increases by **1 **minute.

This sample data ends at **7:40 AM**. We want to get a sub-sample of this data to resample this time series. To do this we will gather the values for time series with a time span of **5 minutes** instead of **1 minute**. To do this, we have added a **Helper** column.

** Steps**:

- Apply the following formula in cell
**C5**.

`=MOD(MINUTE(B5),5)=0`

__Formula Breakdown__

**MINUTE(B5) →**becomes**MINUTE(6:00) →****The MINUTE function**returns the minute value**Output → 0**

**MOD(MINUTE(B5),5) →**becomes**MOD(0,5) →****The MOD function**gives the remainder value after dividing**0**by**5****Output → 0**

**MOD(MINUTE(B5),5)=0 →**becomes**0=0 →**returns**TRUE**when the remainder value is**0**otherwise**FALSE****Output → TRUE**

- Press
**ENTER**and drag down the**Fill Handle**tool.

** **

In return, we will get **TRUE **for time periods after every **5 **minutes.

Now, we have to sort this data to gather the time periods having a **TRUE **value in the **Helper **column.

- Select the dataset and then go to the
**Data**tab >>**Sort & Filter**group >>**Sort**.

Afterward, the **Sort **dialog box will appear.

- Select the following options.
**Sort by → Helper****Sort On → Cell Values****Order → Largest to Smallest**

- Click on
**Add Level**option to add other criteria for sorting.

- Choose the following options.
**Then by → Time****Sort On → Cell Values****Order → Smallest to Largest**

- Press
**OK**.

In this way, we have gathered all of the time periods with a gap of **5 **minutes between each other.

- Select the values of the
**Time**column and**Population**column up to**TRUE**in the**Helper**column (you have to press**CTRL**while selecting these non-adjacent columns) and press**CTRL+C**to copy it.

- After pasting these values we will be able to resample our time series data like the following figure.

**Read More: **How to Perform Bootstrapping in Excel

__Example-3__: Resampling Time Series after Doing Interpolation

Here, we will resample the following time series data where we have *population *values starting at **0 sec **and ending at **10 sec **with a step of **0.1 sec **between each time period. The resampled data will be gathered in the table beside this time series data. For resampling, we have to interpolate as the values are missing for these time periods. For doing the interpolation, we will be using **the FORECAST.ETS function**.

** Steps**:

- Apply the following formula in cell
**E5**.

`=FORECAST.ETS(D5,$C$5:$C$105,$B$5:$B$105)`

Here, **D5 **is the time value for which we are calculating the *population value*. **$C$5:$C$105 **is the range of *population,* and **$B$5:$B$105 **is the *time series* sample.

- Press
**ENTER**and drag down the**Fill Handle**tool.

In this way, we have completed the interpolation to create the new resampled time series data.

**Read More:** How to Calculate Bootstrapping Spot Rates in Excel

## Practice Section

To practice by yourself, we have created a *Practice* section on the right side of each sheet.

**Download Practice Workbook**

## Conclusion

In this article, we have discussed different ways to resample time series data in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.

## Related Articles

- Comparison Among MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL Functions in Excel
- How to Calculate Margin of Error in Excel
- How to Find Mean, Median, and Mode on Excel

**<< Go Back to Excel for Statistics | Learn Excel**