I have a Google Sheet which contains various metrics for each day. Each day has it's own row:

day        | cost | impressions | clicks | conversions
2014-02-01 | $45  | 3,540       | 80     | 5
2014-02-02 | $49  | 3,700       | 88     | 7
2014-02-03 | $42  | 3,440       | 79     | 7
2014-02-04 | $51  | 3,940       | 91     | 10

I want to pivot the table and summarize the cost, impressions, clicks and conversion columns by month (not by day) like this:

Month        | Sum of cost | Sum of impressions | Sum of clicks | Sum of conversions
February     | $187        | 14620              | 338           | 29

In Excel I just used the Group by function, but I see it's not available in Google Sheets.


8 Answers 8


Using the Data->Pivot table report... offers exactly what you are asking for. It might be possible that you have to use the "New Google Sheets".
I use that as a default setting and it was easy to achieve what you wanted, similar as in Excel.

Here in my answer I explain how to enable the new spreadsheets.

It seems I did not completly understand the problem.

First we need to have the month. For that add a new column to extract the date using =MONTH(DATE_COLUMN).

enter image description here

Then create a Pivot report:

enter image description here

  • tried the 'new google sheets' and there's no option to group dates by month in the pivot table reports, maybe you could shed some light?
    – Bogdan
    Mar 10, 2014 at 22:06
  • Sorry, I thought the main problem was that you couldn't find grouping at all. Then why not adding a column that provides the month only and group by it?
    – StampedeXV
    Mar 11, 2014 at 7:24
  • Unfortunately I can't add in another column, or change the formatting of the source sheet as it's being used to power a dashboard in Cyfe, but thanks for your answers...
    – Bogdan
    Mar 11, 2014 at 22:23
  • You can add the column, then hide it so it is invisible. Still you can use it in the Pivot report.
    – StampedeXV
    Mar 12, 2014 at 7:15
  • Excellent idea. I tried it, however Cyfe doesn't seem to be able to ignore the hidden columns. I'm just going to have to maintain 2 duplicate sheets with the source data to work around this....
    – Bogdan
    Mar 12, 2014 at 11:46

Google Sheets now supports this as 'Create pivot date group'

In the Pivot table, once you've added your date/time column as rows:

  1. Right-click on one of the values in the Pivot table,
  2. Choose 'Create pivot date group'
  3. Choose the desired grouping (e.g., 'Month' or 'Year-Month')

Before + Visual Example:

Pivot table before grouping


enter image description here

More Info

  • 3
    this should be the right answer probably
    – senseiwu
    Oct 5, 2018 at 7:23
  • 1
    This is the best answer by far as of 2018
    – cal
    Oct 17, 2018 at 23:31
  • Can I use my own formulae with Pivot tables somehow? I want to get quantiles
    – Hugo G
    Jul 9, 2019 at 2:08
  • 4
    I don't see this option in my date column
    – Ian Hyzy
    Sep 3, 2019 at 15:49
  • 4
    @IanHyzy I was also confused by this — couldn't find the option. Then realized it's after you create the pivot table and you right-click on any of the dates in the pivot table (not on the sheet that has your original data), hope that helps!
    – paulvs
    Jul 4, 2020 at 21:15

Without a helper column:

=query(A:E,"select sum(B), sum(C), sum(D), sum (E) group by month(A) offset 1")  

assuming day is in A1.

Documentation that does a much better job of describing how the above works than ever I could.

  • 1
    This sounds really useful. Could you explain how it works? I've never used the Query function. May 10, 2018 at 20:10
  • Is it possible to group by month AND year?
    – Hugo G
    Jul 9, 2019 at 2:08
  • @HubertGrzeskowiak Yes, it is
    – Lee Taylor
    Jul 21, 2019 at 15:18
  • @HubertGrzeskowiak Try =query(A:C,"select avg(B), avg(C) group by DAY(A), MONTH(A), YEAR(A)") Nov 16, 2022 at 14:31

So for those who asked similar question as me, There is apparently no Group field function for Pivot in Google Sheet (as it there is in Excel) yet. Hence the simplest way is to add another column transforming your dates to the groups you need, e.g. weeks, months, years... Hopefully the functionality is soon in Google sheet too.


An easier way that I found was to convert the date cell to text in a new column.

=Text(A1, "mmm-yy")

You can then pivot and it will group by month.


I found a way I like better. I simply created a hidden column with the following steps:

  • Round every day of the month to the first day of the month.

=A2 - day(A2) + 1 where A2 is the standard date

  • Set the formatting to custom and choose month / year. This will require you to delete the day setting. So, it will display month / year when the actual value will be first day of month, month, year.
  • The pivot table will now group all similar items together and display formatting from the column with month / year.

This way you'll get unique values as the years pass by. Myself I decided to hide the column in question because I have a Doc connected to this Sheet that pulls the first two rows as I add them.

Formula for date and formatting

Custom Date Format

Pivot Table Results


None of the previous answers worked for me, because I use a form as input. You can't add formulas on the form-sheet.

Here is my work-around.

Reading the above, the Pivot table needs a column with month to select on. You can use a different criteria, like week or fridays. Or John, Doe, Foo or bar. Whatever you want to 'group'on.

In my case: date is in B, =month(B2) gives the month. So I needed an extra column with =month(B2).

Form-sheet doesn't allow formulas. And on a sheet with a form, you can't add a column with formulas (exactly: you can, but a new form-entry deletes the formula on the corresponding line).

Second sheets

A simple second sheet with =page1!A1 etc and adding the column month has the same problem. A new entry in the form => adds a row.


So I did the following.

  • Added an extra sheet
  • Used =importrange("longkeynumber";"sheet form!A1:E1000") on A1
  • (importrange is to import a different document, but hey … it works)
  • In F, used =if(B3>0;(MONTH(B3));"")
  • When adding extra data in sheet A, formulas in sheet B with the importrange stays.
  • Now you can add a pivot table, with (in this case) F as a row or column.
  • In user165410's answer, he states: - In F, used =if(B3>0;(MONTH(B3));"") This is a good solution, but it can be made even easier: - in F3, =arrayformula(if(b3:b>0;(month(b3:b));"")) What this does, is automatically put the month for every non-empty cell in the range B3:B. Inserting or deleting a row, or moving a row will NOT screw up the formulas! Feb 10, 2016 at 13:58
  • When a new responses is added to a spreadsheet, a new row is inserted. Array formulas (multiple values result) could be used instead or scalar formulas (single values results). By the other hand, when the source data comes from the same file you could use array formulas to return the same result as IMPORTRANGE and they updated immediately Nov 3, 2016 at 16:40
  • I love this solution! in F3, =arrayformula(if(b3:b>0;(month(b3:b));"")) Only problem is when the data is for more than one year and each month gets combined with the same month in the previous year.
    – Vic M
    Mar 29, 2019 at 21:29

If you're already adding a new column, you can also just use eomonth("reference cell",0). That'll always grab the last day of the month. Or eomonth("reference cell",-1)+1 to get the beginning of the month.

The beauty with this approach instead of converting to text is that when you pivot the data. It'll automatically sort ascending left to right.

Not the answer you're looking for? Browse other questions tagged or ask your own question.