Excel Pivot Table Average: Add a Column and Set It Up

Charts & pivot tables · Updated November 18, 2026

To add an average column to an Excel PivotTable, drag the numeric field into the Values area a second time, then right-click a value in the new field and choose Summarize Values By > Average. This keeps the original summary, such as Sum, and adds a separate average alongside it.

What an average column means in a PivotTable

A PivotTable groups source records into categories and summarizes their values. For example, a sales report might show total sales by salesperson. Adding the Sales field to Values again and summarizing the second copy by Average lets the same report show both total sales and average sale amount for each salesperson.

The new average is a value field, not a new column in the original source data. Excel calculates it for every group in the PivotTable, such as each salesperson, region, month, or combination of row and column labels. You can also choose Average as the summary for a field that is already in Values; in that case, the field shows averages instead of its previous summary rather than adding a second measure.

Add Average while keeping Sum

  1. Click a cell in the PivotTable. If the PivotTable Fields pane is not visible, select the PivotTable and choose PivotTable Analyze > Field List.
  2. In the field list, drag the numeric source field—for example, Sales—into the Values area a second time. You should now see two entries for that field in Values.
  3. Right-click a number in the second measure’s column in the PivotTable and choose Summarize Values By > Average. You can also open Value Field Settings from the right-click menu and select Average under Summarize Values By.
  4. Select OK. Excel calculates the average for each group while retaining the original Sum field.

Excel commonly labels the fields Sum of Sales and Average of Sales. The exact captions depend on the source field name and the summaries in use. If Excel says that the PivotTable field name already exists when you try to rename a value field, use a distinct caption, such as Average Sale, rather than trying to give both measures the same name.

Choose the right layout for the new measure

When a PivotTable has category fields in Rows and more than one field in Values, Excel usually places the measures in separate columns. The column headings may appear beneath a heading called Values. For instance, with Salesperson in Rows and Sales in Values twice, the table can display each person’s total and average in adjacent columns.

If the measures appear as rows instead, move the Values field in the layout areas to Columns. In the PivotTable Fields pane, the four areas are Filters, Columns, Rows, and Values. The layout depends on the other fields already in the PivotTable, so moving Values or another field can change how the report is arranged.

  • Use Rows for categories you want listed down the report, such as salesperson or product.
  • Use Columns for categories that should run across the top, such as year or region.
  • Keep both summary measures in Values, then place the Values field in Columns if you want the measures side by side.

For a more conventional table with clear headers, select a PivotTable cell and choose Design > Report Layout > Show in Tabular Form. This changes the report’s layout; it does not change how Average is calculated. You can also use Design > Report Layout > Repeat All Item Labels when repeating category names would make the report easier to read.

What Excel includes in the average

For each group, Excel averages the numeric source values belonging to that group. A value of zero is included in the calculation; a blank cell is not treated as a zero. Text entries in a numeric field are not included in the average calculation. This distinction matters: if a salesperson has recorded values of 100, 200, and 0, the average is 100. If the third record is blank instead, the average of the two numeric entries is 150.

A PivotTable average is an ordinary arithmetic mean, not a weighted average. Suppose two products have average prices of $10 and $100, but the first average is based on 100 transactions and the second on one transaction. The average of those two displayed averages is not the overall average price across all 101 transactions. To report an overall mean correctly, calculate it from the underlying records or use a measure designed for that calculation. Do not average subgroup averages unless the groups have equal numbers of observations or that is specifically the statistic you want.

Check what each source row represents before interpreting the result. If a row is a transaction, Average of Sales gives the mean transaction amount. If a row is a monthly summary, it gives the mean of those monthly totals, not the mean transaction amount. The PivotTable summarizes the records it receives; it cannot infer a different level of detail.

Format the average column

To change the number format, right-click a value in the average field and choose Value Field Settings. Select Number Format, choose a format such as Currency, Number, or Percentage, set decimal places and other options, then select OK. Applying a number format through Value Field Settings is preferable to formatting a few cells directly because the field’s format is more likely to remain consistent when the PivotTable updates.

