How to Calculate an Average in an Excel Pivot Table

Charts & pivot tables · Updated November 18, 2026

To calculate an average in an Excel PivotTable, add the numeric field you want to average to the Values area, open that field’s Value Field Settings, and choose Average under Summarize Values By. Excel then calculates the mean for each row or column group, based on the source records included in the PivotTable. Before you start, make sure your source data has clear column headings and that the values to average are stored as numbers.

What You’ll Need

  • An Excel workbook containing a list of records, with one record per row and a heading at the top of each column.
  • A column of numeric values to average, such as hours, units sold, scores, or prices. Check that numbers have not been imported as text.
  • Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. The main PivotTable options are similar in Excel for Mac, though their placement can differ slightly.

Step-by-Step

  1. Check and prepare the source data. Put the field you want to group by—such as department, product, or month—in its own column, and put the values to average in another. Use one header row with a distinct heading for every column. Remove completely blank rows and columns from inside the list, and check for merged cells, subtotals, or notes embedded in the data. A PivotTable works most reliably when each row represents one observation and each column contains one type of information. For example, a sales list might have headings for Date, Region, Product, and Units Sold, with one sale per row.
  2. Select a cell in the data. Click any populated cell in the source list. If the range is organized as an Excel Table, clicking inside it is enough; Excel can use the table as the PivotTable source and the table can expand as you add records. If it is a normal range, check that the surrounding data is contiguous and that Excel selects the complete list when you create the PivotTable.
  3. Insert the PivotTable. On the Windows ribbon, choose Insert > PivotTable. In the Create PivotTable dialog, confirm the Table/Range is correct, choose whether to place the PivotTable in a New Worksheet or an Existing Worksheet, then select OK. If you choose an existing worksheet, select a clear, empty destination cell so the PivotTable will not overwrite other content. Excel for Mac has a similar PivotTable command on the Insert tab; the dialog labels and layout can vary by release.
  4. Choose the fields that define the groups. In the PivotTable Fields pane, drag the category you want to compare into Rows. For example, drag Region to Rows to get one result per region. To split those results into another level, drag a second category, such as Product, below Region in Rows. You can also put a category in Columns to display groups across the top. The field arrangement controls how results are divided; it does not change the source records.
  5. Add the numeric field to Values. Drag the numeric column you want to average into the Values area. Excel may initially label it Sum of Units Sold or Count of Units Sold, depending on the data and its initial interpretation. That default is not necessarily the calculation you need. The next step changes how Excel summarizes this field.
  6. Open the value field settings. In the Values area of the PivotTable Fields pane, select the drop-down arrow beside the field name and choose Value Field Settings. You can also right-click a value in the PivotTable and choose Summarize Values By or Value Field Settings, depending on the menu shown in your Excel version. In Excel for Mac, use the equivalent value-field settings command in the PivotTable field list or context menu.
  7. Choose Average. In the Value Field Settings dialog, find the Summarize Values By tab. Select Average from the list, then choose OK. The field label usually changes to Average of Units Sold, and the PivotTable displays an average for each group. If Average is unavailable or produces blanks, inspect the source column for numbers stored as text or a selection that contains no numeric values.
  8. Format the results and check the total. To change decimals or display a unit, reopen Value Field Settings and select Number Format. Choose an appropriate format, such as Number with two decimal places or Percentage, and confirm the dialogs. Use a format that matches the data: currency formatting does not change the calculation, but it can make the result easier to interpret. Compare one group with its source records as a reasonableness check. The grand total is an average across the included source records, not necessarily the simple average of the displayed group averages.
  9. Refresh after changing the source. If you add, edit, or remove source records after making the PivotTable, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh on the Windows ribbon. If new records fall outside a normal range used as the source, the PivotTable may not include them; adjust the source range or convert the list to an Excel Table. Refreshing updates the calculation but does not repair values stored as text.

Example: Suppose a Region field has North records of 10, 20, and 30 units, and a South record of 40 units. With Region in Rows and Units in Values summarized by Average, North shows 20 and South shows 40. The grand total is 25, because the four source values total 100 and 100 divided by 4 is 25. It is not 30, which would be the unweighted average of the two displayed regional averages, 20 and 40. This difference matters when groups contain different numbers of records.

