Pivot Table Issues

Include New Excel Pivot Table Rows in an Average Formula

Huda QurayshiHuda Qurayshi Nov 18, 2026 870 views

Question details

The user needs an average formula outside an Excel PivotTable to include new pivot rows automatically.

Include New Excel Pivot Table Rows in an Average Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the value column

In column B, keep only the B4 header and the contiguous PivotTable values starting at B5. Place the average formula outside column B.

2
Exclude PivotTable totals

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.

3
Enter the dynamic formula

In a cell outside the PivotTable and column B, enter =AVERAGE(OFFSET(B5,0,0,COUNTA(B:B)-1,1)).

4
Refresh the PivotTable

After adding source records, right-click the PivotTable and select Refresh. The formula expands to include the new contiguous values.

Use OFFSET and COUNTA to expand the average range
Check what COUNTA includes: COUNTA counts every nonempty cell in column B, including cells containing formulas that return an empty string. Extra headers, filters, totals, unrelated data, or blank gaps can make this range inaccurate. Adjust the count only when those extra cells are consistently present, and verify the result after refreshing.
Work with PivotTables in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the Excel workbook.
  2. 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. 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. 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.
Include new contiguous PivotTable values in a dynamic averageOpen and edit Microsoft Excel XLSX and XLS filesWork with formulas and PivotTables in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.