For example, you can’t create a calculated field or calculated item. If the command is active, your pivot table is OLAP-basedĪnd if you check the Fields, Items, & Sets drop down, some of the features will be dimmed out, for OLAP-based pivot tables.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).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.Ī quick way to tell if your pivot table is OLAP-based is to check the Ribbon: 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 first, here’s the newer issue, that might affect you, if you’re using Excel 2013 or later. We’ll look at the traditional reasons for this grouping problem in the next section. The message doesn’t give you any clues as to why you can’t group the items. Here’s a screen shot of the “Cannot group that selection.” error message that appears. For Excel 2013 and later, there’s another thing that can prevent you from grouping - the Excel Data Model. If you ungroup a group of selected items, only the selected items will be ungrouped.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. Note that ungrouping a numeric or date and time field will remove all groups for that field. Select any cell in the grouped field and on the Pivot Table Tools | Analyze tab, in the Group group, click the Ungroup button. Right-click the grouped field and select Ungroup. To ungroup data in a pivot table, do one of the following. The resulting report is shown in the image below. To do that, right-click the group header and select the Subtotal 'Field Name' item. You can also enable or disable displaying the subtotal for the created group. To rename the group, select the group header, press F2 and type the required name. or on the PivotTable Tools | Analyze tab, in the Group group, click the Group Selection button.Īs a result, the selected range will be combined into a single group. Right-click the selected range and select the Group item from the context menu.
#Pivot table in mac numbers how to#
The following topic describes how to group dates, numbers or selected items in a pivot table. Grouping can help you get a clearer view of data and show only data you want to analyze.