Create an Excel PivotTable for Monthly Average, Maximum, Minimum, and Median
Question details
Create a monthly report showing average, maximum, minimum, and median values from spreadsheet data.

- 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.
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.
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.
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.
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.
Drag the numeric value field to Values three times. Open Value Field Settings for each copy and select Average, Max, and Min respectively.
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.
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.

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. 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. 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. Set the summaries: Open the field settings for each value field and select Average, Max, and Min as the respective calculations.
- 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.

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.




