Problem Grouping Pivot Table Items

Problem Grouping Pivot Table Items

If you try to group pivot table items in Excel, you might get an error message that says, “Cannot group that selection.” For older versions of Excel, if you had a problem grouping pivot table items, it was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there’s another thing that can prevent you from grouping — the Excel Data Model.

Cannot Group That Selection

Here’s a screen shot of the “Cannot group that selection.” error message that appears. The message doesn’t give you any clues as to why you can’t group the items. It’s up to you to do the detective work.

Data Model

We’ll look at the traditional reasons for this grouping problem in the next section. But first, here’s the newer issue, that might affect you, if you’re using Excel 2013 or later.

  • When you create a pivot table, there’s a check box to “Add this data to the Data Model”.
  • If you checked that box, you won’t be able to group any items in the pivot table.

When the source data is added to the data model,  you end up with an OLAP-based Power Pivot, instead of a traditional pivot table, and the grouping feature is not available.

Is It OLAP-Based?

A quick way to tell if your pivot table is OLAP-based is to check the Ribbon:

Select any cell in the pivot table

  • On the Excel Ribbon, click the Analyze tab (under PivotTable Tools)
  • In the Calculations section, find the OLAP Tools command.
    • If it’s dimmed out, your pivot table is the traditional type
    • If the command is active, your pivot table is OLAP-based

olap tools on Analyze tab

And if you check the Fields, Items, & Sets drop down, some of the features will be dimmed out, for OLAP-based pivot tables.  For example, you can’t create a calculated field or calculated item.

pivotgroupolap02

Fix the Grouping Problem

I haven’t found any way to change the pivot cache from an OLAP-based source (data model), to a data source that isn’t in the data model. So, if you need grouping, create a new pivot table from the source data, and do NOT check the box to add the data to the Data Model.

NOTE: You can keep the OLAP-based pivot table too, and have two pivot tables based on the same data, using different pivot caches.

Blank Cells or Text

If your pivot table is the traditional type (not in the data model), grouping problems are usually caused by invalid data in the field that you’re trying to group.

If you’re trying to group dates or numbers, the grouping problem usually occurs when the field contains records with one of these items:

  • a blank cell in a date/number field, or
  • a text entry in a date/number field.

To fix the problem

  • For blank cells, fill in the date/number (use a dummy date/number if necessary).
  • If there is text in the date/number field, remove it.
  • If numbers are being recognized as text, use one of the techniques to change text to real numbers.

Then refresh the pivot table, and try grouping the items again.

Previous Grouping

If you don’t have blank cells or text in the date column, there may be a grouped field left over from a previous time that you grouped the data.

  1. Check the field list, to see if there’s a second copy of the date field, e.g. Date2.
  2. If there is, add it to the row area, and ungroup it.
  3. Then, you should be able to group the date field again.

Videos: Pivot Table Grouping

To learn more about pivot table grouping, and get a workbook with sample file that you can use for testing, go to the How to Group Pivot Table Data page on my Contextures website.

This short video shows the basics of pivot table grouping

This video shows how to group Text items in a pivot table.

More Pivot Table Links

Clear Old Items in Pivot Table

Calculated Field – Count

Calculated Items

Summary Functions

_____________________________

Problem Grouping Pivot Table Items http://blog.contextures.com/

_____________________________

18 thoughts on “Problem Grouping Pivot Table Items”

  1. THANK YOU THANK YOU THANK YOU!

    I have been looking all over for the solution to this problem. You are a life-saver (or at least a sanity saver).

    1. I’m a bit late to the party, but your problem might be that the titles were not formatted as a date/number. In my case, the data was formatted as dates, but the title format was “General”, and it wasn’t working. When I changed the title format to “Date”, it worked. Hope that helps.

      1. I just tried formatting the title and dates to dates and it’s still not working. I tried the other suggestions in the community, including making sure the “Choose whether you want to analyze mulitple tables” is turned off and nothing is working. I have other pivot tables with dates that automatically including the month summary and I don’t know why this new pivot is not doing that.

        Any help would be greatly appreciated. I’m going crazy.

  2. Yes, this is helpful.

    I actually do not have OLAP active and grouping still wasn’t appearing for me, but creating a new pivot did actually work.

  3. Thank you so very much!
    I’ve been strugling with this until I found your blog entry!
    I suggest you leave a Link in your main website to this blog entry!

    1. This solved my problem! But I don’t understand why. I had a Pivot Table that only grouped by month but didn’t by year. I tried everything, formatting, settings, create a new pivot table, check every date and nothing worked until I tried this. I just want to understand why. Thanks for the tip.

  4. Why does automatic grouping of dates work using normal pivot tables, BUT when creating a pivot from the power pivot tool using the exact same data set I cannot group them automatically? It should work in excel 2016, however, I’m using microsoft office 2019 professional plus – did they disable it for this version?

    Thanks a lot in advance.

  5. I have a big table and I filtered out data by name of doctors. When I copy this data to other excel and create pivot table, it worked for most doctors. However, exact same columns but for few doctors filtered, it did not work. I have tried creating new pivots, new temp excels, formatting title, formatting data. Nothing works. It is not grouping by date.

  6. I had a similar problem caused by a whole bunch of dates which were apparently secretly text values (despite the field being formatted as date)
    I confirmed it by entering an identical date in another field and then asking excel if A1 = B1. Despite being the same date, they did not come up as equal. So I made a new column, added a formula to multiply the value in the date column by 1, and then pasted the results over the original as values. It replaced the text dates with number dates. That resolved my issue.

  7. What got it to work for me is doing all of the above: formatting title as date format in my source data tab, adding a dummy date in my blank row in the pivot, making sure it wasn’t OLAP, and then the final fix was deleting the sum row from the bottom of my source data. Thank you!!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.