Why the Pivot Table Group Field Button is Disabled for Dates - Excel Campus
14

Why the Pivot Table Group Field Button is Disabled for Dates

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.

Skill level: Beginner

pivot-table-group-field-button-disabled-not-working-for-dates

The date grouping feature of pivot tables is a great tool that can save us a lot of time.  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.

However, the date grouping feature doesn’t always work.  The Group Field button on the Analyze/Options tab of the PivotTable tools ribbon might be disabled or grayed out.  Let’s take a look at why this happens.

The Simple Rule for Grouping Dates in Pivot Tables

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).  If there are any cells in the date field of the source data that contain text or errors, then the group feature will NOT work.

all-cells-must-contain-date-values-in-pivot-table-source-data-date-columns

I explain how to create date groupings in video #3 of my 3-part video series on pivot tables and dashboards.

Turn Data into a Dashboard with Pivot Tables

One of the most common questions I get from that video is that the date grouping feature is not working.  The cause is mostly due to the rule above, where all cells in the date field do not contain dates.

So, we are now on the hunt for TEXT and ERRORS! 🙂

Note: In Excel 2016, date fields are automatically grouped when added to the rows or columns area of a pivot table.  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.

3 Ways to Find Text or Errors in a Column

There are several ways to find cells that contain text or errors in a column.  Here are a few quick tips to locate the bad eggs in our date columns…

The Filter Drop-down Menu Groups Dates

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.

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.  All text and error values are listed at the bottom of the listbox.

text-and-error-values-are-listed-below-date-groups-in-the-filter-drop-down-menu

We can see that this column contains some dates that were entered in the incorrect format.  Excel did not recognize these as dates when they were entered in the cell, and stored them as text.

To filter for the Text  and ERROR values:

  1. Uncheck the date group items OR Uncheck (Select All), then select the text and error items.
  2. Press OK.

The column will now be filtered to only display the text and error values.  The next step is to determine why the values are not dates, and fix them.

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.

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.  In the filter drop-down menu select Date Filters > Equals > type an asterisk * in the criteria box > press OK.

filter-for-asterisk-to-view-all-text-items-in-date-column

This will apply a filter for all the text values in the column.  Note that the technique only works for text, not logical (TRUE/FALSE) or error values.

Select Text and Error Cells with the GoTo Special Menu

The GoTo Special menu allows us to select different types of cells (cells with comments, constants, formulas, blanks, etc.).  We can also use it to select cells with specific data types.

goto-special-menu-to-select-text-error-and-logical-values

In this case, we can use GoTo Special to select cells with Text or Errors.  Here are the instructions:

  1. Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar].
  2. Open the GoTo Special menu (Home tab > Find & Select menu > GoTo Special…) [keyboard shortcut: F5, Alt+S]
  3. Select the Constants radio button option.
  4. Uncheck the Numbers checkbox.
  5. Press OK.

All cells that contain text, logical (TRUE/FALSE), or error values will be selected.  We can then apply a different fill color to flag these cells for fixing.

Find Date Cells with a Macro (VBA)

We can also use VBA to determine the data type of a cell.  There are several ways to do this.  One quick way is to use the TypeName function.

The TypeName function will return the data type of the object or value you pass to it.  We can put the following line of code in the Immediate Window and hit enter to get the data type of a cell.

<pre>?TypeName (ActiveCell.Value)</pre>

The string value “Date” will be returned if the cell contains a date value.

vba-typename-function-returns-the-object-or-data-type

Note: All cells in the column should be formatted as dates before running this code.

Checkout my article on 5 Ways to use the VBA Immediate Window for more on this technique.

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.  You can copy/paste the code to your own file.

Sub Check_Cell_DataType()
'Create a list of all cells in the selection
'that are not dates.  Cells must be formatted
'as dates first.

Dim c As Range

    'Loop through all cells in the selected range
    For Each c In Selection.Cells
        'Determine if the cell contains a date
        If TypeName(c.Value) <> "Date" Then
            'Add the cell address to the Immediate Window
            Debug.Print c.Address
        End If
    Next c

