Function Problems

Excel Formula to Advance by 36 Rows Using the Fill Handle

Kushani Nimanthika Sep 29, 2026 869 views

Question details

The user needs a formula to calculate averages for data grouped in consecutive 36-row chunks, ensuring the range automatically advances by exactly 36 rows each time the formula is dragged down.

How to Create an Excel Formula to Advance by 36 Rows with the Fill Handle

Product: Excel
Device & OS: not provided
Scenario: Analyzing large datasets by calculating the average of specific data chunks (e.g., L39:L74, L75:L110) automatically across multiple rows.
Observed behavior: Dragging a standard AVERAGE formula down using the fill handle only advances the cell references by 1 row at a time, rather than jumping the required 36 rows.

Before you start

Ensure your dataset is organized continuously without any blank rows breaking the 36-row chunks, as the formula relies on strict mathematical offsets to fetch the correct data range.

Solution 1Recommended

Use OFFSET and ROWS Functions for Dynamic Advancing

This is the most reliable method. By combining OFFSET with the ROWS function, the formula perfectly shifts your data range by 36 rows each time it is dragged down, regardless of which row you start your calculation in.

The OFFSET function lets you return a range that is a specific number of rows and columns away from a starting cell. By nesting the ROWS function inside OFFSET, we can create a multiplier that increases by 1 each time the formula moves down.

This formula structure is highly recommended for processing gigabytes of data because it adapts flawlessly even if you copy it to another starting row.

Select the target cell

Click on the cell where you want the first average to appear (for example, cell R1).

Enter the OFFSET formula

Type the following formula: =AVERAGE(OFFSET($L$39,(ROWS($R$1:R1)-1)*36,0,36,1)) and press Enter. This averages the first 36-row range (L39:L74).

Use the Fill Handle

Click the small square at the bottom-right corner of cell R1 and drag it down. R2 will now automatically average L75:L110, R3 will average L111:L146, and so on.

Use OFFSET and ROWS Functions for Dynamic Advancing

Formula Breakdown: $L$39 is your absolute starting point. (ROWS($R$1:R1)-1)*36 calculates how many rows to shift down, while the final '36' and '1' dictate the height and width of the range to average.

Solution 2

Use OFFSET and ROW Functions (Alternative Approach)

Use this slightly shorter variation if you are guaranteed to place your first calculation strictly in row 1 of your spreadsheet.

Powerful Data Analysis Tool

Easily Handle Large Datasets with WPS Spreadsheet

When processing massive datasets, you need a highly efficient tool. WPS Spreadsheet fully supports advanced array functions, OFFSET mechanics, and complex data chunking required to analyze millions of rows with ease.

1. Open your data file: Launch WPS Spreadsheet and open the large dataset you need to analyze.
2. Input the offset formula: Paste the combined AVERAGE and OFFSET formula into your designated calculation cell.
3. Drag to calculate: Use the intuitive fill handle to drag down and instantly average your 36-row chunks.

100% compatibility with Microsoft Excel formulas like OFFSET, AVERAGE, and ROWSLightweight architecture handles large data files smoothly without crashingFamiliar user interface means zero learning curve for Excel usersCompletely free to use for your daily data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

How can I change the formula to advance by 50 rows instead of 36?

To change the range size, replace the number '36' in the formula with '50'. You must change it in both places: the multiplier for the offset rows and the height parameter of the range. The updated formula would be: =AVERAGE(OFFSET($L$39,(ROWS($R$1:R1)-1)*50,0,50,1)).

Can I use this formula to SUM data instead of finding the AVERAGE?

Yes. The OFFSET function simply retrieves the data range. You can wrap any aggregate function around it. Just replace 'AVERAGE' with 'SUM' at the beginning of the formula, like this: =SUM(OFFSET($L$39,(ROWS($R$1:R1)-1)*36,0,36,1)).

Why do I get a #REF! error when dragging the formula down?

A #REF! error occurs when the OFFSET formula attempts to reference a cell that falls outside the boundaries of the spreadsheet (for example, beyond the maximum 1,048,576 rows). Ensure you do not drag the formula down further than your source data actually extends.

Why is the ROWS($R$1:R1) function better than the ROW() function?

ROWS($R$1:R1) counts the total number of rows within the specified range, which naturally expands as you drag down (becoming R1:R2, R1:R3, etc.), starting securely at 1. The ROW() function evaluates the absolute row number where the formula sits. If you move your starting cell to row 5, ROW() returns 5, which breaks the math, whereas ROWS() still correctly returns 1.

Excel & Data Problems