The following alternative process allows you to manually group Items in 2 simple steps: Select the Items you want to group. If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." Here’s a screen shot of the pivot table error, “Cannot group that selection.” that appears. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. Using the Excel group function Functions List of the most important Excel functions for financial analysts. I recorded a macro and based on that wrote this code: Sub Macro2() Dim i As Double With ActiveSheet.Outline .AutomaticStyles = False .SummaryRow = xlAbove .SummaryColumn = xlLeft End With For i = 1 To 141163 If Range("B" & i).Value = 9 Then Rows("i:i").Select Selection.Rows.Group End If Next i End Sub By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy. So many things can cause this but it all comes down to the same thing – the underlying datatype is not what you think, i.e. How to extend lines to Bounding Box in QGIS? Question is, lets say, i have a total of 4 group box. What is the largest single file that can be loaded into a Commodore C128? Super User is a question and answer site for computer enthusiasts and power users. Did I make a mistake in being too honest in the PhD interview? You can now refresh the pivot and should be able to group. Can index also move the stock? With time grouping, relationships across time-related fields are automatically detected and grouped together when you add rows of time fields to your PivotTables. How can deflection and spring constant of cantilever beam stack be calculated? Cannot Group that Selection The first problem " Cannot Group that Selection " is normally caused by one of two things . I want to create a pivot table and then group the time to be sorted by hours (between 01:00:00 and 01:59:59). Excel pivot table field grouping problems (numbers seen as text), Excel Pivot Table recognizing blank cells (“”) in column as text not numbers, Excel pivot table counts empty cells as having a value, Blank cells outside of Excel sort selection affecting sort order. After you group Items, Excel creates a new Pivot Table Field. Software: MS Excel 2016. Pick The Earliest Date From A Selection Of Dates Grouping data bunches rows or columns the way you want and which might not be possible with the PivotTable tools like sorting or filtering. Cannot Group That … Unfortunately, it seems that if you check this option, you will not be able to group any fields and you will always get the "Cannot group that selection" error. I wanted to group them using Excel>Data>Group function. It only takes a minute to sign up. When you attempt to copy nonadjacent cell or range selections, you may receive the following error message: That command cannot be used on multiple selections. This would result in rectangles of varying shapes and sizes. Is Blank; Contains Text; Contains an Error. Do GFCI outlets require more than standard box volume? Select the column, run Text to Columns (in the Data menu or AltAE), Delimited, and Finish. The first, and most likely cause, is due to the fact that your Pivot Table data range includes blank cells within the field in which you are trying to group. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy. When you highlight the section to copy and then go to the new workbook both the paste and paste special are "grayed out". That's not a strange format, that's text. Making statements based on opinion; back them up with references or personal experience. false. For example if you wanted to perform an operation on the following sample data: Because a contiguous rectangle remains if you were to collapse row 2 and column B, you can individually select cells A1, C1, A3, C3 and copy them without error. We have a number of Excel users in our office who cannot copy and paste between Excel workbooks. How can I use pivot table grouping by dates incremented by a day at a time? What is the make and model of this biplane? “Cannot group that selection” error in Excel pivot tables If you’re seeing the “Cannot group that selection” error message when trying to group pivot tables, it is most likely because your data is invalid in some way. Clean your data to contain only numeric value in the row label column 2. Because Excel "slides" the ranges together and pastes them as a single rectangle, a contiguous rectangle must remain if the rows and columns in between the selected cells are collapsed or set to a size of 0. Author Debra Posted on August 12, 2020 August 17, 2020 Categories Excel Pivot Table Leave a comment on Value Group Slicer for Excel Pivot Table Create and Filter Two Pivot Tables on Excel Sheet Usually, it’s best to set up your Excel file with one pivot table per sheet, so … Filtering cannot happen independently of cell selection. Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. rev 2021.1.11.38289, The best answers are voted up and rise to the top, Super User works best with JavaScript enabled, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site, Learn more about Stack Overflow the company, Learn more about hiring developers or posting ads with us. These error messages may occur when your cell or range selections are in different columns or different rows. If you attempt to group a pivot table, but find that the Group... option is greyed out, or that a message box pops up, saying "Cannot Group That Selection", this is usually because the data column in the original data sheet contains one or more non-numeric values (or non-date values), or errors.. Excel leaves it up to you to find out what the problem is. Multiple columns within the same row, for example, A2, C2, E2. Excel 2007 :: Pivot Table - Can't Group On Dates; Unable To Group Dates In Pivot Table Despite Formatting The Column; Excel 2010 :: Grouping Pivot Dates By Group - Can Change Quarters? In Europe, can I refuse to use Gsuite / Office365 at work? “Cannot group that selection” error when creating pivot table, Podcast 302: Programming in PowerPoint can teach you a few things, Dates not recognized as dates in pivot table pulling directly from SQL Server. 3. In this case, the cell range must be a single selection, or the cells must be copied individually. I checked column c ( my date column) there are no blanks - the format it 1/2/2016, don't know why it's not grouping. Multiple rows and columns within the same columns and rows, for example, A1, C1, A3, C3, A5, C5. So, the workaround is to not choose "Add this data to the Data Model" when creating the pivot table. and is then later filled in. I'm not sure why this happens. is that possible? Was there ever any actual Spaceballs merchandise? Why is there no spring based energy storage? Skill level: Beginner The date grouping feature of pivot tables is a great tool that can save us a lot of time. Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. When you make a nonadjacent selection and then click Copy on the Edit menu, Excel tries to identify an outline type of the selection. Thanks for contributing an answer to Super User! For more information about this change, read this blog post. You may need to remove and re-add the field to the Pivot Table before it will group, I find it seems to cache the field type and won't group dates if one was blank (so it treats a whole column as text, grrr!) How to prevent players from having a specific item in their inventory? For Excel 2013 and later, there's another thing that can prevent you from grouping -- the Excel Data Model. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst is the best practice when it comes to staying organized, as you should never hide cells in Excel. Cannot Group on this selection. Add helper column in data range to serve as grouping header. 1) Blanks in the column, or . Excel File: GitHub Repository I am unable to group fields in pivot table of Excel file.. Graffiti table is linked with Calendar table via Date (in PowerPivot > Data Model), so why is there difficulty in grouping the following. This might be caused by the Data Model, or cells that are blank, or previously grouped items. In Microsoft Office Excel 2003 or in Microsoft Excel 2002, on the Tools menu, click Protection, and then click Allow Users to Edit Ranges. "CANNOT GROUP THAT SELECTION" Error; Changing Dates Based On Dropdown Selection? There are few ways to work around this. (To make a nonadjacent selection, hold down the CTRL key while selecting additional cells or ranges.). To subscribe to this RSS feed, copy and paste this URL into your RSS reader. The reason for this seems to be how Microsoft Excel sees the data. For example, B2 could slide between A1 and A3, C1 and C3, A1 and C1 or A3 and C3. Where did all the old discussions on Google Groups actually come from? (Ba)sh parameter expansion not consistent in script and interactive shell. To learn more, see our tips on writing great answers. 2) The column is not of date type Date in Excel. If a president is impeached and removed from power, do they lose all benefits usually afforded to presidents when they leave office? Reasons for “Cannot group that selection” Error in Excel Pivot Table Date Grouping. Generally, Stocks move the index. This selection will be pasted as a single rectangle: However, you cannot add cell B2 to this nonadjacent selection, because Microsoft Excel cannot determine which direction you want the cells to slide.