Pivot Table Issues

Calculate Averages by Person in an Excel PivotTable

Steven ParkerSteven Parker Nov 14, 2026 868 views

Question details

Calculate a separate average for each person across weekly data in an Excel PivotTable.

How to Calculate Averages by Person 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a source table

Select the source range, choose Insert > Table, and confirm that the table has headers for Name, Week Number, and Number.

2
Insert a PivotTable

Click inside the table, select Insert > PivotTable, and choose where to place the PivotTable.

3
Arrange the fields

In PivotTable Fields, drag Name to Rows and Number to Values. Optionally, drag Week Number to Columns to see results for individual weeks.

4
Change the calculation

Open the menu for Number in the Values area, select Value Field Settings, choose Average, and click OK.

5
Show and refresh averages

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.

Set up an Excel PivotTable to average values by person
Understand the average: Each person's total averages the underlying numeric records, not necessarily the displayed weekly averages. If weeks contain different numbers of records, the two calculations can differ.
Average weekly data in WPS

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. 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. 2. Create a PivotTable: Select the data range and choose Insert > PivotTable, then choose where to place it.
  3. 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. 4. Select Average: Open the settings for the Number value field, choose Average as the calculation, and confirm.
Calculate a separate average for each personOptionally display weekly results alongside each person's totalOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
QA img-9

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.