Editorial team
AccountingWEB.co.uk
Share this content
0
4
40080

Excel pivot table problem - can't Group dates

i am currently using my own data to try out the the tutorial provided by excelzone in accountingweb.co.uk - 'Five minute's pivot table tip'.

i am trying to sort out the invoice listing into detailed ageing format. hence, for row - name, invoice number, column - date(dd/mmmm/yyyy), date - amount in GDP & USD.

so i try to change the date to month by following the tips given in excelzone. but message pop up and said 'cannot group that selection'.

i dont know where have i gone wrong. can anyone here please pinpoint out to me so that i can rectify it? thanks a lot
Ken Wen

Replies

Please login or register to join the discussion.

avatar
By mingali
25th Oct 2006 04:25

Thanks David

However, i am still unable to sort out the problem with Tips 18. i have a 'blank' column and row showing the sum amount. i tried to delete that column and row without success.

what shall i do now?

Thanks again

Thanks (0)
avatar
23rd Oct 2006 18:56

see Tip no 18
Ken,
You get this message when at least one of the fields you have selected is not a true date.

Tip 18 in the series deals with it more fully. Possible reasons:

- the data range you have highlighted contains a blank row or field. Since there's no date here, Excel can't group.

- the data range seems to contain dates, but they just look like dates. To find out, either sort the Date column and look at the top or bottom for any funnies.

Alternatively, highlight the column and format it as Number. True dates will convert to a number around 38000. False dates will continue to look like dates.

Thanks (0)
avatar
By mingali
28th Oct 2006 13:38

thank David
it works now.

Thanks (0)
avatar
25th Oct 2006 11:48

you have to sort out the Data range in Step 2
Ken,
The "blank" column definitely proves that Excel is looking at a blank row or column in the source data.

The blank column in the pivot table is just a symptom of the problem so deleting it won't change anything. You have to modify the data range that Excel is looking at in Step 2 of the wizard. To do this (exact wording depends on your version of Excel):

Right click anywhere on the pivot table - pivot table menu appears - choose Pivot Table Wizard - Step 3 of wizard appears - choose Back - Data range box (Step 2) appears.

The data range in the box needs to be adjusted. If you look at the broken lines moving around the block of data, you will see that they are enclosing a blank row or column somewhere. That's what's causing the Group function to fail because there's no date there.

So you need to go ahead and correct the data range so that the broken lines surround data and not blanks. Then Finish - right click for menu again -Refresh Data.

Having said all this, sometimes Group still doesn't work even after you have corrected the data range! In that case, you will just have to delete the pivot table and start all over again. But at least when you get to Step 2 you will make now sure that you select the data range correctly. David

Thanks (0)