Even if in filter they are mentioned as Blanks, If select filter table on "blanks" only, press Del (other words clean such cells), refresh PivotTable, we will see that (blank) appears in it and grouping is available. 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. ‎08-09-2020 3. For some reason I only have dates in that field. Drop the data into Excel into a table. 1. Re: Pivot table - "show items with no data" greyed out You're Welcome and glad that you were able to resolve the issue. I spend some hours trying to bundle data in groupings in my table. The pivot table in that figure is using Tabular layout. June 13, 2017, Posted in Clicking this PivotTable icon gets the usual dialog about where to place the Pivot Table, it can be positioned on an existing sheet, or open a new sheet. Thus we shall clean the source before aggregate by PivotTable. If you try to pivot off this data, the calculated field will still be grayed out. My option is greyed out, along with Calculated Field, Solve Order and List Formulas. The Date field contains many items. STEP 6: We need to Refresh our pivot table to load our new correct dates but first we need to “uncheck” the ORDER DATE field. The Group Field button on the Analyze/Options tab of the PivotTable tools ribbon might be disabled or grayed out. The second pivot table does NOT allow for Date grouping. custom subtotal in pivot table greyed out I am trying to work out a 13 week average for my work, however when i go to field settings, custom subtotals is greyed out. I did go to the data table and made sure that my dates are actually dates. I spend some hours trying to bundle data in groupings in my table. Yeah, nothing is stored as text here, and everything is in order. Drop the data into Excel into a table. thank you Database1.accdb For more detail information for "Group items in a PivotTable report", please refer to the following link: http://office.microsoft.com/en-us/excel-help/group-items-in-a-pivottable-report-HP010342560.aspx. I … A Date field (column) can be grouped by Days, Weeks, Months or Years. Tia! Step #1: Select 1 Of The Items Within The Group. As far as I know, if there is a cell empty without date, we could not use the Group Field. May 07, 2019, by Let's take a look at why this happens. I want to be able to group by month so I can see orders by month/user vs day/user. And yes there is text in the example file. So now i'm trying to add a COUNTIF formula in the Calculated Fields section. I attached an example file. 2. - edited Below is a before and after example of the date grouping structure. I set a filter in the date column of the source data, and discovered that several dates were invalid (e.g. The data doesn’t need to have a field (column) for those categories. on To group these dates by quarters, execute the following steps. Thanks! If you don't have any other questions then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. The Item you select depends on the group you want to ungroup. How do you 'scrub' your data? Create and optimise intelligence for industrial control systems. It would be nice if you can find the fix needed! Insert Pivot Table Is Greyed Out! Im using PowerPivot and pulling data from PowerQuery. When you create a pivot table, there's a check box to "Add this data to the Data Model". I recommend we check the dates in the file and re-create the Pivot Table. Several viewers asked me to demonstrate some other ways to -Group a Field- in a Pivot Table. But when i copy this output in a new table, without formulas, i am able to make a pivot table and do a manual grouping. For now, I can only see dates by month and not days. Example #3: Ungroup Numeric Pivot Table Field Through Ribbon Or With Keyboard Shortcut. To create the pivot table below, instead of the Product field, add the Date field to the Rows area. For example, you … Would be nice if someone can find the solution. by Sophia Tsoleridou If it's dimmed out, your pivot table is the traditional type If the command is active, your pivot table is OLAP-based And if you check the Fields, Items, & Sets drop down, some of the features will be dimmed out, for OLAP-based pivot tables. Your data is now clean! The "field grouping" option in the menu of pivot table analysis is greyed out for some reason. September 22, 2020. I am using a pivot table from PowerPivot data and I have a field called 'Sections' in the pivot table Columns that I need to show all of the sections even if there is no data after I select dates with a slicer. 05:15 AM In general it's doesn't matter the value was added to the cell manually or by formula. I believe that by right clicking one of the dates it should be possible to group them by say quarter but the 'group' item on the menu is greyed out. I've tried the following: Application.CommandBars("PivotTable Field List").Enabled = True ActiveWorkbook.ShowPivotTableFieldList = True. They were originally stored with a time in the field. - Just wondering I have a few spreadsheets that I created... - Free Excel Help Home. The most common type of grouping is date grouping. If you checked that box, you won't be able to group any items in the pivot table. Slicer connection option greyed out for excel pivot table so I found that the grouping function is not working with data comes from a formula (in my main table). Using the Date attribute of the Date dimension - Excel still doesn't enable "Group Field". If you try to pivot off this data, the calculated field will still be grayed out. The date field will be placed in the Report Filter of the Pivot Table. Perhaps the main culprit is that the data in the column originally is text format (text+number - P18001001 for example). Please see attached 2010 db and open the queries and show as Pivot and select the field [Percent] and try to "Show as Percent of Column Total" which is found in the Pivot Table Tools / Tools group on the far right. It is greyed out only in "NotWorks". Right click and click on Group. However, the date grouping feature doesn't always work. The simple rule for the enabling the Group Field feature for dates is: ‎08-09-2020 For now, I can only see dates by month and not days. Find out more about the Microsoft MVP Award Program. I added a new example file without text. Click any cell inside the column with dates. Still no result. To create a grouping, select the items that you want to group, right-click the pivot table, and then choose […] And it's better to switch off Show Formulas flag on ribbon. I am relatively new to pivot tables so guess there is a simple explanation. What I did was I went to the Data Tab > Data Tools > Text To Columns and format the information in date format. I did go to the data table and made sure that my dates are actually dates. I removed the item table to see if that was perhaps the cause, but even after removing it and only using the Data table, the grouping option remains grayed out. Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Outside of the table, I have various calculations, 12 month trend, 3 month trend, current month compared to trends etc. Fully managed intelligent database services. Connect and engage across your organization. For some reason I now see that a Years Field has been automatically created for me and I can not group them because the Group Field is grayed out.