Columns by Month in Pivot Table

[rjbinney]

Dear Mr. Excel:

If this is answered elsewhere, I can't find it (I keep finding the same answers to different questions...!)

I have a long table of financial transactions:

• Date
• Account
• SubAccount
• Amount

I'd like to build a table - similar to an Income Statement - where the SubAccounts are totalled by month.

I'd like the SubAccounts in Rows and each Column is one month.

So my Pivot Table is:

The layout is great, but no matter what I do, I can't figure out how to consolidate the Date column into months.

(I have confirmed that the underlying data column is formatted correctly (ISNUMBER)).

All my Googling indicates "Group" is the answer - but it seems like I have to go and manually assign each bundle of dates into its appropriate group? That's five years...

I'm sure it's something easy?

 

[arthurbr]

There is probably something preventing from grouping your dates into months. An empty cell perhaps?

 

[Alex Blakenburg]

Can you confirm that you followed these steps:
When you create the pivot table per your screen shot you should get a heading row with your dates
• Select one of the dates
• Right Click and Select "Group"
• In the dialogue box make sure the Month is selected and hit OK
If you can't do that tell us whate error message you get or show us what you see.
If you get "cannot group that selection" then you have text in your date column (is the date column a formula ?)

 

[rjbinney]

Thanks. Had not thought of empty cells. But:

• As mentioned in OP, EVERY cell in the column returns "TRUE" to "ISNUMBER()"
  • Confirmed by COUNTIF(Range, TRUE) = the number of rows and COUNTIF(Range, FALSE) = 0
• Also, COUNTBLANK(Date Column) = 0

When I right-click a date, I get "cannot group that selection." Confirming again, no text, no formulas in the date data.

I DO have plenty of date-management options in the "Column Filters" dialog, but nothing about grouping.

Appreciate any new info / help!

Thanks,

 

[Alex Blakenburg]

Can you go to the original data table.
Enable filtering if you haven't already.
Click on the filter drop down of the date field (that is used in the pivot).
If it all fits on one screen show us what you see in the drop down.
If its a big list then scroll to the bottom of the list and show us what is there.
(The top of the list is another spot that may show anomalies)

 

[rjbinney]

Confirmed that the PivotTable ends with the last row of "legit" data...

 

[rjbinney]

Bump... ?

 

[felixstraube]

You mean something like this?:

 

[rjbinney]

Yes. But my underlying data (Column A in your example) is specific dates. I'd like the Pivot to summarize by month.

 

[Alex Blakenburg]

Confirmed that the PivotTable ends with the last row of "legit" data...

Thanks for that, that helps a lot. Sorry I missed the notification that you had responded.

None of your Pivot screen shots show the field list.
Have you by any chance loaded the data to the data model and are using a Power Pivot pivot ?
(your field list would show a Table symbol on the field parent node if you have)
Data Model pivots typically don't allow Grouping function to work.