Formula Errors

Keep Excel AVERAGE Formulas Starting at the First Row

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

Keep the starting row of an AVERAGE range fixed while extending the ending row as the formula is filled down.

Keep Excel AVERAGE Formulas Starting at the First Row
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

Select cell C2 and enter =AVERAGE(B$1:B2), then press Enter. The dollar sign fixes row 1 in the starting reference.

2
Fill the formula down

Select C2 and drag the fill handle at its lower-right corner down through the rows you want to calculate.

3
Check the expanded ranges

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.

Lock the starting row in the AVERAGE formula
About the fixed reference: B$1 fixes the row but not the column. If you also need column B to stay fixed when copying across, use $B$1 as the starting reference.
Work with formulas in WPS

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. 1. Open your worksheet: Open WPS Spreadsheets and select Open to load your workbook, or create a new spreadsheet.
  2. 2. Enter the formula: With values in column B, select C2 and enter =AVERAGE(B$1:B2), then press Enter.
  3. 3. Fill down: Select C2 and drag its lower-right fill handle down to calculate averages for the remaining rows.
  4. 4. Verify a result: Select C3 and check the formula bar to confirm that the range is B$1:B3.
Calculate an expanding average with a fixed starting rowOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.