Common Mistakes

  • Leaving the calculation as Sum or Count. Seeing a number in the PivotTable does not mean it is an average. Check the value field label and its Value Field Settings whenever the summary method matters.
  • Mixing numbers with text. A value that looks like 12 may actually be text, especially after importing data. Text entries are not treated as numeric observations in an average and can be skipped. Convert the source values to real numbers, then refresh the PivotTable.
  • Treating blanks and zeroes as interchangeable. Blank cells do not contribute a numeric value to the average; a zero is a number and does contribute. If a blank means “not measured,” leaving it blank may be appropriate. If it means a measured result of zero, enter zero. Do not replace blanks with zero unless that matches what the data means.
  • Averaging averages without weighting. The grand total may differ from the average of the visible group averages because groups can contain unequal numbers of records. For the overall average of all observations, use the PivotTable’s grand total average rather than calculating a simple mean of the group results.
  • Including summary rows in the source. If the source list contains manually calculated totals or averages as well as detail records, those summary rows can be counted as observations and distort the result. Build the PivotTable from the detail records only.
  • Forgetting filters. Report filters, slicers, and field filters can exclude records. The displayed average is based on the records currently included, so check active filters before comparing results with a calculation from the entire source.

Tips

  • Use an Excel Table for a growing list. Select the data and choose Insert > Table, making sure the headers option is correct. A PivotTable based on that table can include newly added table rows after you refresh it, rather than requiring you to reset a fixed source range.
  • Keep the original observations. Store one measurement per row instead of pre-aggregating data into group totals. This lets the PivotTable calculate the average at different levels and makes it easier to investigate an unexpected result.
  • Use more than one view of the same field. If you need both total and average, add the numeric field to Values twice. Set one copy to Sum and the other to Average in Value Field Settings. Give the fields clear custom names, such as Total Units and Average Units, so readers know what each number means.
  • Choose a suitable number format. Formatting changes how a result is displayed, not the underlying average. Use enough decimal places to show useful differences, but avoid suggesting more precision than the source measurements support.
  • Document what a row represents. An average is meaningful only when the observations are comparable. If each row is a transaction, the result is an average per transaction—not necessarily an average per customer, day, or product. Make the unit of analysis clear in the PivotTable labels or nearby notes.

Troubleshooting

Average is missing from the summary options: Check that you selected a field in the Values area and opened its Value Field Settings. Then inspect the source values. If the column consists of text, dates, or blank cells rather than numbers, Excel may offer Count but not the calculation you expect. Correct the source data type and refresh the PivotTable.

The PivotTable shows Count instead of Average: Excel may have detected text or mixed content in the source field. In the source list, test a few cells by editing or re-entering them as numbers, and look for leading apostrophes or imported text formatting. Convert the affected values to numbers, refresh, and set the summary to Average again. Changing the number format alone does not convert text into numbers.

The result is blank or unexpectedly high or low: Check whether the current group has any numeric records, whether filters have removed records, and whether zeroes or blanks are being handled as intended. Look for duplicate rows, hidden summary rows, and outliers in the source. To verify a group, filter the source list to the same category and compare its numeric observations with the PivotTable result.

New rows do not appear: Refresh the PivotTable first. If the added rows are outside the original source range, choose PivotTable Analyze > Change Data Source on Windows and expand the range, or change the source to an Excel Table. Refresh again after correcting the source.

The grand total does not match the average of group results: This is often expected. Excel calculates the grand total from the included underlying values, so larger groups contribute more observations than smaller ones. If you intended every group to have equal influence, that is a different calculation from the average of all records; label and calculate it accordingly rather than assuming the PivotTable total should equal it.

Frequently Asked Questions

Does an Excel PivotTable average ignore blank cells?
Yes. Blank cells do not count as numeric observations in the average. Zeroes do count, so decide whether a missing measurement should remain blank or represent a true zero.

Can I show a sum and an average at the same time?
Yes. Add the same numeric field to the Values area twice, then set one copy to Sum and the other to Average. Rename the displayed fields so each calculation is clear.

Why is the PivotTable average different from my calculator result?
Check that you used the same records and included the same filters, blanks, and zeroes. Also confirm that all intended values are numeric and that you are comparing the overall average with the grand total, not with an unweighted average of group averages.

Can I calculate an average by month or category?
Yes. Put the category in Rows or Columns and the numeric field in Values, summarized by Average. For dates, you can group the date field into periods such as months when the PivotTable date-grouping option is available and the source contains valid dates.

Will the average update when I edit the source data?
Refresh the PivotTable after changing its source. If you add records outside the original range, expand the source or use an Excel Table as the source, then refresh. PivotTables do not always update automatically when source cells change.

Can I do this in Excel for the web or on a Mac?
Yes. Excel for Mac supports PivotTable value-field summaries, though ribbon and dialog placement can differ from Windows. Excel for the web also supports PivotTables, but some commands and options vary; look for the field’s summarize or value settings and select Average. If a specific control is unavailable in your browser version, open the workbook in desktop Excel.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.