Excel - Query on Group field in Pivot Table

Asked By Shweta Srivastava

14-Jan-10 11:42 PM

I have a large database wherein under Date column, data is in the format "Thu
Oct 1 12:00 AM".
When I use Pivot Table Under OPTIONS->GROUP-> Group Field is disabled.
I used this to group Date field to either present data by Month, quarter at
one click.
Can anybody guide me why this seems to be disabled here.
I want to present my data only by Monthwithout changing the format ""Thu Oct
1 12:00 AM".

Thanks

15-Jan-10 03:28 AM

Hi Shewta


Check you Data Source of your Pivot. it should come up like
Documents\[EEXXXEL.xlsx]Sheet6'!$G$1:$I$821"...if its the later you may need
to change the source or reapply the pivot function...

Hope it helps

15-Jan-10 08:30 AM

Hi

If you have any Text or any Blank cells in your column of dates, then Excel
will not permit Grouping.

Instead of selecting a fixed range larger than your existing data, allowing
room for adding new data, use a dynamic range as your source, which only
contains rows which have values.

For example
Insert>Name>Define>
Name>   myData
Refers to >  =$A$1:INDEX($A:$A,COUNTA($A:$A),INDEX($1:$1,COUNTA($1:$1))

Right click on the PT>Back>Source =myData>OK
--
Regards
Roger Govier


__________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________

The message was checked by ESET Smart Security.

http://www.eset.com

22-Jan-10 01:32 AM

Hi Roger,
Thanks for your message.

I am not able to understand the 2nd paragraph followed with the example.

When I click on Insert tab it does not give me any option of Name and then
Define.

I would appreciate if you can brief me about the same.

Regards,
Shweta Srivastava

--
Shweta Srivastava

23-Jan-10 04:48 AM

Hi Shweta

It sounds as though you are using XL2007.
I gave instructions for XL2003.

Instead for defining a Dynamic Range as per my earlier posting, make use of
the XL2007 Table feature.
Place your cursor in the first row of your data>Insert tab>Table>click my
table has headers.
When you do this, you will see the Design tab, and in the Top left corner
you will see the Table Name that has been allocated by Excel - typically
Table1.
You can change this name to something more meaningful for you e.g. SalesData

Now, on your PT>right click>on the Options tab>Data section>Change data
source>in the Table/Range enter SalesData

--
Regards
Roger Govier