Give the average a readable custom name in the Custom Name box in Value Field Settings, for example Average Sale. If you want the caption to distinguish it clearly from a total, use a name that describes the statistic and its units. An average quantity, average price, and average percentage are different measures, even if they come from fields with similar names.

If you need to change the width of the result column, drag its edge or use Home > Format > AutoFit Column Width. Excel may adjust widths when a PivotTable refreshes, depending on the PivotTable options. If your chosen width keeps changing, right-click the PivotTable, select PivotTable Options, and review the Autofit column widths on update setting.

Source data and refreshes

A standard PivotTable needs a source field containing values Excel can summarize. Check that the source column has a clear header and that its entries are genuinely numeric where you expect an average. Values imported as text—for example, numbers with unexpected spaces or inconsistent symbols—can be excluded from an average or lead to an unavailable summary option. Correct the source data, then refresh the PivotTable.

After changing or adding source records, refresh the report. Select a PivotTable cell and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. On Windows, Alt+F5 refreshes the active PivotTable, while Ctrl+Alt+F5 refreshes all. On Mac, use the Refresh command on the PivotTable Analyze tab; keyboard shortcuts can differ by Mac model and Excel configuration.

If new records are outside the PivotTable’s source range, refreshing alone may not include them. One useful setup is to convert the source range into an Excel table: select a source cell and choose Insert > Table, confirm the range and headers, then build the PivotTable from that table. When rows are added to the table, refresh the PivotTable so it can include them. A PivotTable does not necessarily update the moment its source changes.

Common problems and how to fix them

  • Average is unavailable or disabled: Verify that the field contains numeric values and is in the Values area. In a PivotTable based on the Data Model, some summarization options may differ; a DAX measure may be needed for a specialized calculation.
  • The average looks too high or low: Check for zeros, blanks, text-formatted numbers, duplicate records, and the level of detail in the source. Confirm that the average is intended to be per transaction, per day, or per some other record.
  • The second field replaced the first: Drag the field into Values again rather than changing the existing field’s summary. Confirm that both field entries are visible in the Values area before setting the second one to Average.
  • There is no separate average column: Check whether Values is arranged in Rows rather than Columns, and whether another column field is nesting the measures. Move Values to Columns or adjust the report layout.
  • New source rows do not appear: Expand or update the PivotTable’s source range, or use an Excel table as the source, then refresh the PivotTable.

Also distinguish a value-field summary from a calculated field. Average summarizes the existing numeric values in a group. A calculated field defines a calculation using source fields, and its result may be summarized across records in ways that are not equivalent to averaging a ratio or other row-level calculation. If the intended statistic is a ratio of totals—for example, total profit divided by total sales—averaging individual row ratios can produce a different answer. Define the desired calculation before choosing a PivotTable feature.

Frequently Asked Questions

Can I show Sum and Average in the same PivotTable?
Yes. Add the same numeric field to Values twice, then set one copy to Sum and the other to Average. Excel displays both summaries for each group, subject to the current PivotTable layout.

How do I change a PivotTable from Sum to Average?
Right-click a value in the field, choose Summarize Values By > Average, or open Value Field Settings and select Average. This changes the selected value field’s summary; it does not automatically preserve a separate Sum column.

Does an average include zero values?
Yes. Numeric zeros count as values in the arithmetic mean. Blank cells are not counted as zero, so replacing a blank with zero can change the result.

Why does my PivotTable show Count instead of Average?
Excel may be treating the source entries as text, or the field may not be recognized as numeric. Check the source column for text-formatted numbers, symbols, or inconsistent entries, correct them, and refresh the PivotTable.

Can I add an average column without changing the source data?
Yes. For a standard PivotTable, the average is calculated from the existing source field; you do not need to add a separate average column to the source table. You may need a different measure for a weighted average or another custom statistic.

When a PivotTable average is the right choice

Use Average when you want the arithmetic mean of the numeric records in each PivotTable group, such as average order value by region or average hours by employee. It is especially useful beside a sum, count, or other summary because the measures answer different questions: a total shows overall volume, while an average describes a typical record. Before sharing the report, check the source’s units, missing values, and record granularity, and label the measure so readers know exactly what is being averaged.

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.