Pivot Table Issues

Create an Excel PivotTable for Monthly Average, Maximum, Minimum, and Median

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Create a monthly report showing average, maximum, minimum, and median values from spreadsheet data.

Create an Excel PivotTable for Monthly Average, Maximum, Minimum, and Median
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to summarize dated numeric data by month in a PivotTable-style report.
Observed behavior
The report should show four statistics for each month, although a standard PivotTable does not offer median as a value summary.
Before you start

Check that the source data has column headers, valid Excel dates, and numeric values. For the example formula below, dates are in A2:A100 and values are in B2:B100.

Solution 1Recommended

Build a monthly PivotTable and calculate median alongside it

Use a PivotTable for average, maximum, and minimum, then add a separate median formula for each month.

1
Add a month column

In a new source-data column named Month, enter =DATE(YEAR(A2),MONTH(A2),1) in the first data row and fill it down. Format the results as dates so each month and year is identifiable.

2
Create the PivotTable

Select the source data, including the Month column, and choose Insert > PivotTable. Choose a destination and confirm. In the PivotTable Fields pane, drag Month to Rows.

3
Add the three summaries

Drag the numeric value field to Values three times. Open Value Field Settings for each copy and select Average, Max, and Min respectively.

4
Calculate the median

In a column outside the PivotTable, enter =IFERROR(MEDIAN(IF(($A$2:$A$100>=E2)*($A$2:$A$100<EDATE(E2,1))*ISNUMBER($B$2:$B$100),$B$2:$B$100)),"") for the first month. Replace E2 with the cell containing that row's month date, and adjust the source ranges if needed. In older Excel versions, confirm the formula with Ctrl+Shift+Enter; then fill it down beside the monthly rows.

5
Refresh and check the report

After changing source data, right-click the PivotTable and select Refresh. Check that each median formula still refers to the month on its row, and extend the formula ranges if the source data has grown.

Build a monthly PivotTable and calculate median alongside it
Median is calculated separately: Median is not a standard PivotTable value summary. The example formula returns a blank if there are no numeric values for the month. Keep the formula column outside the PivotTable and check it after the PivotTable expands or changes order.
Monthly spreadsheet reporting

Create a monthly statistics report in WPS Spreadsheet

Use WPS Spreadsheet to build a monthly PivotTable for average, maximum, and minimum, then calculate median in a separate column.

  1. 1. Prepare the data: Open the workbook in WPS Spreadsheet. Add a Month column using =DATE(YEAR(A2),MONTH(A2),1) and fill it down for all source rows.
  2. 2. Insert a PivotTable: Select the source range and choose Insert > PivotTable. Place Month in the Rows area and add the numeric field to Values three times.
  3. 3. Set the summaries: Open the field settings for each value field and select Average, Max, and Min as the respective calculations.
  4. 4. Add the median formula: Outside the PivotTable, enter =IFERROR(MEDIAN(IF(($A$2:$A$100>=E2)*($A$2:$A$100<EDATE(E2,1))*ISNUMBER($B$2:$B$100),$B$2:$B$100)),""). Adjust the ranges and E2 reference to match your sheet, confirm it as an array formula with Ctrl+Shift+Enter, and fill it down beside the monthly rows.
Summarize monthly values with a PivotTableCalculate median alongside the PivotTableOpen and edit Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel PivotTable calculate median directly?

Median is not available among the standard PivotTable value summaries. Calculate it separately with a formula beside the PivotTable, or use another suitable reporting method.

How do I keep the same month in different years separate?

Create a Month helper column with =DATE(YEAR(A2),MONTH(A2),1) and use it in the PivotTable Rows area. Format the dates to show both month and year.

Why is my median formula blank?

The example formula uses IFERROR to return a blank when it cannot calculate a median. Check that the month cell contains a date, the source ranges cover the data, and the month has numeric values.

Will the median column update when I refresh the PivotTable?

The formula recalculates from its referenced source cells, but it is outside the PivotTable. After selecting Refresh, check its month references and fill it down for any new monthly rows.