Include New Excel Pivot Table Rows in an Average Formula
Question details
The user needs an average formula outside an Excel PivotTable to include new pivot rows automatically.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- New records are added to the PivotTable source, creating additional rows after the PivotTable is refreshed.
- Observed behavior
- The existing AVERAGE formula uses a fixed range and does not include the new PivotTable rows.
Identify the PivotTable value column and its first data cell. For the example below, B4 is the only header in column B, B5 is the first value, and the average formula is placed in another column.
Use OFFSET and COUNTA to expand the average range
Use this formula when the PivotTable values form a continuous list in a dedicated column.
OFFSET starts at B5 and uses COUNTA to determine the range height. The subtraction of 1 excludes the header in B4.
In column B, keep only the B4 header and the contiguous PivotTable values starting at B5. Place the average formula outside column B.
Select the PivotTable, then choose Design > Grand Totals > Off for Rows and Columns. Remove subtotals as well if they appear in the value column and should not be averaged.
In a cell outside the PivotTable and column B, enter =AVERAGE(OFFSET(B5,0,0,COUNTA(B:B)-1,1)).
After adding source records, right-click the PivotTable and select Refresh. The formula expands to include the new contiguous values.

Use an Excel table as the PivotTable source
Use a structured source table so new source records are included when the PivotTable is refreshed.
Create a dynamic PivotTable average in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, refresh the PivotTable, and use OFFSET with COUNTA to average a value range that grows with new rows.
- 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the Excel workbook.
- 2. Check the PivotTable layout: Confirm that B4 is the only populated header in column B and that the values start at B5 without gaps. Use the PivotTable settings to exclude totals from the value list.
- 3. Add the formula: Select a cell outside column B and the PivotTable, then enter =AVERAGE(OFFSET(B5,0,0,COUNTA(B:B)-1,1)).
- 4. Refresh after adding data: After adding source records, right-click the PivotTable and select Refresh, then check that the average includes the new rows.

Frequently Asked Questions
Why doesn't my fixed AVERAGE range include new PivotTable rows?
A formula such as =AVERAGE(B5:B10) keeps those cell references when the PivotTable grows. Replace the fixed range with a dynamic range, such as the OFFSET formula shown above.
Will an Excel source table expand the average formula automatically?
No. An Excel table can expand the PivotTable's source data, but you still need to refresh the PivotTable and use a dynamic external formula to include new pivot rows.
Why is my dynamic average incorrect after I refresh?
Check column B for extra populated cells, blank gaps, subtotals, or a grand total. COUNTA counts nonempty cells across the entire column, so the example formula requires a dedicated, contiguous value list and one header.
How do I keep a grand total out of the average?
Select the PivotTable and choose Design > Grand Totals > Off for Rows and Columns. Also check for subtotals in the value column before using the dynamic formula.




