NOTE: There’s no option or setting that lets you change the pivot table from an OLAP-based source (data model), to a data source that isn’t in the data model. Keep the OLAP-based pivot table too, and you’ll have two pivot tables based on the same data, using different pivot caches. Do NOT check the box to add the data to the Data Model.Create a second pivot table from the source data.If you want grouping, you’ll need a pivot table with its source data NOT added to the data model. Unfortunately, there’s no fix for grouping in an OLAP-based pivot table.
![pivot tables in excel 2013 cannot be used in 2010 pivot tables in excel 2013 cannot be used in 2010](https://www.excel-university.com/wp-content/uploads/20130516e.png)
Other features are unavailable too.įor example, you can’t create a calculated field or calculated item. It’s not just grouping that is prevented In OLAP-based pivot tables. If the command is active, your pivot table is OLAP-based.If it’s dimmed out, your pivot table is the traditional type.In the Calculations section, find the OLAP Tools command.On the Excel Ribbon, click the Analyze tab (under PivotTable Tools).In OLAP-based pivot tables, the grouping feature is not available.Ī quick way to tell if your pivot table is OLAP-based is to check the Ribbon: If you added the source data to the data model, you created an OLAP-based Power Pivot, instead of a traditional (normal) pivot table. If you checked that box, you won’t be able to group any items in the pivot table.When you create a pivot table, there’s a check box to “Add this data to the Data Model”.But if you’re using Excel 2013 or later, there’s a new reason for the error message, that might affect you.
![pivot tables in excel 2013 cannot be used in 2010 pivot tables in excel 2013 cannot be used in 2010](https://www.pivot-table.com/wp-content/uploads/2014/02/datasourcechange2013_01.png)
You can run into pivot table grouping problems in any version of Excel. Excel leaves it up to you to find out what the problem is. As you can see, the error message doesn’t tell you WHY you can’t group the items. Here’s a screen shot of the pivot table error, “Cannot group that selection.” that appears. If you’re using Excel 2013 or later, there’s another reason that might prevent you from grouping pivot table items. When you try to group dates in an Excel pivot table, or other pivot table items, you might get a pivot table error, “Cannot group that selection.” In Excel 2010, and earlier versions, that error was usually caused by blank cells in your source data, or text in the number or date columns.