Pivot Table Issues

Group Daily Temperatures by Month in an Excel PivotTable

Bushra ParveenBushra Parveen Nov 20, 2026 870 views

Question details

Calculate monthly temperature extremes by year from daily records whose dates are stored as YYYYMMDD text.

Group Daily Temperatures by Month in an Excel PivotTable
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains daily dates from 1833 through 2018 and corresponding maximum and minimum temperatures.
Observed behavior
The desired result is a table showing the highest daily maximum and lowest daily minimum for each year and month, including years before 1900.
Before you start

Check that each date is an eight-character YYYYMMDD value and that the temperature columns contain numbers. The steps below assume dates are in column A, daily maximums in B, and daily minimums in C, with headers in row 1.

Solution 1Recommended

Summarize temperatures with helper columns and a PivotTable

Use numeric year and month fields to group records without converting pre-1900 dates into Excel dates.

1
Extract the year

Enter Year in D1. In D2, enter =VALUE(LEFT(A2,4)), then fill the formula down through the last data row.

2
Extract the month

Enter Month in E1. In E2, enter =VALUE(MID(A2,5,2)), then fill the formula down. The resulting numbers 1 through 12 keep the months in calendar order.

3
Create the PivotTable

Select the source range, including all five headers, then choose Insert > PivotTable. Select where to place the PivotTable and confirm.

4
Arrange the fields

In the PivotTable Fields pane, drag Year and then Month to Rows. Drag the daily maximum and daily minimum fields to Values.

5
Set the calculations

Open Value Field Settings for the daily maximum field and choose Max. Open the same settings for the daily minimum field and choose Min. Each row now shows the highest daily maximum and lowest daily minimum for that year and month.

Summarize temperatures with helper columns and a PivotTable
Dates before 1900: The helper columns extract numbers from the date text, so the PivotTable does not need to interpret historical dates as Excel date values.
Summarize historical temperatures

Group monthly temperature extremes in WPS Spreadsheets

Use year and month helper columns and a PivotTable in WPS Spreadsheets to summarize daily temperature extremes, including records from before 1900.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the daily dates and temperatures.
  2. 2. Add grouping columns: With dates in column A, enter Year and Month as new headers. Use =VALUE(LEFT(A2,4)) for the year and =VALUE(MID(A2,5,2)) for the month, then fill both formulas down.
  3. 3. Insert a PivotTable: Select the data and helper columns, then choose Insert > PivotTable and select a location for the report.
  4. 4. Set up the summary: Place Year and Month in the row fields and add the daily maximum and minimum temperature fields to Values. Set their value calculations to Max and Min, respectively.
Group historical records by numeric year and month without converting them to datesCalculate monthly maximum and minimum temperatures in a PivotTableOpen and save Microsoft Excel XLSX workbooksWork with familiar spreadsheet formulas and controls
microsoft office alternative - wps office

Frequently Asked Questions

Why not group the original dates directly in the PivotTable?

The YYYYMMDD values are text, and the dataset includes dates before 1900. Extracting numeric year and month fields avoids relying on Excel to recognize those historical values as dates.

How do I show monthly averages instead of extremes?

In the PivotTable, open Value Field Settings for the relevant temperature field and select Average. This calculates the average of the daily values in each year and month group.

How do I update the PivotTable after adding records?

Make sure the PivotTable source includes the new rows, then right-click the PivotTable and select Refresh. If needed, use PivotTable Analyze > Change Data Source to expand the range first.

Can VBA calculate the same monthly results?

Yes. A VBA macro can read the source range into an array, group records by the year and month extracted from each date, calculate the extremes, and write the results to a worksheet in one operation.