none
Pivot Table

    Question

  • Hi All i am pretty new to Excel, i have puleed some data through from an external Data Source the information is all of the Quotes our company has ever done.

    I Have a quote Date Column and a Quote number column. I have created a pivot table on this data and the table corectly shows the number of Quotes generated for each Day.

    What i would like to be able to do is Group the Days into Months if i right click on a cell and choose Group and show detail i get the error message Cannot Group that selection. There is a blank right at the bottom of the info how do i get rid of that?

     

    john

    Monday, October 18, 2010 3:22 PM

Answers

  • Hi John

    In order to Group dates in a Pivot Table, all of the data must be true Excel Dates with no Text entries.
    Blank cells are text entries, hence the message you are seeing.

    You need to have a Dynamic range for your source data, which only includes rows which are populated in the Date field.
    In XL2003 this can be achieved with the List object, Data>List>Create list> my data has headers, then base the PT on the list that is created.
    In XL2007 and 2010 there is a Table object, Insert tab>Table>my data has headers. In the Design tab that appears you will see the Table name (default Table1) which you can amend.
    Make this Table the source for your PT data.

    For all versions of Excel, you can create your own Dynamic named range.
    Insert>Name>Define>
    Name            myData
    Refers to        =$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

    The above assumes that your data starts with the first header in A1, and has up to 65536 rows (expand the row numbers if required for 2007 upward), and also assumes that the Dates are in column A
    Adjust the formula to suit your data layout.


    Regards
    Roger Govier

    "pepps1976" wrote in message news:9923e69f-f72c-45ea-93a0-8089bebba9b7@communitybridge.codeplex.com...

    Hi All i am pretty new to Excel, i have puleed some data through from an external Data Source the information is all of the Quotes our company has ever done.

    I Have a quote Date Column and a Quote number column. I have created a pivot table on this data and the table corectly shows the number of Quotes generated for each Day.

    What i would like to be able to do is Group the Days into Months if i right click on a cell and choose Group and show detail i get the error message Cannot Group that selection. There is a blank right at the bottom of the info how do i get rid of that?



    john


    ________ Information from ESET Smart Security, version of virus signature database 5542 (20101018) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com

    ________ Information from ESET Smart Security, version of virus signature database 5542 (20101018) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    • Marked as answer by pepps1976 Tuesday, October 19, 2010 8:19 AM
    Monday, October 18, 2010 3:55 PM

All replies

  • Hi John

    In order to Group dates in a Pivot Table, all of the data must be true Excel Dates with no Text entries.
    Blank cells are text entries, hence the message you are seeing.

    You need to have a Dynamic range for your source data, which only includes rows which are populated in the Date field.
    In XL2003 this can be achieved with the List object, Data>List>Create list> my data has headers, then base the PT on the list that is created.
    In XL2007 and 2010 there is a Table object, Insert tab>Table>my data has headers. In the Design tab that appears you will see the Table name (default Table1) which you can amend.
    Make this Table the source for your PT data.

    For all versions of Excel, you can create your own Dynamic named range.
    Insert>Name>Define>
    Name            myData
    Refers to        =$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

    The above assumes that your data starts with the first header in A1, and has up to 65536 rows (expand the row numbers if required for 2007 upward), and also assumes that the Dates are in column A
    Adjust the formula to suit your data layout.


    Regards
    Roger Govier

    "pepps1976" wrote in message news:9923e69f-f72c-45ea-93a0-8089bebba9b7@communitybridge.codeplex.com...

    Hi All i am pretty new to Excel, i have puleed some data through from an external Data Source the information is all of the Quotes our company has ever done.

    I Have a quote Date Column and a Quote number column. I have created a pivot table on this data and the table corectly shows the number of Quotes generated for each Day.

    What i would like to be able to do is Group the Days into Months if i right click on a cell and choose Group and show detail i get the error message Cannot Group that selection. There is a blank right at the bottom of the info how do i get rid of that?



    john


    ________ Information from ESET Smart Security, version of virus signature database 5542 (20101018) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com

    ________ Information from ESET Smart Security, version of virus signature database 5542 (20101018) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    • Marked as answer by pepps1976 Tuesday, October 19, 2010 8:19 AM
    Monday, October 18, 2010 3:55 PM
  • Hi,

    If the blank cell(s) are not at the bottom of the data, then you will need to remove that lines. 

    If the blank lines are at the bottom then follow Roger's suggestion.  I favor using List or Table over dynamic range names in this case.


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
    Monday, October 18, 2010 6:23 PM