Month summary pivot (2007)

[Egg 'n' Bacon]

Hi I'm trying to create a pivot table that will summarise data into months, from date fields and have totally forgotten how to use this tool.

[HansV]

Your source data should be in table format, i.e. with column headings (field names) in the first row, and with records (data) in the rows below.

Click anywhere in the source data.
Activate the Insert tab of the ribbon.
Click PivotTable.
You'll see the PivotTable Field List pane on the right hand side.
Drag fields to the Row Labels, Column Labels and Values areas as needed.
Your pivot table will be populated automatically.
Click on any of the dates in the pivot table.
Activate the Options tab of the ribbon (under PivotTable Tools).
Click the Options button.
Select Group Selection.
In the dropdown labeled By, select Months and Years.
Click OK.

[Egg 'n' Bacon]

Not sure why, but selecting the Group option tells me I cannot do this :(

I have attached a stripped down version

ForEileen.xls

[HansV]

That's because your source range includes blank rows. If you limit the source range to Sheet1!$B$2:$J$54, you'll be able to group the dates.

[Egg 'n' Bacon]

Aha!

Thank you.

When new records are added, can the pivot automatically include this on refresh (Offset?)?

[HansV]

You could create a dynamic named range SourceData (Formulas tab of the ribbon, Name Manager) that refers to

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),9)

Set the data source of the pivot table to SourceData.

(This assumes that column B doesn't contain values that don't belong to the pivot table)

[Egg 'n' Bacon]

I think I'm nearly there, but I must be making another mistake with the Offset as the pivot is not accepting the formula. Get the error; "Data source reference is not valid".

I have tried the formula separately, but this also is weird as it's showing relative results as opposed to absolute???

ForEileen 3.xls

[HansV]

In this case, the definition should be

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,15)

The -1 is used to exclude A1 from the count. See the attached version.

ForEileen 3.xls

[Egg 'n' Bacon]

OK, figured the relative/absolute; I hadn't held the ctrl AND the shift key on ENTER

[Egg 'n' Bacon]

Got it.

Cheers Hans