End Sub

The Debug.Print method prints the value to the Immediate Window on a new line.  Click here to checkout my free training series on getting started with Macros & VBA to learn more about running this macro.

Fixing the Dates Stored as Text

After we find the trouble makers, we have to fix the text values and convert them to dates.  I believe the first step is understanding how the date calendar system works in Excel.  Dates are stored as numbers and formatted to display a date in a cell.  That knowledge alone will get you along ways in fixing dates.

There are a TON of additional techniques for fixing bad date values.  Probably enough to write a book on.  Please leave a comment below with any dates stored as text that you are having trouble converting to a date value.  I will do a followup post with some common techniques.

 

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 14 comments
Marquelle - August 24, 2017

Thank you for this page. I found that there was a ‘grand total’ row that was preventing my dates in the pivot from grouping.

Thank you,
MW

Reply
Roman Shalagin - June 26, 2017

Thanks a lot Jon, this is a very good article that will save me lots of time.

I like how you integrate the immidiate window into your analysis. I thought this is VBA only.

Reply
Naz - March 10, 2017

Hi Jon
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.

Im using excel for Mac and the group field function only seems to be available for dates and so Is greyed out ?

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?

Any idea ?

Thanks
Naz

Reply
    Jon Acampora - March 13, 2017

    Hi Naz,
    Each cell in the column will need to contain numbers. There cannot be any cells that contain text or blanks in that column. That is the most typical cause of the group button being disabled. I hope that helps.

    Reply
Priscilla Templeton - March 1, 2017

Hi Jon-
I am trying to group dates by month and I have read many blogs and ensured my data is correct per those blogs. But I still get the error message “Cannot group that selection”. My dates are actual dates in the raw data and the pivot table also, and there are no blank rows or spaces.

I have tried everything. Do you have any idea why this continues to happen?

Thanks.
Priscilla

Reply
    Jon Acampora - March 6, 2017

    Hi Priscilla,
    It would be hard for me to determine this without seeing the data. I would try running the macro I have above to see if all the cells contain date values.

    Reply
Michael - October 12, 2016

I often have dates that looks like dates but do not behave like dates (e.g. exported from another system and are treated as text in Excel). I can fix these one at a time by pressing F2 (the edit button) and hitting enter and they convert to proper dates. 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. The result is that each cell is multiplied by the 1 that I copied.

It is not really a sophisticated fix but it is simple and quick.

Reply
Stephen Wright - September 30, 2016

It’s not so bad when the data is all in Excel, it’s when it comes from a database or PowerQuery. Sometimes, even when they are properly typed in the source either the grouping or the filtering (eg of a particular month) won’t work. I usually have to create year/month columns. Other times it works perfectly.

Reply
    Jon Acampora - October 4, 2016

    Yes, it can be tricky to get everything stored as a date value in Excel. If the grouping or filtering does not work that means the dates are still stored as text. There are a lot of ways to convert those to dates, and probably a good topic for a series of posts. Thanks

    Reply
Elba - September 30, 2016

Jon
Good afternoon
I have spreadsheet with data that the date of the repair is like below.
The data is for about a whole year of information . Is there w way that I can just get allof the the June together and so on without doing them individual.
I did try the above but it is not working for me.

0000000000 1.4 65.31 06-Nov-14
TINTA BRANCA 1 21.22 17-Nov-14
PANEL ASSEMBLY HOOD 1 601.48 26-May-15
0000000000 8 231.51 29-Jun-15
SIKAFLEX 1 10.4 06-Aug-14

Reply
    Jon Acampora - October 4, 2016

    Hi Elba,
    Are the dates in their own column? If so, Excel should automatically recognize them as dates.

    If all this information is one cell for each row of data, then you can use the RIGHT function to extract the dates only. Or you can use the Text-to-Columns function and separate by space.

    I hope that helps.

    Reply
wilson - September 29, 2016

Thank you for the information and keeping me on your list
God Bless

Reply

Leave a Reply: