Keep Excel AVERAGE Formulas Starting at the First Row
Question details
Keep the starting row of an AVERAGE range fixed while extending the ending row as the formula is filled down.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants each average to include the original starting cell B1 and all values through the current ending row.
- Observed behavior
- When the formula is filled down, the range shifts from B1:B2 to B2:B3 instead of continuing to start at B1.
Make sure your values start in B1 and choose an empty column for the results. The example places the first formula in C2 to avoid including the formula cell in its own range.
Lock the starting row in the AVERAGE formula
Use a mixed reference to keep row 1 fixed while the ending reference changes as you fill down.
Select cell C2 and enter =AVERAGE(B$1:B2), then press Enter. The dollar sign fixes row 1 in the starting reference.
Select C2 and drag the fill handle at its lower-right corner down through the rows you want to calculate.
Select C3 and check the formula bar for =AVERAGE(B$1:B3). In C4, the ending reference should be B4 while the starting reference remains B$1.

Create an expanding average in WPS Spreadsheets
Use the same mixed-reference formula in WPS Spreadsheets to keep the first row fixed while extending the average down your worksheet.
- 1. Open your worksheet: Open WPS Spreadsheets and select Open to load your workbook, or create a new spreadsheet.
- 2. Enter the formula: With values in column B, select C2 and enter =AVERAGE(B$1:B2), then press Enter.
- 3. Fill down: Select C2 and drag its lower-right fill handle down to calculate averages for the remaining rows.
- 4. Verify a result: Select C3 and check the formula bar to confirm that the range is B$1:B3.

Frequently Asked Questions
Why does B1 change to B2 when I fill the formula down?
B1 is a relative reference, so its row changes when the formula is copied down. Use B$1 to keep row 1 fixed.
How do I lock both the column and the starting row?
Use $B$1 as the starting reference, as in =AVERAGE($B$1:B2). Both the starting column and row will remain fixed when you copy the formula.
What range will the formula use in the next row?
If C2 contains =AVERAGE(B$1:B2), filling it down to C3 produces =AVERAGE(B$1:B3).
Does AVERAGE include blank cells or zeros?
AVERAGE ignores blank cells but includes cells containing zero. Check the values in column B if the result is not what you expect.




