Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Pivot Table: “Cannot Group That Selection” error

This is a discussion on Pivot Table: “Cannot Group That Selection” error within the Excel Questions forums, part of the Question Forums category; I have a data entry sheet with a column titled “Start Date”. This column contains only dates. I have a ...

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Posts
    55

    Default Pivot Table: “Cannot Group That Selection” error

    I have a data entry sheet with a column titled “Start Date”. This column contains only dates.

    I have a Pivot Table based on the data entry sheet. In the Pivot Table “Start Date” is a Column Field. Start Date is also used as the data field (Count of Start Date).

    I clicked on the “Start Date” In the Column Filed area and selected Grouping by Month & Year.

    While I was developing the spreadsheet, I used some arbitrary dates in the Start Date column, and the grouping worked fine. Then I brought in a column of dates from the ‘real’ data entry sheet, and now the grouping is not functional (“Cannot Group That Selection” error).

    Can you tell me some possible causes of this error?

    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default Re: Pivot Table: “Cannot Group That Selection” error

    Make sure that

    a) there are no empty spaces in the data
    b) all the dates are real dates (You can check that by changing the format in the database, and if there are some dates that don't change, they need to be "converted" to numbers again)
    c) try creating the pivot table again.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2004
    Posts
    55

    Default Re: Pivot Table: “Cannot Group That Selection” error

    Thanks, will empty spaces cause the error?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default Re: Pivot Table: “Cannot Group That Selection” error

    Yes
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Feb 2004
    Posts
    55

    Default Re: Pivot Table: “Cannot Group That Selection” error

    Is there a way to get the Pivot Table to ignore the empty spaces?

  6. #6
    Board Regular jillian728's Avatar
    Join Date
    Jun 2003
    Location
    San Diego
    Posts
    243

    Default Re: Pivot Table: “Cannot Group That Selection” error

    I had the same problem with the "Cannot Group That Selection" error. I did not have any empty spaces and all of my dates were formatted properly.

    However, the date field I was trying to group was located in the Page Field section (top left corner filters). Once I moved the field into the main body of the pivot table, I was able to group the dates. This worked for me!

  7. #7
    New Member
    Join Date
    Jan 2008
    Posts
    4

    Default Re: Pivot Table: “Cannot Group That Selection” error

    This cannot group problem just stops us in our tracks. It blocks us from benefitting from the features of pivot tables. Are there quick ways to implement the solutions and data-checking such as checking for blank cells in a 13,000 row database?

  8. #8
    New Member
    Join Date
    Dec 2011
    Posts
    1

    Default Re: Pivot Table: “Cannot Group That Selection” error

    If date formatting or blank cells are not the reason you cannot group on a date column, save the excel file as a text file and reopen. After reformatting the date column you should be able to group on that column.

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Posts
    112

    Default Re: Pivot Table: “Cannot Group That Selection” error

    You can try first creating a pivot table excluding the empty range (only to the extend of range that data is available) and now you should be able to group the date into month or years. Once this is done, you can go back to options, change data source and now you can extend your data range over the empty cells.

    Though its im too late ,,,,,,Hope this would work .......

    Regards

  10. #10
    New Member
    Join Date
    Dec 2013
    Posts
    1

    Default Re: Pivot Table: “Cannot Group That Selection” error

    Quote Originally Posted by Juan Pablo González View Post
    Make sure that

    a) there are no empty spaces in the data
    b) all the dates are real dates (You can check that by changing the format in the database, and if there are some dates that don't change, they need to be "converted" to numbers again)
    c) try creating the pivot table again.
    This is the reason I come to this site. For the past 3 months a pivot table that generates a report has been screwing up in an automated spreadsheet. Backup copies of the file worked, but every time I went to the most recent, or copied the data from the most recent to the backup, the table messed up.
    I went to the data and found where someone had messed up the format of one date, fixed that, and the table now works, my reports now come out properly.
    Thanks

    Paul

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com