Jump to content


Photo

Grouping in PT's

Graying out?

  • Please log in to reply
3 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 23 January 2018 - 01:18 AM

I am trying to do some grouping in a traditional PT but every field I bring in tells me that that field cannot be used. What can be the cause of the grouping tool to be grayed out with all fields?

 

JPK1066



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 23 January 2018 - 02:34 PM

Hi JPK,

 

My guess is that you have inadvertently created an OLAP pivot table rather than a regular pivot table. OLAP pivot tables add additional functionality (such as being able to work with multiple tables), but aren't able to use the Group tool.

 

Excel will create an OLAP pivot table if you check the Add this data to the Data Model checkbox when creating your pivot table, so my guess is that you have done this.

 

If you recreate your pivot table with the checkbox unchecked, you should find that you're able to use grouping.

 

Pivot table grouping is covered in: Expert Skills Session 8: Pivot Tables.

OLAP pivot tables are covered in depth in: Expert Skills Session 9: Data Modeling, OLAP and Business Intelligence.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 23 January 2018 - 09:08 PM

I tried to do as you suggested but it continues to tell me that it can't group this selection?

This is just a small section of a hugh WS with over 100k rows but just one WS.

I created a PT and then chose         Batter    and then tried to use the grouping tool.....?

 

JPK1066

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 24 January 2018 - 01:40 PM

Hi JPK,

 

I've tried using grouping on your sample data and it seemed to work for me. Bear in mind that when grouping a text field such as the Batter field used in your example data, you must select multiple items before clicking PivotTable Tools > Group > Group Selection.

 

If you try to apply grouping to a text field with only a single cell selected, you will always see the "Cannot group that selection" error message. This differs from numeric fields, which will offer you the ability to select a grouping ranges.

 

You can see the process of applying grouping to text fields in more depth in: Expert Skills Lesson 5-19 Group by text.

Numeric grouping is covered in: Expert Skills Lesson 5-21 Group by numeric value ranges.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users