Keep an Excel AVERAGE Formula Range Fixed When Filling Cells
Question details
The user needs to fill an AVERAGE formula without changing the cell range used in the calculation.
- Product
- Microsoft Excel formula; WPS Spreadsheets alternative
- Device & OS
- not provided
- Scenario
- The same average of one totals range must appear in multiple cells after filling a formula across or down.
- Observed behavior
- Relative cell references change automatically when the AVERAGE formula is copied to another cell.
Identify whether every copied formula should use one fixed range or whether the range should move with each destination cell. Make sure the source cells contain numeric values, because text and blank cells are handled differently by AVERAGE.
Use absolute references to lock the AVERAGE range
Use this method when every filled formula must calculate the average from the exact same cells.
Absolute references use dollar signs before the column letter and row number. They prevent the source range from shifting when you copy or fill the formula.
Click the cell that contains the AVERAGE formula, such as =AVERAGE(B2:B10).
In the formula bar, change the range to absolute references: =AVERAGE($B$2:$B$10). You can also select B2:B10 in the formula and press F4 to cycle reference types where supported.
Press Enter to apply the updated formula to the selected cell.
Drag the fill handle from the lower-right corner of the formula cell across or down, or copy the cell and paste it into the target cells. Each formula will continue to use $B$2:$B$10.
Use relative references when the averaging range should move
Use this method when each copied formula should average the corresponding range in the next row or column.
Use a table column reference for an expanding totals list
Use a table when the totals range may gain new rows and the average should update automatically.
Lock an AVERAGE Formula Range in WPS Spreadsheets
WPS Spreadsheets lets you use absolute references to keep an AVERAGE source range fixed while filling formulas across a worksheet. Enter dollar signs in the reference, then use the fill handle to copy the formula without changing the range.
- 1. Open the worksheet: Open the workbook in WPS Spreadsheets and select the cell containing the AVERAGE formula.
- 2. Edit the formula: Click the formula bar and change =AVERAGE(B2:B10) to =AVERAGE($B$2:$B$10).
- 3. Apply the locked reference: Press Enter to confirm the formula and verify that the dollar signs appear in the cell formula.
- 4. Fill the formula: Drag the fill handle at the lower-right corner of the selected cell to copy the formula to the required rows or columns.
Frequently Asked Questions
Why does my AVERAGE formula change when I drag it down?
Excel and WPS Spreadsheets treat references such as B2:B10 as relative by default. When you fill down, the range shifts to match the new row position.
How do I keep only the row or column fixed in an AVERAGE formula?
Use a mixed reference. For example, B$2:B$10 locks rows 2 through 10 but lets the column change, while $B2:$B10 locks column B but lets rows change.
Can I use F4 to add dollar signs to an Excel formula?
Yes. While editing a formula, select or place the cursor in the reference and press F4 to cycle through relative, absolute, and mixed reference types. If your keyboard requires it, use Fn+F4.
Will =AVERAGE($B$2:$B$10) update if values in B2:B10 change?
Yes. Absolute references lock the location of the range, not its values. The average recalculates whenever a numeric value in B2:B10 changes.
What formula should I use if my totals list expands regularly?
Convert the range to a table and use a structured reference, such as =AVERAGE(Table1[Totals]). The table column reference expands when new rows are added to the table.
