Cannot group that selection

[ColinEssex]

Hi All,

My apologies if this is simple to answer but I'm puzzled.

I have 419 rows of data in Excel and am working on a pivot table. Each record has an "Incident Date".

In the pivot table I would like the date to be shown as "April" "May" June" etc, I have done this before by selecting "Group and Outline" then selecting "Group" (this is using the Incident Date column in the pivot table)

Now, I get the message "cannot group on that selection" when I click on the date header (in the pivot table) and try to group.

I have searched but can't find any reference or answer.

Thanks in advance for any help, its odd it has done it ok in other pivot tables.

Thanks

Col

 

[The Stoat]

Hi Colin,

The usual problem is the date format for the column is incorrect for a 1 or 2cells and that prevents it from grouping. 99 times our 100 you'll never be able to find the cell that's wrong. The simplest solution is to format a blank cell in the format you want then copy the cell. Then highlight your column and use the paste special>paste format only function. This should fix the problem with the cells. You can then try and refresh the pivot and try the groupings again, but i often find you need to rebuild the pivot table from scratch.

 

[ColinEssex]

Thanks Stoat, I re-downloaded the data and imported it into Excel again, this time the grouping is working ok on the date field.

Col

 

[The Stoat]

No worries.

 

[ColinEssex]

Another quick question. . . . . .

Is it possible to group the dates into quarters where Quarter1 starts Apr to June and Quarter2 is July to Sept etc?

When you use the default quarter grouping, it does Quarter1 Jan to Mar and Quarter2 Apr to June. I know thats correct for the calendar year but I need it done on the Financial year.

Thanks

Col

 

[The Stoat]

Hi,

The easiest way is to rename the groups. click on the cell with the group name i.e. Qtr1 and it should appear in the forumula bar where you can then change it.

Start with Qtr1 and call it Qtr - to avoid a clash as this will become Qtr4 - then rename Qtr2 as Qtr1 : Qtr3 as Qtr2 : Qtr4 as Qtr3 and then go back to Qtr and call that Qtr4.


HTH