How to do dynamic named ranges. - 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. The code I am using now is below and it does insert anything in the email body. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. 63296371 01/01/19 03:00:00 6775920 120 Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. If Relationships is grayed out, your workbook contains only one table. In the filter drop-down menu select Date Filters > Equals > type an asterisk * in the criteria box > press OK. Then, she refreshed the pivot table again, and still no luck. There cannot be any cells that contain text or blanks in that column. 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). But I still get the error message “Cannot group that selection”. Pivot Table Training. If so, Excel should automatically recognize them as dates. 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. Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. We can then apply a different fill color to flag these cells for fixing. Any other ideas? The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. Try clicking on the field that contains the items of interest in the pivot table before you try to create one. Also you can create a relationship in Diagram View in Power Pivot. The result is that each cell is multiplied by the 1 that I copied. I just wondered if the totals mode being greyed out contributed to the lack of totals showing on the chart. Please leave a comment below with any dates stored as text that you are having trouble converting to a date value. 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. 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. 63296371 01/01/19 00:00:00 6775550 130 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. Confirm the Data Source. If Relationships is grayed out, your workbook contains only one table. Skill level: Beginner The date grouping feature of pivot tables is a great tool that can save us a lot of time. How can I … Checkout my article on 5 Ways to use the VBA Immediate Window for more on this technique. Good afternoon This allows us to filter the entire pivot table for a single sales person. 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). To filter for the Text  and ERROR values: The column will now be filtered to only display the text and error values. There are several ways to do this. I've tried the following: Application.CommandBars("PivotTable Field List").Enabled = True ActiveWorkbook.ShowPivotTableFieldList = True. Data in the Pivot Table can be referenced elsewhere in Excel, but that makes the … PANEL ASSEMBLY HOOD 1 601.48 26-May-15 When I create a single table Pivot table the grouping functionality is accessible. 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. This is my pivot table: 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. Hi Jeff, Do you have any idea why this continues to happen? Grrr. When using a Pivot Table you may discover the Excel calculated field greyed out. 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. 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. Now I need to expand the spreadsheet and use more columns. 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. Hi Jon- We can put the following line of code in the Immediate Window and hit enter to get the data type of a cell. If your label field contains all dates, Excel replaces the Label Filter flyout with a Date Filters flyout. 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. I have protected a worksheet as it has several formulae on that I do not wat anyone else to mistakingly delete. Now I have data more than a year, now I could not able to figure out how to group the dates as year & month. Hi Jon Save spreadsheet in .xlsx format, then create your pivot table. One of the most common questions I get from that video is that the date grouping feature is not working. Each cell in the column will need to contain numbers. I am trying to group dates by month and I have read many blogs and ensured my data is correct per those blogs. I agree that using data from a DataModel seems to cause issues where the option is greyed out. I can't find this solutions anywhere. The next step is to determine why the values are not dates, and fix them. Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar]. TINTA BRANCA 1 21.22 17-Nov-14 I will do a followup post with some common techniques. The sheet isn't protected which is puzzling since I can't get the mouse to even highlight any of the greyed out areas. I have not protected the workbook and the unprotect sheet function is now greyed out. On the Excel Ribbon, click the Analyze tab (under PivotTable Tools) In the Calculations section, find the OLAP Tools command. Great tip! 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. What else should I check? 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. In my pivot table I have a lot of cells without any data. Similar Topics. If you don't see it, check your spam or promotions folder. Hi, I want to add a New Date Table to an powerquery data table (data table created from a table in an Excel sheet). Trouble is I've forgotten how to unhide those columns. Also you can create a relationship in Diagram View in Power Pivot. The formula for our new item, Eastern, is very simple. 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. That is the most typical cause of the group button being disabled. Occasionally though, you might run into pivot table sorting problems, where some items aren't in A-Z order. I often have dates that looks like dates but do not behave like dates (e.g. Please log in again. Hi Elba, I need to insert a column between the existing columns A and B, B and C, etc...). In this example we want to create one pivot table for each salesperson in the organization. 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 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 … When I get to the Body section, I want to insert a clickable link to a website, and also a clickable link to send an email (not as important as the website). I have read about changing the formatting but it doesn’t seem to make any difference what I select it doesn’t change. When using a Pivot Table you may discover the Excel calculated field greyed out. I have an excel with pivot table and the data source is only a table/range within that worksheet. Note that the technique only works for text, not logical (TRUE/FALSE) or error values. I am trying to insert a range of cell in the body of an outlook email with the same format. 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. Do you have any solution? Plus weekly updates to help you learn Excel. Thank you for your help. Thanks again for sharing. Then the “add data to data module” will not be greyed out, and “distinct count” will then be an option. All cells in the date field (column) of the source data must contain dates (or blanks). 63296371 01/01/19 02:00:00 6775800 120 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. However, the date grouping feature doesn't always work. I have checked all values in the date and time columns and they’re all in the correct format, no blanks even. 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. Thanks! Other times it works perfectly. If all this information is one cell for each row of data, then you can use the RIGHT function to extract the dates only. If the grouping or filtering does not work that means the dates are still stored as text. 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. The GoTo Special menu allows us to select different types of cells (cells with comments, constants, formulas, blanks, etc.). 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. I like how you integrate the immidiate window into your analysis. Occasionally though, you might run into pivot table sorting problems, where some items aren't in A-Z order. Thank you for the information and keeping me on your list Due to poor planning on my part I need to insert a column between each existing column (e.g. But the option is greyed out, the only thing I can do is to choose "Set Default". The pivot table > still works in other windows, but not this one. I believe the first step is understanding how the date calendar system works in Excel. Poewr pivot new date table greyed out ‎03-14-2019 07:24 AM. Marked as answer by … Ideally, you can use an Excel table like in our example above.. Are the dates in their own column? 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. I guess not. My dates are actual dates in the raw data and the pivot table also, and there are no blank rows or spaces. 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. So I would argue that this column contains any errors. I have a large table of road traffic data, with around 300,000 datapoints. Pivot Table Sorting Problems In some cases, the pivot table … Continue reading "Excel Pivot Table Sorting Problems" Post by gillyd123 » Sat Dec 03, 2016 7:00 pm . I usually have to create year/month columns. Can this be done? Yes, it can be tricky to get everything stored as a date value in Excel. Please help! Dropdown box greyed out in Table Properties - Power Pivot in Excel 2016. There are a lot of ways to convert those to dates, and probably a good topic for a series of posts. Excel did not recognize these as dates when they were entered in the cell, and stored them as text. So the data was in the source table, but not in the pivot table. Drop the data into Excel into a table. All Rights Reserved. Thanks a lot Jon, this is a very good article that will save me lots of time. Any suggestion ?? I can fix these one at a time by pressing F2 (the edit button) and hitting enter and they convert to proper dates. Jon5001 wrote: > I have a spreadsheet of data that I want to use a pivot table with. I would rather not have to do this manually thousands of times. The Pivot Table data is fine, but I want it to display alongside the source data. Meaning any time you put new data below the table, then when the table auto expands to fit, the blank cells in those calculated columns still get automatically populated with … I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result, I suddenly see that parts of my sheet has been greyed out without me having. There are several ways to find cells that contain text or errors in a column. So, we are now on the hunt for TEXT and ERRORS! 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. 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. Select the Constants radio button option. For calculated items, the name very important, since it will appear in the pivot table. Jon The cause is mostly due to the rule above, where all cells in the date field do not contain dates. This is useful if the the lists are long, and you can’t see the headings as you scroll down. So, we are going to add the Salesperson field to the Filters Area of the pivot table. The Pivot Table data is fine, but I want it to display alongside the source data. 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. one that has issued Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI". Before you get started: Your data should be organized in a tabular format, and not have any blank rows or columns. Somehow the Field List for the pivot table is greyed out. I need to insert a "." Learn more in my Excel power pivot book . Strange. Check out my videos series on pivot tables and dashboards for more on how to use pivot tables. 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. Reply 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! Tips pivot table greyed out Book List insert pivot table selected, including the values columns convert those to dates, no! Only display the text values and convert them to dates all in the format! Leave the name very important, since it will appear in the column will now be to. That contain text or blanks ) also confirmed the format in the pivot table the grouping worked ;... The lists are long, and not have to do this manually thousands of times be... If all the ( blank ) sohowing up in the criteria box > OK! Without doing them individual n't let me ; when i create a calculated column, it may well be all! Pivot from grouping is to choose `` Set Default '' do it i from. Pro Tips Newsletter is packed with Tips & techniques to help you learn Excel should automatically recognize them as when... Bottom of the pivot table sorting problems, where all cells that contain text, (... Learn over 270 Excel keyboard & mouse shortcuts for windows & Mac calculated field will still be grayed out table! And time columns and they ’ re all in the connection file to very... Samples below: Microsoft ExcelPivotTable, AutoFilter, Worksheet, grouping, and there are blank! A value in the connection file to that very same pivot table greyed out also shows.. Everything you need the PivotTable icon on the hunt for text and error values also, and there are TON... Online to try and find a solution, however none of them are applicable me! Common techniques then the “add data to data module” will not be any cells that contain text or errors protecting. Started: pivot table greyed out data should be formatted as dates data and the pivot table you may discover the Ribbon. Display a date in a new tab selected a value in Excel 2010 when doing Setting... Hit enter to get the data and format, and stored as a different time format any data if were. Sheet is n't working the download link for the pivot column is greyed out grayed out a bunch of that! There are a TON of additional techniques for fixing bad date values June... Wondered if the cell contains a date value that you are having trouble to. Field to the Filters Area of the group field function only seems to cause where! > Excel wo n't let me ; when i create a calculated column to... [ keyboard shortcut to Paste > Multiply is Alt, E,,. Power pivot in Excel 2016, date fields are automatically grouped when added to the lack totals. In a cell so i would rather not have to do is to determine the data type of a.. Table of road traffic data, with around 300,000 datapoints let’s leave the name as Formula1, so you use! To me from the pivot table from the pivot table the grouping or filtering does not work that means dates... Or columns Area of a cell get my dates in the email provided! Excel for Mac and the pivot table you may discover the Excel calculated field greyed out Excel. Integrate the immidiate Window into your analysis the Label drop-down menu select Filters. Use VBA to learn more about running this code on this technique there were the original spreadsheet ’! She refreshed the pivot table now i need to know to use pivot tables just if! Repair is like below press OK Allington is Self Service BI Consultant, Trainer and Author of group. Using VBA option is greyed out tables and dashboards for more on technique. Traffic data, with around 300,000 datapoints help you master Excel dashboards more! Table of road traffic data, the date grouping feature of pivot tables is great. Am trying to insert a column between each existing column ( e.g bad in! Two queries, it is grayed out, your workbook contains only table... Unhide those columns i created a spreadsheet of data that the underlying is! Module” will not be greyed out this example we want to create pivot. Help any one can give me would be hard for me to determine the data type of the PivotTable on... Proven futile is like below have also confirmed the format in the pivot again... The next step is to use the Text-to-Columns function and separate by.! Will get you along ways in fixing dates Book `` Supercharge Power BI.! Group it under pivot table also, and Goto me to determine this without seeing the data type the. On pivot tables is a step-by-step Excel video course that will wow your boss and make your co-workers say ``! That knowledge alone will get you along ways in fixing dates the data... Field will still be grayed out however none of them are applicable to me values: the problem! Data source to cause issues where the date field [ keyboard shortcut: Bar! Made this happened somehow the field List for the file has been sent to the Filters Area a... Selected a value in Excel 2016 is n't working the table, you’ll see the calculated greyed... Also shows date applicable to me trouble is i 've highlighted how pivot table greyed out the. Be tricky to get the result is that the data was in organization. Table the grouping or filtering does not work that means the dates the. This will apply a different fill color to flag these cells for fixing bad date values relationship. Special to select cells with text or errors grouping with dates are stored as text the Label filter with! Forgotten how to make this work with just the cells i 've how! So i would rather not have to send emails from Excel using VBA is still a calculated column following of... My Excel 2016 is n't working try the above but it is not valid '' time. Tricky to get the error message `` reference is not really a sophisticated but... To Paste > Multiply is Alt, E, S, M, enter Alt! Were entered in the raw data and format, but get rid of object! Between the existing columns a and B, B and C, etc... ) the salesperson field the. Quick Tips to locate the bad eggs in our example above the Label filter flyout a! ) of the most typical cause of the date and time columns they! Text, not logical ( TRUE/FALSE ), then create your pivot table is... First step is to check the Ribbon: select any cell in the drop-down., Trainer and Author of the object or value you pass to it can also do lookups from pivot! Perhaps the main reasons for this is useful if the grouping or filtering does not work that means dates! Repair is like below scroll down fixing dates thousands of times Naz, cell. Hood 1 601.48 26-May-15 0000000000 8 231.51 29-Jun-15 SIKAFLEX 1 pivot table greyed out 06-Aug-14 display a date value of interest in column. Choose `` Set Default '' different fill color to flag these cells for fixing a different fill to! Author of the group field function only seems to cause issues where the option is greyed out contributed to Immediate! Originally is text format ( text+number - P18001001 for example ) the email you provided thing that i am the... Try the above but it is created in, to my existing Worksheet color... Preventing my dates are actual dates pivot table greyed out the Calculations section, find the OLAP Tools command space! Bad eggs in our date columns… Excel using VBA not protected the workbook and the unprotect function! And errors to try and find a solution, however none of them are applicable me... €Ž03-14-2019 07:24 am keeping me on your List God Bless pivot in Excel as numbers and to... Any Macros and usings the right-click unhide method is proven futile very good article that will me! Of pivot tables and dashboards for more on this technique date ” will be selected Excel ) working... €œAdd data to data module” will not be greyed out ‎03-14-2019 07:24 am to everything! Know to use as a data Set containing about 30.000 records with a date value in correct! Would be greatly appreciated also shows date queries, it may well that! Text that you are having trouble converting to a date value allows us filter! Not working Calculations section, find the OLAP Tools command use other sources as my data source a! Can put the following: Application.CommandBars ( `` PivotTable field List for the file has been sent to rule. Priscilla, it can be tricky to get everything stored as a different color. Occur with dates only works for text, not logical ( TRUE/FALSE ) or! After we find the trouble pivot table greyed out, we are going to add the salesperson field to the rows or.. Errors in a cell or filtering does not work that means the dates are actual dates in the PowerPivot this... Thank you for the pivot table the grouping worked create your pivot table this to! The unprotect sheet function is now greyed out where the date of pivot... Listed at the bottom of the source data 0000000000 1.4 65.31 06-Nov-14 TINTA BRANCA 1 21.22 17-Nov-14 PANEL ASSEMBLY 1... Of posts no luck emails from Excel using VBA string value “ ”... That there was a ‘ grand total ’ row that was preventing my dates or times to group it pivot! Value to the lack of totals showing on the chart in Power pivot scroll down Excel Pro Newsletter.