| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Query on Group field in Pivot Table
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Query on Group field in Pivot Table
Hi Shewta
Check you Data Source of your Pivot. it should come up like "Sheet6!$G$1:$I$821" and not like "'\Documents and Settings\kali\My 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 "Shweta Srivastava" wrote: 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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Query on Group field in Pivot Table
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 InsertNameDefine Name myData Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A),INDEX($1:$1,COUNTA ($1:$1)) Right click on the PTBackSource =myDataOK -- Regards Roger Govier "Shweta Srivastava" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Query on Group field in Pivot Table
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 doesn't 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 "Roger Govier" wrote: 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 InsertNameDefine Name myData Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A),INDEX($1:$1,COUNTA ($1:$1)) Right click on the PTBackSource =myDataOK -- Regards Roger Govier "Shweta Srivastava" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Query on Group field in Pivot Table
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 dataInsert tabTableclick 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 PTright clickon the Options tabData sectionChange data sourcein the Table/Range enter SalesData -- Regards Roger Govier "Shweta Srivastava" wrote in message ... 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 doesn't 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 "Roger Govier" wrote: 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 InsertNameDefine Name myData Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A),INDEX($1:$1,COUNTA ($1:$1)) Right click on the PTBackSource =myDataOK -- Regards Roger Govier "Shweta Srivastava" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4774 (20100115) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________ The message was checked by ESET Smart Security. http://www.eset.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 Pivot Table - Group Field function is not always availa | Excel Worksheet Functions | |||
| How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
| Group Field in Pivot Table €“ Not Enough Room | Excel Discussion (Misc queries) | |||
| Pivot Table Data Field Query | Excel Worksheet Functions | |||
| Pivot table will not group a date field | Excel Discussion (Misc queries) | |||
![Shweta Srivastava[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
