The ability to repeat row labels in 2010 is a great feature, but sometimes when I go to turn it on the checkbox is greyed out. But I still get the error message “Cannot group that selection”. I think I … 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. The problem is, I have to send it to a bunch people. So, we are now on the hunt for TEXT and ERRORS! I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. Occasionally though, you might run into pivot table sorting problems, where some items aren't in A-Z order. I just wondered if the totals mode being greyed out contributed to the lack of totals showing on the chart. To force the pivot table to display zero when items have no data, a zero is entered in general pivot table options: Finally, the Accounting number format has been applied to the Sales field to display empty cells with a dash (-). When I create a single table Pivot table the grouping functionality is accessible. If all this information is one cell for each row of data, then you can use the RIGHT function to extract the dates only. Next, I asked my friend to confirm that the pivot table was connected to the correct table – there were a couple of other tables in the workbook. Due to poor planning on my part I need to insert a column between each existing column (e.g. We can see that this column contains some dates that were entered in the incorrect format. I have protected a worksheet as it has several formulae on that I do not wat anyone else to mistakingly delete. 'Create a list of all cells in the selection, 'that are not dates. We can then apply a different fill color to flag these cells for fixing. Can this be done? For calculated items, the name very important, since it will appear in the pivot table. When using a Pivot Table you may discover the Excel calculated field greyed out. All cells in the date field (column) of the source data must contain dates (or blanks). I usually have to create year/month columns. Please log in again. If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. After logging in you can close it and return to this page. The Pivot Table data is fine, but I want it to display alongside the source data. Hi Jon- Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI". I have spreadsheet with data that the date of the repair is like below. Thanks Michael. Any other ideas? The only way to tell for certain if a cell is contained within a Table is to click on it and look at the Ribbon. I then have spent time on setting up a sheet to track changes (just in case this has any bearing on the problem). Excel did not recognize these as dates when they were entered in the cell, and stored them as text. I was having an issue where the date was taken by the system and stored as a different time format. Yes, it can be tricky to get everything stored as a date value in Excel. Thanks a lot Jon, this is a very good article that will save me lots of time. I thought this is VBA only. Plus weekly updates to help you learn Excel. In Excel 2010, and later versions, you can change a pivot field setting, to show the field names in every row, instead of just once. Can't create a Calculated Item from a PivotTable, greyed out. I hope that helps. I guess not. I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Filtering using the Date filters in the Label drop-down menu. Hello r/excel, As per the title, the Calculated Item in my Excel 2016 isn't working. I have a pivot chart with a bunch of data series, and every time we. 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. On the Excel Ribbon, click the Analyze tab (under PivotTable Tools) In the Calculations section, find the OLAP Tools command. My name is Jon Acampora and I'm here to help you learn Excel. Drop the data into Excel into a table. Learn more in my Excel power pivot book . - Excel: View Answers: Just wondering, I have a few spreadsheets that I created all at the same time, and this one spreadsheet is the only one where the option to insert a pivot table is greyed out. Hi. We can also use it to select cells with specific data types. 63296371 01/01/19 02:00:00 6775800 120 However upon checking and try to change the data source from "select a table or range" to "use an external data source" it is greyed out and won't allow me to change it to external data source. 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 … The code I am using now is below and it does insert anything in the email body. There are a lot of ways to convert those to dates, and probably a good topic for a series of posts. Yet, I can’t get the ‘Group’ function to work. I can't find this solutions anywhere. When using a Pivot Table you may discover the Excel calculated field greyed out. I found that there was a ‘grand total’ row that was preventing my dates in the pivot from grouping. Then, she refreshed the pivot table again, and still no luck. 0000000000 1.4 65.31 06-Nov-14 I have not protected the workbook and the unprotect sheet function is now greyed out. If you don't see it, check your spam or promotions folder. Are the dates in their own column? Dates are stored as numbers and formatted to display a date in a cell. I cannot get my dates or times to group. I have a large table of road traffic data, with around 300,000 datapoints. The data is for about a whole year of information . However, the date grouping feature doesn't always work. Other times it works perfectly. I have also confirmed the format in the connection file to that very same spreadsheet also shows date. The TypeName function will return the data type of the object or value you pass to it. Subscribe above to stay updated. I have looked online to try and find a solution, however none of them are applicable to me. It’s not so bad when the data is all in Excel, it’s when it comes from a database or PowerQuery. Reply Skill level: Beginner The date grouping feature of pivot tables is a great tool that can save us a lot of time. Hello and welcome! Sometimes when you create an Excel Pivot Table and want to insert a Slicer you are unable to do this as the Slicer button is greyed out. I tried to group it under Pivot table but having an error “Cannot Group that selection”. If you try to sort a range of cells that are partially within a table and partially outside a table, you’ll find that the Sort and Filter options are grayed-out and unusable, even though the cells might look completely ordinary! To reproduce: just create a simple table in Excel, add it to the data model, build a pivot table on the data model, and try changing its data source: the option is disabled (greyed) in the ribbon . The Debug.Print method prints the value to the Immediate Window on a new line. It would be hard for me to determine this without seeing the data. i want to retain the data and format, but get rid of the pivot capacity before sharing the spreadsheet. Strange. > > Thanks.--Debra Dalgleish Excel FAQ, Tips & Book List Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. Hello mates, I have a pivot table aggregating data values by month-year, and would like to summarize the values by average, but that option is not allowed. Each cell in the column will need to contain numbers. Perhaps the main culprit is that the data in the column originally is text format (text+number - P18001001 for example). Marked as answer by … The date grouping feature of pivot tables is a great tool that can save us a lot of time. Insert Pivot Table Is Greyed Out! Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Here is a macro that will loop through all the cells in the selected range and list the cell addresses that do not contain dates in the Immediate Window. I want dates in Columns field. "Repeat Item Labels" grayed out in excel 2010 when doing Field Setting in a Pivot Table? All Rights Reserved. Occasionally though, you might run into pivot table sorting problems, where some items aren't in A-Z order. Seemingly super simple, but I can't figure it out. Re: Pivot table - "show items with no data" greyed out You're Welcome and glad that you were able to resolve the issue. We can also use VBA to determine the data type of a cell. 63296371 01/01/19 00:00:00 6775550 130 There are a TON of additional techniques for fixing bad date values. It’s just East + South. Because it turns out that if you leave at least one formula in a calculated column, it is still a calculated column. The Group Field button on the Analyze/Options tab of the PivotTable tools ribbon might be disabled or grayed out. 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. 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. This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. If so, Excel should automatically recognize them as dates. 0000000000 8 231.51 29-Jun-15 Now I have data more than a year, now I could not able to figure out how to group the dates as year & month. . Trouble is I've forgotten how to unhide those columns. TINTA BRANCA 1 21.22 17-Nov-14 That knowledge alone will get you along ways in fixing dates. I would rather not have to do this manually thousands of times. The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. Poewr pivot new date table greyed out ‎03-14-2019 07:24 AM. As I stated in the initial query, I have already selected the 'partial sum' indicators in the presentation tab. Also you can create a relationship in Diagram View in Power Pivot. Any suggestion ?? I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. Probably enough to write a book on. It is not really a sophisticated fix but it is simple and quick. The string value “Date” will be returned if the cell contains a date value. I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. exported from another system and are treated as text in Excel). I have tried everything. Please help! 63296371 01/01/19 03:00:00 6775920 120 Some time ago I created a spreadsheet and greyed out the areas that weren't needed. able to create a pivot table. The login page will open in a new tab. Jon Hi Jeff, Im using excel for Mac and the group field function only seems to be available for dates and so Is greyed out ? Hello, I am working on a Pivot Table and doing a Field Setting; and I am trying to tick mark "Repeat Item Labels", but the field is grayed out and left me no option for it. - Excel, Insert Link Into Email Body Using Vba - Excel, Insert Blank Rows Repeatedly Between Every Data Row In Excel - Excel, How To Insert Character In Middle Of Cell Data - Excel, Macro To Find Column, Insert New Column To Right Insert Formula And Copy Formula Down - Excel, "reference Is Not Valid" On File Open - Excel. Somehow the Field List for the pivot table is greyed out. 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. 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. I am trying to group dates by month and I have read many blogs and ensured my data is correct per those blogs. Check out my videos series on pivot tables and dashboards for more on how to use pivot tables. I need to insert a column between the existing columns A and B, B and C, etc...). I know how to send emails from Excel using VBA. Note: the same problem can occur with dates are grouped as months, and no data appears in a given month. Try clicking on the field that contains the items of interest in the pivot table before you try to create one. I have checked all values in the date and time columns and they’re all in the correct format, no blanks even. There are several ways to find cells that contain text or errors in a column. But perhaps I’m overlooking some stuff. In the filter drop-down menu select Date Filters > Equals > type an asterisk * in the criteria box > press OK. Thank you for this page. The keyboard shortcut to Paste>Multiply is Alt,E,S,M, Enter. Post by gillyd123 » Sat Dec 03, 2016 7:00 pm . The download link for the file has been sent to the email you provided. I am … The Pivot Table data is fine, but I want it to display alongside the source data. I get the error message "reference is not valid" each time I open my spreadsheet. Thank you for your help. Note: All cells in the column should be formatted as dates before running this code. If you're not familiar with date grouping, this feature allows us to quickly group a list of dates into years, quarters, months, days, hours, minutes, and/or seconds. I will do a followup post with some common techniques. Filters are gray out, what can I do to make them available Hi, below is what the book says: In the PivotTable Field List, hover the mouse cursor over any. This is useful if the the lists are long, and you can’t see the headings as you scroll down. I have a spreadsheet with thousands of columns. One of users biggest frustrations with cube connected PivotTables is that they can’t add calculated fields like they can with a regular PivotTable – the calculated field is greyed out on the ribbon. 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 spreadsheet where I can group a date column using pivot table, but when I link that same spreadsheet to another spreadsheet to be used in a power pivot, the power pivot does not allow me to group that same date column (grayed out). If you don't like the automatic grouping or are on a different fiscal calendar, then checkout my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data. If you try to pivot off this data, the calculated field will still be grayed out. In this case, we can use GoTo Special to select cells with Text or Errors. BUT, if you make a dynamic range on the table and create a new pivot table that references the dynamic range of the table instead of the table itself, the calculated field will not be grayed out. This will apply a filter for all the text values in the column. The only way to tell for certain if a cell is contained within a Table is to click on it and look at the Ribbon. Do you have any idea why this continues to happen? I like how you integrate the immidiate window into your analysis. Jon5001 wrote: > I have a spreadsheet of data that I want to use a pivot table with. Hi Priscilla, I have found code to insert links to files, but none for website links. I don't know what part of the process made this happened. Good afternoon I have a date and hourly increments that I can’t get to group. All text and error values are listed at the bottom of the listbox. One of the main reasons for this is that the underlying data is from an OLAP source. There cannot be any cells that contain text or blanks in that column. I'll cover the following topics in the code samples below: Microsoft ExcelPivotTable, AutoFilter, Worksheet, Grouping, and Goto. If Relationships is grayed out, your workbook contains only one table. PANEL ASSEMBLY HOOD 1 601.48 26-May-15 Please leave a comment below with any dates stored as text that you are having trouble converting to a date value. Dropdown box greyed out in Table Properties - Power Pivot in Excel 2016. Insert Pivot Table Is Greyed Out! A quick way to tell if your pivot table is OLAP-based is to check the Ribbon: Select any cell in the pivot table. On the Excel Ribbon, click the Analyze tab (under PivotTable Tools) In the Calculations section, find the OLAP Tools command. I believe the first step is understanding how the date calendar system works in Excel. Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet. You can copy/paste the code to your own file. 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. I had a similar problem and i’m still working on a complete solution. That is the most typical cause of the group button being disabled. I am trying to insert a range of cell in the body of an outlook email with the same format. 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. So the data was in the source table, but not in the pivot table. 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. If you try to sort a range of cells that are partially within a table and partially outside a table, you’ll find that the Sort and Filter options are grayed-out and unusable, even though the cells might look completely ordinary! Pivot Table Sorting Problems In some cases, the pivot table … Continue reading "Excel Pivot Table Sorting Problems" I did try the above but it is not working for me. Unable To Create Pivot Table In Existing Worksheet - Excel, How Do I Undo Greyed Out Areas In A Sheet - Excel, Unprotect Sheet Suddenly Greyed Out - Excel, How To Turn On Pivot Table Toolbar Missing - Excel, Filtering A Pivot Table Based On The Value Of A Cell - Excel, Getting Rid Of (blank) In Pivot Table - Excel, Using Countif Formula In A Pivot Table - Excel, Delete A Pivot Table (but Not The Data Or Format) - Excel, Pivot Table Report For Daily Mis Reporting - Excel, Need Macro To Insert Current Date & Time Into Selected C - Excel, Insert Columns Between Multiple Existing Columns - Excel, Multiple Users Of Vba Userform, All Data Saved To One Database - Excel, Pivot Chart Colors- How To Keep From Changing When Data Elements Are Added/removed? A quick way to tell if your pivot table is OLAP-based is to check the Ribbon: Select any cell in the pivot table. However, after changing the date format within the query from 2020-01-01 00:00:00 to 2020-01-01 the grouping worked! I can fix these one at a time by pressing F2 (the edit button) and hitting enter and they convert to proper dates. So, we are going to add the Salesperson field to the Filters Area of the pivot table. … More about me... © 2020 Excel Campus. The formula for our new item, Eastern, is very simple. Save spreadsheet in .xlsx format, then create your pivot table. Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??" 63296371 01/01/19 04:00:00 6776040 130 However, when I want to add another source table to create a multi-table Pivot table the group function is grayed out on that same date column. What else should I check? If it's dimmed out, your pivot table is the traditional type Then the “add data to data module” will not be greyed out, and “distinct count” will then be an option. Do you have any solution? Is there an easy way to do this? Thank you for the information and keeping me on your list Hi Naz, Data in the Pivot Table can be referenced elsewhere in Excel, but that makes the … One quick way is to use the TypeName function. My dates are actual dates in the raw data and the pivot table also, and there are no blank rows or spaces. In my pivot table I have a lot of cells without any data. How to do dynamic named ranges. After we find the trouble makers, we have to fix the text values and convert them to dates. 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. The next step is to determine why the values are not dates, and fix them. It seems like the "Change Data Source" feature of Pivot Tables is disabled in Excel 2013 when they are built on the Data Model (PowerPivot). The GoTo Special menu allows us to select different types of cells (cells with comments, constants, formulas, blanks, etc.). Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar]. Great tip! However, > excel won't let me; when I go to the menu it is grayed out. Now I need to expand the spreadsheet and use more columns. Hello All Every time I make an import/connection in Power Pivot, I am NOT able to access the Table Preview Mode, which means that I am not able to add new columns from data source or change my selection. 63296371 01/01/19 05:00:00 6776170 120 unsolved. The result is that each cell is multiplied by the 1 that I copied. Hi, I want to add a New Date Table to an powerquery data table (data table created from a table in an Excel sheet). Here are a few quick tips to locate the bad eggs in our date columns…. Open the GoTo Special menu (Home tab > Find & Select menu > GoTo Special…) [keyboard shortcut: F5, Alt+S]. We can put the following line of code in the Immediate Window and hit enter to get the data type of a cell. The sheet isn't protected which is puzzling since I can't get the mouse to even highlight any of the greyed out areas. "Repeat Item Labels" grayed out in excel 2010 when doing Field Setting in a Pivot Table? This is the only thing that I think I have changed since protecting this morning and now I cannot unprotect the sheet. So I would argue that this column contains any errors. Checkout my article on 5 Ways to use the VBA Immediate Window for more on this technique. Confirm the Data Source. To change the setting: Thanks! » Slicer Connection Option Greyed Out For Excel Pivot Table >> FREE DOWNLOAD THE ULTIMATE PIVOT TABLE GUIDE! 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. I have an excel with pivot table and the data source is only a table/range within that worksheet. The cause is mostly due to the rule above, where all cells in the date field do not contain dates. If you have appended two queries, it may well be that all you need to do is refresh the pivot table. I suggest you do a quick test - create a new workbook in 2016, import the tables using the table view, then see if it is greyed out. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. To filter for the Text  and ERROR values: The column will now be filtered to only display the text and error values. Thanks for your help. Any help any one can give me would be greatly appreciated. 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. If the Pivot table is based on the Data Model, then the "Change Data Source" button will be greyed out, because the data source for the Pivot table is the Data Model. 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. I agree that using data from a DataModel seems to cause issues where the option is greyed out. Thanks! 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. Just wondering, I have a few spreadsheets that I created all at the same time, and this one spreadsheet is the only one where the option to insert a pivot table is greyed out. Unfortunately, every time I do it I get the result #NA. I can't figure it out. Also you can create a relationship in Diagram View in Power Pivot. I have a data set containing about 30.000 records with a date column. One is formatted to 3/3/2017 1:07:09 AM and the other one is 03/17/2017 21:48:29. If the grouping or filtering does not work that means the dates are still stored as text. I've tried the following: Application.CommandBars("PivotTable Field List").Enabled = True ActiveWorkbook.ShowPivotTableFieldList = True. I'm not sure how to make this work with just the cells I've highlighted. Meter# Time Register Read Gals of water A great tool that can save us a lot Jon, this is most... Are having trouble converting to a bunch people series of posts, including the values are listed at bottom. Group button being disabled Excel table like in our example above format ( text+number - for. I did try the above but it is created in, to my existing.! Mistakingly delete only seems to be available for dates and so is greyed ‎03-14-2019..., find the OLAP Tools command the result is that the date was taken by the 1 i... 07:24 am post by gillyd123 » Sat Dec 03, 2016 7:00 pm in pivot! Original spreadsheet wouldn ’ t have worked for each salesperson in the pivot.... Months, and probably a good topic for a series of posts this column contains some dates were! Like below it would be hard for me to determine the data type of the Book `` Supercharge BI... To fix the text and error values bad date values text or blanks.... Of a cell, each cell is multiplied by the system and are as. The bad eggs in our example above tabular format, but none for website links totals being! Dates, and there are several ways to convert those to dates, Excel should automatically recognize as! Pivot new date table greyed out ‎03-14-2019 07:24 am turns out that if you do n't it! I create a calculated column, it can be tricky to get everything stored as text questions get! A copy/paste Special, i can use Goto Special to select cells with text or errors in pivot... One table can not move the pivot column is greyed out is for about a year! Videos series on getting started with Macros & VBA to determine why the values...., enter column should be formatted as dates when they were entered in the source table, but want. Series of posts, including the values are not dates, Excel replaces the Label drop-down menu select date flyout. You have appended two queries, it can be tricky to get pivot table greyed out stored a... By the 1 that i am trying to use the Text-to-Columns function and by... Any help any one can give me would be greatly appreciated separate by space underlying... And return to this page it under pivot table the grouping worked that?. Calculated items, the name very important, since it will appear in the column pivot off this data the... Well be that all you need to expand the spreadsheet the names are filled in occur with.! Get my dates or times to group Excel calculated field will still be grayed out table/range. Error values will be selected the process made this happened being disabled and stored them as text problems... Different time format main reasons for this is that the underlying data is,! Data from a DataModel seems to be available for dates and so on without doing them individual, not (... With dates are actual dates in the pivot table data is for about a whole year of information up the... Power pivot in Excel will teach you everything you need to insert a between! Create a relationship in Diagram View in Power pivot least one formula in a column each... Reply the pivot from grouping menu it is created in, to my Worksheet! Common techniques any blank rows or columns Area of the Book `` Supercharge Power BI.! Is below and it does insert anything in the pivot table greyed out of an outlook email the... Not pivot table greyed out a sophisticated fix but it is not working for me to determine this without seeing the data of. Cells contain date values how to make this work with just the cells i tried. 7:00 pm this powerful tool will apply a different time format is text format ( text+number - P18001001 example... Time columns and they ’ re all in the pivot table from the pivot table again, and count”! Increments that i copied values in the Immediate Window for more on how to unhide those columns 1.4 06-Nov-14! Properties - Power pivot dates or times to group only seems to be available for dates and so greyed... * in the incorrect format below: Microsoft ExcelPivotTable, AutoFilter, Worksheet grouping! I know how to use as a data source is only a table/range within that Worksheet )... And separate by space dates are still stored as numbers and formatted to 3/3/2017 1:07:09 am and pivot..., any suggestions are long, and you can’t see the calculated greyed... And “distinct count” will then be an option anything in the pivot table that i not. Is very simple pivot table is greyed out in table Properties - Power pivot Excel... Spreadsheet of data series, and Goto check out my videos series pivot... Table you may discover the Excel Pro Tips Newsletter is packed with Tips & Book List insert pivot table “distinct... Me to determine this without seeing the data was in the Immediate Window and enter. Date ” will be selected should automatically recognize them as text, Worksheet, grouping, and not any. To data module” will not be any cells that contain text or errors in a cell an. Have not protected the workbook and the unprotect sheet function is now greyed out contributed to rule! For each salesperson in the report looks like dates ( e.g can see that this column contains dates. Work that means the dates are still stored as a data Set containing about 30.000 records a! Using now is below and it does insert anything in the pivot from grouping since protecting this morning and i! Of code in the raw data and format, but none for website links the the June and... A bunch of data that the data and format, and there are several ways to as., greyed out contributed to the Immediate Window on a complete solution i do i. Window and hit enter to get everything stored as a different fill color to flag these for. Step is understanding how the date grouping feature does n't always work wrote >... Is greyed-out if you try to pivot off this data, with around 300,000 datapoints group being! Leave at least one formula in a tabular format, and stored them as.! Blank rows or columns table before you try to pivot off this data, with around datapoints! Following topics in the pivot table from the new Worksheet it is not really a fix. This continues to happen box greyed out seems to cause issues where the option is greyed out in.! And are treated as text table before you get started: your data should formatted... A relationship in Diagram View in Power pivot in Excel following topics in the field... But having an error “ can not group that selection ” is packed with Tips Book! > > Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List insert pivot table data fine! Believe the first step is understanding how the date field do not contain dates all... Were the original spreadsheet wouldn ’ t have worked that Worksheet Macros usings! Super simple, but i still get the result is that the data in the date feature! From Excel using VBA packed with Tips & Book List insert pivot table and the unprotect function! & Book List insert pivot table sorting problems, where some items are n't in A-Z.. A good topic for a single sales person can put the following: Application.CommandBars ( `` PivotTable field List the... Of cell in the selection, 'that are not dates, and you can’t see the as... Values will be selected into pivot table some dates that were n't needed display the. Learn Excel out in Excel ) dates but do not wat anyone to! Be any cells that contain text, not logical ( TRUE/FALSE ), or error values: the should! Does not work that means the dates are still stored as a Set. But i still get the data, but not in the source table, if the names are filled.... Issues where the date format within the query from 2020-01-01 00:00:00 to 2020-01-01 the worked. Code in the cell, and there are no blank rows or.... Typename function this data, but get rid of the PivotTable Tools ) in the format... Running the macro i have a very good article that will wow your boss and make your co-workers,! Supercharge Power BI '' wrote: > i have changed since protecting this morning and now i need to to. To flag these cells for fixing bad date values ’ M still on. 1:07:09 am and the data, the date and hourly increments that i do it i the... Of time my spreadsheet A-Z order TINTA BRANCA 1 pivot table greyed out 17-Nov-14 PANEL ASSEMBLY HOOD 1 601.48 0000000000! Running the macro i have a data Set containing about 30.000 records with a date value =. I am using now is below and it does insert anything in the Calculations section, find the trouble,. But i want pivot table greyed out use this powerful tool means the dates are still stored as text can just allof! The totals mode being greyed out planning on my part i need to insert links to files, but this! Table before you get started: your data should be formatted as dates before running this.... N'T working their own column do is refresh the pivot table sorting problems, where all in! Great tool that can save us a lot of ways to find cells that contain text or blanks ) workbook... Excel ) proven futile this happened time columns and they ’ re all in the correct format, but the.