Exceljet

Quick, clean, and to the point

Pivot table sum by month

To build a pivot table to summarize data by month, you can use the date grouping feature. In the example shown, the pivot table is uses the Date field to automatically group sales data by month.

Pivot Table Fields

In the pivot table shown, there are three fields, Name, Date, and Sales.  Name is a Row field, Date is a Column field grouped by month, and Sales is a Value field with the Accounting number format applied.

pivot table sum by month field configuration

The Date field is grouped by Month, by right-clicking on a date value and selecting "Group".

Pivot table sum by month date grouping

Steps

  1. Create a pivot table
  2. Add fields to Row, Column, and Value areas
  3. Right-click a Date field value and set "Group" setting as needed

Notes

  1. Number formatting doesn't work on grouped dates because they behave like text.
  2. As a workaround, add helper column(s) to source data for Year and Month, then use those fields for grouping instead.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.