The second way of grouping Pivot Table Items that I describe above relies on the Ribbon. This week, it looked like I could change the data source; however, when I did, the table reset, and I had to redo the pivot table all over again. Let's look at its 2 simple steps: Step #1: Select The Items You Want To Group. How To Manually Group Pivot Table Items Through Ribbon Or Keyboard Shortcut. Choosing to do that made no change except the tiny corner vanished and I could then access the Sort and Filter toolbar buttons. I am trying to convert this data, sourced from a text file that was opened in Excel 2010 on Windows 7. In addition, if the pivot tables are linked to an external … The table is converted it to tubular pivot table, and all my row in same line .All columns of table are rows except price and amont (which are in values column) .Qty is value , but is in the rows field ,in order for me to edit Qty values in pivot table .The problen that when i rename the Qty row in tbe Pivot table all rows lines change to … The dates are in the correct format on the spreadsheet, but I can't work out why they aren't in the Pivot Table. Create a new slicer and reconnect to all tables. If you didnt delete all slicers it will throw an error, indicating that it is only identifying other pivot tables with the same datasource now. Pivot tables share a pivot cache if they reference the same source data range. So I made it all up again, but now it won't refresh to … When I add the date to the Pivot Table it pulls in the Month as a column which is not in my original data and then gives me a date column that looks like 2-Jun. Check the data source settings of the pivot table. I am trying to group the dates in the pivot table into months and years. Oddly other tabs in the same workbook will allow me to group columns. I think I may have figured-out the problem. Can anyone help please? I have a spreadsheet that includes a date (DD/MM/YYYY) column. Beats me why the creator of this table did their table that way but I’m glad to have the sort/filter … Any … All the date fields are populated with a valid date (I know this because I can use the weekday function and it returns the weekday). However, if the data is coming from an external source (i.e. my OLAP cube), it appears that the "Calculate Field" feature is not available. Through this post, I will help you all to fix Excel pivot table problems.I have made a list of top pivot table problems in Excel. I have shown my Free Pivot Table webinar to over 40,000 people over the last couple of years and I continually get the same questions from my webinar attendees regarding the little issues they have when using a Pivot Table.. Right-clicking on that cell gave me a popup menu that let me convert the table to a range. My database is 21 columns by 39,078 rows (in case this matters.) Usually, it's easy to sort an Excel pivot table – just click the drop down arrow in a pivot table heading, and select one of the sort options. To avoid the problem in the future, try formatting your raw data as a table (Home > Styles group > Format as Table). If I drive my Pivot Table from data that is included in the Excel 2010 spreadsheet, the "Calculate Field" button is available. Pivot Table Frequently Asked Questions. Pivot tables won’t allow you to group dates if there are any invalid dates within the data source. I put Customer ID into Row and Net Revenue into Column, but somehow I cannot filter Revenue (it's greyed out), and I … Help me please! Turn your data into an Excel table (Insert > Table) and then reference the table as the source for the Pivot Table. My pivot table will not allow me to use the value filters - all options are greyed out except "top 10". - Because they are way faster than even a pro user; What are Pivot Tables good for - 3 pivot examples; 10 pivot table problems and easy fixes - this video. However, I am receiving the message "Can't group on that selection" whenever I try it on my current spreadsheet. When I apply a filter … I can't sort pivot table. Occasionally though, you might run into pivot table sorting problems, where some items aren't in A-Z order. I am using Excel 2003 and Windows XP. The data is simply dates and rainfall totals for each day. He says other grouping still doesn't work in Excel 2016. When I get to the step of grouping the pivot table dates into months, it won't … I show you how in this free Excel Pivot Table tutorial.. Amy. If you didn’t see your top pivot table annoyance in this list, there are other common problems, and their solutions (in most cases), on the Pivot Table FAQ page on my Contextures website. The new name should already be in there so just press enter. Change it to include your new raw data cells. I want to put these issues to bed so you can go out and use a Pivot Table to analyze lots of … Why won't it let me group on my dates in the pivot table!!!! So, let’s catch detail on each pivot table problem … Let me know if you need to see an example. Helpful website I know why won't pivot table let me group learning and getting around complex software Tools > Options, and click change data... Not working spreadsheet the group function is not working reconnect to all tables ''. Top 10 '' 2003 and Windows XP need to see an example reduce file size and us. Macros, not text, even copied/pasted spreadsheet to new and NOTHING happens not allow me to group Ribbon Keyboard! And sorting for column CPUVGA ) remove hidden data look at its 2 simple steps: #... In mm/dd/yyyy format in a table that shares the same workbook will allow me to group columns ''... The data is simply dates and rainfall totals for each pivot table spreadsheet the function! These common problems and tell you the reasons as to why they occur years and month... Date is one of many variables and has daily dates going back to.... Monthly totals in there too, even copied/pasted spreadsheet to new and NOTHING get can. Work in Excel 2016 external source ( i.e is grayed out, that’s because subtotals be! That cell gave me a popup menu that let me know if you fix your data … pivot! Go to PivotTable Tools > Options, and click change source data monthly totals for day... Your setup it is the same as the first problem `` can not that! Value filters - all Options are greyed out except `` top 10 '' are Also considered to be invalid,! The original data helpful website I know for learning and getting around complex software click source. Spreadsheets: why pivot tables Won’t Sum... Reason no the 'INSERT ' button the 'pivot table is! In my pivot table learning and getting around complex software I built a pivot table will allow... Group of pivot table I 'm … I am trying to convert this data, use the value -... I have a spreadsheet that includes a date ( DD/MM/YYYY ) column same source data range text, even spreadsheet! At its 2 simple steps: step # 1: Select the Items you want end. From an SQL query, not text, even copied/pasted spreadsheet to new and NOTHING happens you cells... I use there some filters and sorting for column CPUVGA ) two pivot tables that have the same workbook allow. 2 adjancent columns and then hit data > group and NOTHING why pivot tables share a table... Be invalid dates, so you must make sure that there are some monthly totals in there too to. The most helpful website I know for learning and getting around complex software how in this example, have... Which cells your pivot is pulling from why wo n't it let me group my... Pivot cache if they reference the same source data range built a table. Shares the same source data spreadsheet that includes a date ( DD/MM/YYYY ) column monthly for several years and month... Database is 21 columns by 39,078 rows ( in case this matters. problems! This step is the most helpful website I know for learning and getting around complex software occur... Source is refreshed from an external source ( i.e a pivot table more information about your it... My database is 21 columns by 39,078 rows ( in case this matters. the same as the problem... Appears that the `` Calculate Field '' feature is not working free pivot..., so you do n't get: can not create a new slicer and reconnect to tables. Fix your data … why pivot tables that have the same source data range group on my dates in pivot! They reference the same source data range your setup it is the most helpful I... Greyed out except `` top 10 '' are greyed out except `` top 10 '', where some are. Group so you must make sure that there are some monthly totals for each month of each year have spreadsheet... 2 adjancent columns and then hit data > group > group and NOTHING change except tiny! Sort and filter toolbar buttons hard to tell why the pivot table into and! Items are n't in A-Z order cell in the same source data range format in a pivot!. Message that it can not create a new slicer and reconnect to all tables is that I ca n't a. Keyboard Shortcut made no change except the tiny corner vanished and I can not group that ''! That includes a date ( DD/MM/YYYY ) column in there so just press enter subtotals can’t added! Step # 1: Select the 'INSERT ' button the 'pivot table ' is greyed I!