Group Field greyed out in PivotTable?

[FloydPalmer]

I'm wanting to group a date field in my PivotTable by month however the Group Field is greyed out.

I think it meets all criteria (no blank fields, data source is in Date format) but annoyingly, it's not happening for me atm.

Help please!

 

[Drumstick22]

Hi Floyd,

It could possibly be due to Excel formatting your data as text rather than a date. Would you mind posting a sample of the data causing you trouble?

Regards,

D

 

[FloydPalmer]

Hi Floyd,

It could possibly be due to Excel formatting your data as text rather than a date. Would you mind posting a sample of the data causing you trouble?

Regards,

D

A sample of the dates I input - there's more than this.

 

[starl]

The dates have to be actual dates as defined by Excel. If you apply a different date format, does it change to that format? If not - those aren't real dates and you need to convert them.

Note that the format you are showing us is not a standard Excel format. Though, you could have created a custom format.

 

[Drumstick22]

1. Select the entire row containing your dates
2. Press ctrl+f
3. Select the "Replace" tab
4. Type "." (without the quotes) into "Find what:"
5. Type "/" (without the quotes) into "Replace with:"
6. Still with all the cells highlighted right click on one and select "Format Cells"
7. In the "Number" tab select "Date" and choose the date formatting you prefer
8. Try to rerun your pivot table again.

Let me know if this fixes your problem.

D

 

[FloydPalmer]

1. Select the entire row containing your dates
2. Press ctrl+f
3. Select the "Replace" tab
4. Type "." (without the quotes) into "Find what:"
5. Type "/" (without the quotes) into "Replace with:"
6. Still with all the cells highlighted right click on one and select "Format Cells"
7. In the "Number" tab select "Date" and choose the date formatting you prefer
8. Try to rerun your pivot table again.

Let me know if this fixes your problem.

D

Thanks - that worked!

Only issue is I have datasheets of Pivot Tables using the old date format and if I refresh that, the data disappears. So either I remember not to refresh or there is an easier way I could update to the new date format for my PivotTable sheets?

 

[Drumstick22]

Thanks - that worked!

Only issue is I have datasheets of Pivot Tables using the old date format and if I refresh that, the data disappears. So either I remember not to refresh or there is an easier way I could update to the new date format for my PivotTable sheets?

I'm not able to duplicate the error, when I refresh it refreshes with the new date format. I would try two things in this order:

1. "Save-As" a copy of your file in case something goes wrong.
2. Click on your pivot table so that the field list for that table displays on the right side of your Excel sheet.
   • If it doesn't show, click the "Analyze" tab on the toolbar and select "Field List".
3. Remove the field you have for the date section of your table.
4. Add it back in.

If that doesn't work I would try this.

1. Click the "Analyze" tab on the toolbar and select "Change Data Source".
2. Make sure the range your pivot tables are referencing is still correct and click "OK".
3. Refresh again.

Let me know if this helps.

Regards,

D

 

[FloydPalmer]

I'm not able to duplicate the error, when I refresh it refreshes with the new date format. I would try two things in this order:

1. "Save-As" a copy of your file in case something goes wrong.
2. Click on your pivot table so that the field list for that table displays on the right side of your Excel sheet.
   • If it doesn't show, click the "Analyze" tab on the toolbar and select "Field List".
3. Remove the field you have for the date section of your table.
4. Add it back in.

If that doesn't work I would try this.

1. Click the "Analyze" tab on the toolbar and select "Change Data Source".
2. Make sure the range your pivot tables are referencing is still correct and click "OK".
3. Refresh again.

Let me know if this helps.

Regards,

D

Hi,

I've tried option one and two and it doesn't re-categorise as before sadly. I click on Date and click Select All so it highlights all dates, but it says a Pivotable cannot be placed on another PivotTable.

 

[Drumstick22]

Floyd,

I know this is a few days old and I hope you didn't give up. Sorry for not following up. I was referring to the "Analyze" tab on your toolbar in Excel, if you're using Excel 13 and above. If using 2007 I believe it's under the "Options" tab. The "Pivot Table Tools" won't appear until you click on the pivot table.

Regards,

D

 

[FloydPalmer]

Floyd,

I know this is a few days old and I hope you didn't give up. Sorry for not following up. I was referring to the "Analyze" tab on your toolbar in Excel, if you're using Excel 13 and above. If using 2007 I believe it's under the "Options" tab. The "Pivot Table Tools" won't appear until you click on the pivot table.

Regards,

D

Hi D,

I've Excel 13 and have completed the above but still it hasn't worked.