Pivot Table Total/Subtotals
 

Hi all

I have data with the following structure (column headings):

Category Brand Group M1 M2 M3... M12

where M1 is month 1, M2 is month 2, etc.

I would like a pivot table that I can manipulate in different ways to
show totals by month, quarter and year. Question is, can I keep the
months in columns to do this, or do I need a different row for each
month?

Thanks in advance

Paul Martin
Melbourne, Australia

Hi,

Keep months in column or row and then use the Group feature. To do this,
right click on the Pivot table and select Group and choose your grouping
options there.

When you use grouping, there shouldn't be any blank rows in the Pivot's
range.

Regards

Govind.

wrote:
Hi all

I have data with the following structure (column headings):

Category Brand Group M1 M2 M3... M12

where M1 is month 1, M2 is month 2, etc.

I would like a pivot table that I can manipulate in different ways to
show totals by month, quarter and year. Question is, can I keep the
months in columns to do this, or do I need a different row for each
month?

Thanks in advance

Paul Martin
Melbourne, Australia

Thanks Govind.

I found part of my solution by placing the months as data fields and
then dragging the Data grey button onto the Totals label. I tried your
suggestion, but got a message "Cannot group that selection". I have
replaced any blank data rows with zeroes, but still same message.
What's the problem here?

Thanks in advance

Paul Martin
Melbourne, Australia

Hi,

What is the format in which you have typed your months? The grouping is
possible only if you type your months in any valid date format. For eg.
Aug-05. However, if you are using say 1 for Jan'05 and so on, then
grouping wont be possible in such a case.

Regards

Govind.

wrote:
Thanks Govind.

I found part of my solution by placing the months as data fields and
then dragging the Data grey button onto the Totals label. I tried your
suggestion, but got a message "Cannot group that selection". I have
replaced any blank data rows with zeroes, but still same message.
What's the problem here?

Thanks in advance

Paul Martin
Melbourne, Australia

The column headings are simple labels: M1, M2 to M12, so this shouldn't
be an issue.
The data in each row/column are simple numeric, so again, I can't see
that this should be an issue.

Paul Martin
Melbourne, Australia