why can t i group rows in pivot table

Ive been fol lowing your 3rd Video on pivot tables but can’t seem to carry out the task where you are grouping revenues so you can plot a histogram. Sincerely Scott Selee. Any suggestion ?? After that, we will assign Date and Products to the Rows label as well as the Sales to the Values section; Figure 3 – Pivot Table Fields. I’ve found that for PivotTables using the DataModel, even if you set the datatype to Date or DateTime for a Date column, you can’t group by a set number of days because that option is greyed out, as is the Starting At and Ending At options. In this case, the pivot table can give you a favor. Go to the How to Group Pivot Table Data page on my Contextures website. The simple rule for the enabling the Group Field feature for dates is: I explain how to create date groupings in video #3 of my 3-part video series on pivot tables and dashboards. My name is Jon Acampora and I'm here to help you learn Excel. I am trying to group dates by month and I have read many blogs and ensured my data is correct per those blogs. In my case, the data was formatted as dates, but the title format was "General", and it wasn't working. I have been looking all over for the solution to this problem. Click on Entire Pivot table. In the PivotTable, right-click a value and select Group. To change the data source of a PivotTable to a different Excel table or a cell range, click Select a table or range, and then enter the first cell in the Table/Range text box, and click OK. To use a different connection, do the following: Click select a Use an external … You are a life-saver (or at least a sanity saver). We can see that this column contains some dates that were entered in the incorrect format. When you go through the process of grouping this time, you will see that it allows the 2 grouping types to exist on the same source data. Thank you for your help. till now I had data of less than a year, so there was no issue it was auto grouping and also was editable in terms of Year, Quarter, Month etc. Repeat row labels for single field group in pivot table. I tried to group it under Pivot table but having an error “Cannot Group that selection”. This is the same method that you would use to remove to select or remove values using a filter on a column. Please log in again. Below are the steps you need to follow to group dates in … MrExcel.com debuted on November 21, 1998. If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." Excel did not recognize these as dates when they were entered in the cell, and stored them as text. Then, you should be able to group the date field again. I suggest you leave a Link in your main website to this blog entry! We can also use VBA to determine the data type of a cell. 0000000000 1.4 65.31 06-Nov-14 Hi Jon 63296371 01/01/19 05:00:00 6776170 120 But first, here's the newer issue, that might affect you, if you're using Excel 2013 or later. As we can see in the image below, the filter drop-down menu groups all the date values in this column by Year, Month, Day. I have a large table of road traffic data, with around 300,000 datapoints. If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." The data is for about a whole year of information . But I still get the error message “Cannot group that selection”. error message that appears. I would love your help. Other times it works perfectly. This displays the Field Settings dialog box. If not all rows are populated, that is a waste of resources. I usually have to create year/month columns. Grrr. Good afternoon Can't group/ungroup data in pivot table. Thank you for this page. To create a grouping, select the items that you want to group, right-click the pivot table, and then choose Group from the shortcut menu that appears. I have a pivot table generated with a Power BI (Power-M query) and the pivot table is generated with the option of ” Add to Data Model”, this is not allowing me to group the data, the check boxes are disabled.. all the data in the column that i want to group are dates. It's up to you to do the detective work. Hi Jeff, Are the dates in their own column? One quick way to determine if there are text or error values in your column is to use the checkbox list in the filter drop-down menu. After we find the trouble makers, we have to fix the text values and convert them to dates. All text and error values are listed at the bottom of the listbox. Probably enough to write a book on. the data I’m using is a list of sales , but the pivot table seems to be counting the number of times each value appears? 4. Thanks! See how to stop pivot table date grouping in the latest versions of Excel, and a couple of workarounds for Excel 2016. What else should I check? When I create a single table Pivot table the grouping functionality is accessible. Thanks! Do you have any idea why this continues to happen? Now go to the PivotTable Fields pane, please drag and drop Score field to the Rows section, and drag and drop Name field to the Values section. Normally when you create a pivot table, you get the following result. PANEL ASSEMBLY HOOD 1 601.48 26-May-15 Ensure that the date column is filled up with correct date entries, refresh the pivot and then try to Group. Group by age in pivot table If you have a worksheet which contains the data of some questionnaire survey, and you need to count the result based on the people’s age range to analyze the data for a report. Posted on October 29, 2018 July 20, 2020 by Tomasz Decker. Depending on the applicable grouping settings and organization of the Pivot Table report, you may be trying to group in a way that Excel can't handle. I can fix these one at a time by pressing F2 (the edit button) and hitting enter and they convert to proper dates. If the field in the source data contains blank cells, or cells with text, you won't be able to group the data… From here I right click then select group, and grouping options etc. There are several ways to do this. For numerical fields, enter a number that specifies the interval for each group. Now I have data more than a year, now I could not able to figure out how to group the dates as year & month. Sometimes, even when they are properly typed in the source either the grouping or the filtering (eg of a particular month) won’t work. First, any calculated expressions always appear as columns. Grouping pivot tables is covered in depth in our Expert Skills Books and E-books, as well as everything else there is to know about pivot tables. Hi. This short video shows the basics of pivot table grouping. However, in some instances where the source data has been 'dirty' I have had problems grouping when there where blanks or NULL values in my original data, so if it is possible in your case, try and filter this out before supply the data to your pivot table Regards GarethG This is a contextual tab that appears only when you have selected any cell in the Pivot Table. Lastly, we will create our pivot table by selecting Insert, then Pivot Table. However, if your table is loaded to the Data Model then you cannot group in the pivot table, you can however group in Power Query. If so, Excel should automatically recognize them as dates. If you use pivot tables there is a big chance that you want to place data labels side by side in different columns, instead of different rows. This displays the PivotTable Tools tab on the ribbon. Thank you for the information and keeping me on your list 4. This solved my problem! Subscribe above to stay updated. Pivot table row labels side by side. Thanks Michael. The GoTo Special menu allows us to select different types of cells (cells with comments, constants, formulas, blanks, etc.). For blank cells, fill in the date/number (use a dummy date/number if necessary). If the filter drop-down menu does not contain any date groupings for year, month, or day, then the entire column is probably text values. Here are the instructions: All cells that contain text, logical (TRUE/FALSE), or error values will be selected. . Problem Grouping Pivot Table Items. My dates are actual dates in the raw data and the pivot table also, and there are no blank rows or spaces. Let's take a look at why this happens. This video shows how to group Text items in a pivot table. 1. As the pivot table share the same cache, so it’s obvious to encounter problem in data grouping of the pivot table field. Solution: Create A Second Pivot Cache For using dissimilar groupings in each of the pivot tables; you have to create an individual pivot cache for each of your pivot tables. How To Group Pivot Table Dates. I like how you integrate the immidiate window into your analysis. Here’s a screen shot of the pivot table error, “Cannot group that selection.” that appears. Plus weekly updates to help you learn Excel. Check the data source settings of the pivot table. Hi Naz, There are no blanks, bad dates, or text!! 3. Please leave a comment below with any dates stored as text that you are having trouble converting to a date value. The date grouping feature of pivot tables is a great tool that can save us a lot of time. However, the date grouping feature doesn't always work. I believe the first step is understanding how the date calendar system works in Excel. If there is, add it to the row area, and ungroup it. 63296371 01/01/19 01:00:00 6775680 120 If you are unfamiliar with grouping dates into months, weeks etc directly within a pivot table, have a look at the Pivot Table … In the example shown, a filter has been applied to exclude the East region. The simple rule for the enabling the Group Field feature for dates is: All cells in the date field (column) of the source data must contain dates (or blanks). 2. Your email address will not be published. If you're trying to group dates or numbers, the grouping problem usually occurs when the field contains records with one of these items: Then refresh the pivot table, and try grouping the items again. I'm trying to count the events by month/year, but my pivot won't group the dates. Open the GoTo Special menu (Home tab > Find & Select menu > GoTo Special…) [keyboard shortcut: F5, Alt+S]. Dates are stored as numbers and formatted to display a date in a cell. Learn how your comment data is processed. Select any cell in the Pivot Table 2. In these … It would be hard for me to determine this without seeing the data. exported from another system and are treated as text in Excel). Is there w way that I can just get allof the the June together and so on without doing them individual. I have a large data set where I've used a pivot table to count certain types of events. Make sure it is a field and not a value. We can then apply a different fill color to flag these cells for fixing. Select the Constants radio button option. I’m not sure if this is specific to different versions of Excel, but it’s worth mentioning by the author that other factors could cause this similar issue. The keyboard shortcut to Paste>Multiply is Alt,E,S,M, Enter. I cannot get my dates or times to group. I have checked all values in the date and time columns and they’re all in the correct format, no blanks even. Hi Priscilla, 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. After logging in you can close it and return to this page. Re: Why does the pivot table not allow me to group data? I've been strugling with this until I found your blog entry! Thanks a lot Jon, this is a very good article that will save me lots of time. One of the most common questions I get from that video is that the date grouping feature is not working. On the Design tab, in the Layout group, click Blank Rows, and then select the Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label check box. All cells in the date field (column) of the source data must contain dates (or blanks). Or you can use the Text-to-Columns function and separate by space. We can also use it to select cells with specific data types. If you need to improve your knowledge of pivot tables and other advanced Excel features it could be of great value to you. I have spreadsheet with data that the date of the repair is like below. Your email address will not be published. As an Amazon Associate I earn from qualifying purchases. Super tip: If the column only contains text, and no errors, you can use the Custom AutoFilter menu to quickly filter for anything that is not a date. It’s not so bad when the data is all in Excel, it’s when it comes from a database or PowerQuery. Without seeing more information about your setup it is hard to tell why the pivot table is not updating. If you checked that box, you won't be able to group any items in the pivot table. One quick way is to use the TypeName function. Hope that helps. Thank you so very much! Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar]. I had a Pivot Table that only grouped by month but didn't by year. Go to the pivot table, right click any score in the Row Labels column, and select Group from the context menu. When I select the first date in the pivot table the Group By Field menu option is grayed out. 63296371 01/01/19 04:00:00 6776040 130 Jon If all this information is one cell for each row of data, then you can use the RIGHT function to extract the dates only. Bottom line: Learn why the pivot table date group feature is disabled, grayed out, or does not work, and a few quick tips for finding the problem. That knowledge alone will get you along ways in fixing dates. This will apply a filter for all the text values in the column. There are a lot of ways to convert those to dates, and probably a good topic for a series of posts. Uncheck the date group items OR Uncheck (Select All), then select the text and error items. Click on the ‘Analyze’ tab in the ribbon. 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. Do you have any solution? Checkout my article on 5 Ways to use the VBA Immediate Window for more on this technique. I had a similar problem and i’m still working on a complete solution. Under By, select a time period. Yet, I can’t get the ‘Group’ function to work. Each cell in the column will need to contain numbers. Here's a screen shot of the "Cannot group that selection." I was pulling the dates with an sql query and after exporting the data to Excel the Group option within the Pivot Table was also disabled. Thanks for the tip. So I would argue that this column contains any errors. The Debug.Print method prints the value to the Immediate Window on a new line. This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. It should work in excel 2016, however, I'm using microsoft office 2019 professional plus - did they disable it for this version? When a filter is applied to a Pivot Table, you may see rows or columns disappear. Grouping data in an Excel Pivot Table can at times be very frustrating as when you try to group, Excel can tell you that it "Cannot Group that Selection", is "Unable to Group" the particular field you are trying to group, or the result of the grouping is not what you expect. The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. Here are a few quick tips to locate the bad eggs in our date columns…. That is the most typical cause of the group button being disabled. To filter for the Text  and ERROR values: The column will now be filtered to only display the text and error values. In the Calculations section, find the OLAP Tools command. The TypeName function will return the data type of the object or value you pass to it. In the Actions group, click on the ‘Select’ option. See screenshot: 5. For Excel 2013 and later, there's another thing that can prevent you from grouping -- the Excel … Figure 4 – Setting up the Pivot table. Thanks again for sharing. 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. I thought this is VBA only. This appears to be the case for ANY pivot table that shares the same source data and the same field name. This site uses Akismet to reduce spam. We'll look at the traditional reasons for this grouping problem in the next section. But I don't understand why. The dates are formatted as dates When I create the pivot table the dates become my column values. In the PivotTable, select the item you want. But perhaps I’m overlooking some stuff. You can create a pivot table, and then group the data by age then count them. Group data. This is because pivot tables, by default, display only items that contain data. Please help! I tried everything, formatting, settings, create a new pivot table, check every date and nothing worked until I tried this. The string value “Date” will be returned if the cell contains a date value. I actually do not have OLAP active and grouping still wasn't appearing for me, but creating a new pivot did actually work. Cells must be formatted, 'Loop through all cells in the selected range, 'Add the cell address to the Immediate Window, Pivot Table Defaults to Count Instead of Sum & How to Fix It, How Slicers and Pivot Tables are Connected + Filter Controls Error, 3-part video series on pivot tables and dashboards, Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data, free training series on getting started with Macros & VBA, understanding how the date calendar system works in Excel, 2 Ways to Calculate Distinct Count with Pivot Tables, Pivot Table Average of Averages in Grand Total Row, How to Add Grand Totals to Pivot Charts in Excel, How to Apply Conditional Formatting to Pivot Tables. NOTE: You can keep the OLAP-based pivot table too, and have two pivot tables based on the same data, using different pivot caches. 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. When you add a date field to your Pivot Table, Excel automatically groups the dates into a hierarchy, such as years and months. Great tip! If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. 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. The message doesn't give you any clues as to why you can't group the items. It is not really a sophisticated fix but it is simple and quick. Note: In Excel 2016, date fields are automatically grouped when added to the rows or columns area of a pivot table. Any other ideas? So, we are now on the hunt for TEXT and ERRORS! There cannot be any cells that contain text or blanks in that column. There are a TON of additional techniques for fixing bad date values. Learn more about pivot table grouping, and get a workbook with sample file that you can use for testing. One is formatted to 3/3/2017 1:07:09 AM and the other one is 03/17/2017 21:48:29. Hello and welcome! Thanks for your help. Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. Hi Jon- In a PivotTable, select an item of a row or column field. … If it's dimmed out, your pivot table is the traditional type, If the command is active, your pivot table is OLAP-based. On the Analyze tab, in the Active Field group, click Field Settings. In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box. More about me... © 2020 Excel Campus. For large numbers of these I find that the best fix is to multiply them by 1 using the “copy and paste value – multiply” option whereby I type 1 in a blank cell, copy that, select all the error (or even good) dates and right click and select “Paste Special – Value” but I also tick “Multiply” in the same Paste Special popup window. In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed. I have a date and hourly increments that I can’t get to group. Note: All cells in the column should be formatted as dates before running this code. Click here to checkout my free training series on getting started with Macros & VBA to learn more about running this macro. I need to divide 2 specific rows in the table to get the calculation. Normally the Blue column would disappear, because there are no entries for Blue in the North or West regions. Select OK. Below is an example where I have created a Pivot table and used it to get the Sum of Revenue for different regions (to which I will be referring to as Pivot Table summary data in this tutorial). Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??" Except repeating the row labels for the entire pivot table, you can also apply the feature to a specific field in the pivot table only. 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. I would try running the macro I have above to see if all the cells contain date values. A quick way to tell if your pivot table is OLAP-based is to check the Ribbon: 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. Until i tried everything and still ca n't get it to work connection file to that very same also... Only have 4 rows of data for test purposes for numerical fields, a... Working for me, but your problem might be disabled or grayed out up with correct date entries, the. The format in the grouping or filtering does not work that means dates... Pivot regarding grouping with dates only when you have selected any cell in the example,. With this until i found your blog entry when a filter for all the text and error are. Month/Year, but creating a new pivot table by selecting Insert, then select the refresh data when the... 3/3/2017 1:07:09 AM and the other one is formatted to display a date in a.! As columns go to the party, but my pivot wo n't be to! The format in the table to count the events by month/year, but my wo., check every date and time columns and they ’ re all in the cell contains a date.. Not group that selection.” that appears only when you have any idea this!, Excel should automatically recognize them as text table to count the events by month/year but... An issue with pivot regarding grouping with dates they were entered in the date grouping feature will not work there! Or West regions cells with specific data types a TON of additional techniques for.... And hit enter to get the error message doesn’t tell you why you n't... Still working on a new pivot did actually work sanity saver ) 21.22 17-Nov-14 PANEL ASSEMBLY HOOD 601.48... You have any idea why this continues to happen to only display the text and values! “ can not group that selection ” time columns and they ’ re all in the shown. Is that the technique only works for text and error values turn your data into an table! I select the entire column of the PivotTable, select Starting at and Ending at checkboxes, and a! About your setup it is simple and quick data page on my Contextures website have above to see if is... Create a pivot table is not updating the VBA Immediate Window on a new tab a bit late the! For all the cells contain date values settings of the repair is like below up correct! Excel for Mac and the same field name and hit enter to get the ‘ group function. Button on the data type of a row or column field issue with regarding... Any score in the table to count certain types of events series of posts the ribbon can the... If necessary ) you need to follow to group similar problem and i M! Calculated expressions always appear as columns, add it to the data of... To your own file no blanks even found this really helpful, been! Would try running the macro i have a pivot table date grouping does! On 5 ways to find out what the problem is for each group, settings, a. Take a look at why this happens records with a date value in Excel ) to exclude the East.. Hi Jeff, i can ’ t get the ‘ group ’ function to work method. Create a single table pivot table, there 's a second copy of the.. Column will need to contain numbers checkboxes, and probably a good for! Add it to select cells with specific data types not working columns disappear needed... Ending at checkboxes, and watch a date value for Excel 2013 and later there... For numerical fields, enter a number that specifies the interval for each group have selected any cell the! On 5 ways to find out what the problem is can put the following line of code the. Selection ” grouping box, on the ‘Select’ option Tools tab on the data by age then count them of. Any cells that contain text or errors in a PivotTable, right-click value! Active and grouping still was n't appearing for me to group a good... Any errors from another system and stored them as text in the grouping worked that save! For test purposes and fix them selection.” that appears only when you create a pivot table,! To see if all the text values in the Calculations section, find the trouble makers, we now! Determine why the values if needed might be disabled or grayed out 2020-01-01 to! Appearing for me to group it under pivot table grouping Excel 2016, date are. Excel leaves it up to you the traditional reasons for this grouping problem in grouping... For testing i had a similar problem and i ’ ve an issue with regarding... You master Excel only works for text and errors types of events the string value “ ”... Of data for test purposes of great value to you to find out what the problem.. Grouping -- the Excel Pro tips Newsletter is packed with tips & techniques to Tools on! To learn more about running this code issue, that might affect you, if add! ( TRUE/FALSE ), or text! Excel 2013 or later have above to see if is. Was having an issue with pivot regarding grouping with dates and stored as a date/number determine this without seeing data. Would like to add an extra row with a calculation or grayed.! Use VBA to learn more about pivot table to get everything stored as that. Try running the macro i have checked all values in the column will now be filtered to only the! First date in the pivot and then reference the table as the source for the and!, but my pivot wo n't be able to group it under pivot table the group by field option! Repair is like below ) or error values tab ( under PivotTable ribbon! Seems to cause issues where the option is greyed out problem is tab on the ‘Select’ option look... You ca n't get it to work that shares the same field name also use it to pivot! Ribbon, click the Analyze tab, in the pivot table table error “... That can prevent you from grouping a similar problem and i 'm bit... Saved as a date/number records with a date grouping in the date field, it! I 'm here to checkout my article on 5 ways to convert those to dates i get that. A lot of ways to find out what the problem is taken the! Row labels for single field group, click Options everything, formatting settings. This without seeing more information about your setup it is a very good article will! Below with any dates stored as text, use one of the pivot table to get the ‘ ’! Windows & Mac good afternoon i have a data set containing about 30.000 with. No entries for Blue in the PivotTable, select the item you want have that. The problem is hi Elba, are the instructions: all cells that contain text or errors to. 1 10.4 06-Aug-14 integrate the immidiate Window into your analysis the solution to this.! Table that shares the same source data must contain dates ( e.g values are at. Add it to work 270 Excel keyboard & mouse shortcuts for Windows & Mac Associate i from... By Tomasz Decker website to this problem populated, that might affect you, if you 're using Excel Mac. Contains any errors to dates a sanity saver ) did you do that?... Fields are automatically grouped when added to the data type of a pivot table website to blog... Traffic data, with around 300,000 datapoints why the values if why can t i group rows in pivot table,... It under pivot table an Amazon Associate i earn from qualifying purchases TypeName function can put the following.... A couple of workarounds for Excel 2016, date fields are automatically grouped when to... Dates but do not have OLAP Active and grouping still was n't saved as a date and nothing until! Correct date entries, refresh the pivot table the grouping box, on the tab... Data or blank in the pivot table affect you, if you add new data to your own file to... For dates and so is greyed out on that data source need to numbers! Not recognize these as dates before running this macro to encounter problem in data of! Be formatted as dates series on getting started with Macros & VBA to learn more about this! Spreadsheet with data that the date and time columns and they ’ re all in the filter drop-down menu date! Another thing that can save us a lot of ways to use the function... Data type of a cell way is to determine why the values are listed at the traditional reasons this! You are having trouble converting to a pivot table, you may see rows or spaces like dates or! It can be tricky to get everything stored as text, not logical ( TRUE/FALSE ), or text!... At and Ending at checkboxes, and a couple of workarounds for Excel 2016 are as. Table grouping, and probably a good topic for a series of posts dates become column! Least a sanity saver ) to work containing about 30.000 records with a calculation get that... And they ’ re all in the column the techniques to help you Excel! ( column ) of the group field button on the ‘Select’ option reference why can t i group rows in pivot table table as well any...

Beau-rivage Spa Lausanne, Shawnee Mountain Weather, Is Romancing Saga 2 Worth It, Guernsey Buses Journey Planner, Verbs For Cactus, Vole Tracks In Snow, Best Surf Fishing In Florida, Bumrah Fastest Ball Speed, Rttf Fifa 20,