Month summary pivot (2007)
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Month summary pivot (2007)
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.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Month summary pivot (2007)
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Month summary pivot (2007)
Not sure why, but selecting the Group option tells me I cannot do this :(
I have attached a stripped down version
I have attached a stripped down version
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Month summary pivot (2007)
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Month summary pivot (2007)
Aha!
Thank you.
When new records are added, can the pivot automatically include this on refresh (Offset?)?
Thank you.
When new records are added, can the pivot automatically include this on refresh (Offset?)?
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Month summary pivot (2007)
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)
=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)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Month summary pivot (2007)
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???
I have tried the formula separately, but this also is weird as it's showing relative results as opposed to absolute???
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Month summary pivot (2007)
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.
=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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Month summary pivot (2007)
OK, figured the relative/absolute; I hadn't held the ctrl AND the shift key on ENTER
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Month summary pivot (2007)
Got it.
Cheers Hans
Cheers Hans