Calculate Averages by Person in an Excel PivotTable
Question details
Calculate a separate average for each person across weekly data in an Excel PivotTable.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Weekly data needs to be summarized by name, and the existing data may already be arranged with weeks in separate columns.
- Observed behavior
- The PivotTable shows an overall average rather than the expected average for each name.
Check whether the source has one row per person and week, with Name, Week Number, and Number columns. Make sure the Number values are numeric and that names are spelled consistently.
Set up an Excel PivotTable to average values by person
Use this method when the source data has a separate row for each person and week.
Select the source range, choose Insert > Table, and confirm that the table has headers for Name, Week Number, and Number.
Click inside the table, select Insert > PivotTable, and choose where to place the PivotTable.
In PivotTable Fields, drag Name to Rows and Number to Values. Optionally, drag Week Number to Columns to see results for individual weeks.
Open the menu for Number in the Values area, select Value Field Settings, choose Average, and click OK.
If Week Number is in Columns, select the PivotTable and choose Design > Grand Totals > On for Rows and Columns to show an overall average for each name. After adding rows to the source table, right-click the PivotTable and select Refresh.

Reshape weekly columns before creating the PivotTable
Use Power Query when the source is already pivoted, with one column for each week.
Calculate per-person averages in WPS Spreadsheets
Use a PivotTable in WPS Spreadsheets to group numeric records by name and calculate an average for each person.
- 1. Prepare the data: In WPS Spreadsheets, open your workbook and arrange the data in columns named Name, Week Number, and Number, with one record per row.
- 2. Create a PivotTable: Select the data range and choose Insert > PivotTable, then choose where to place it.
- 3. Add the fields: In the PivotTable field list, drag Name to Rows and Number to Values. Add Week Number to Columns if you also want weekly results.
- 4. Select Average: Open the settings for the Number value field, choose Average as the calculation, and confirm.

Frequently Asked Questions
Why do I see only one overall average?
Check the PivotTable Fields pane and make sure Name is in Rows, not just Number in Values. Each name should then have its own result.
Can I average data when each week is a separate column?
Yes. In Excel, load the source table with Data > From Table/Range, select the identifier columns, and use Transform > Unpivot Columns > Unpivot Other Columns. Build the PivotTable from the loaded result.
Will the PivotTable include a new week automatically?
An Excel Table can expand to include new source rows, but the PivotTable still needs a refresh. Right-click it and choose Refresh. If the data passes through Power Query, use Data > Refresh All.
Why is the person's total different from the average of the weekly averages?
The PivotTable total averages the underlying numeric records. If some weeks have more records than others, it may differ from an equally weighted average of the displayed weekly averages.
Do blank cells count as zero in the average?
No. Excel's Average calculation ignores blank cells, while numeric zeros are included. Check the source Number column if the result is unexpected.




