Months Grouping In Columns Area Disappers After Pivot Table Refresh

Hi Cliff,

I was afraid of that. Very disappointing to say the least. I'm at a point now where I can longer spend any more time on this issue. I have to go in a different direction. I bought my copy of Office 365 at Best Buy. I was under the impression that once you purchase 365 Microsoft periodically automatically updates for you. If that is not true, is there anyone who I can contact to update my version of Excel? By the way, in my research of this issue (limited information), I stumbled on one piece of information that might be helpful to someone else experiencing this issue. Make sure that your column(s) with the dates are formatted for Date. In some cases, this will correct the issue. In my case the column containing the dates was already formatted for Date. At any rate, I'd like to thank you for all of the great help, patience and time that you spent on my issue. I'm sorry to take up a lot of your time. It definitely was a learning experience for me. So again, thank you.

Best regards,

John

Hi Cliff,

Last time that I will bother you. Thanks for your patience. You are right, the way you do delete the eight rows preserves the Months field. Your method deletes them from the entire Excel Table leaving no blank rows, thus preserving the Months field. I delete the rows with the Delete key. My method leaves eight blank rows which is what I want in order to get a point across to my students. I want to show them what a Pivot Table and Pivot Chart look like with blanks in the data source. Whether I delete with the Delete key from my courseware or your mockup the results are exactly the same. In order to continue with the module, I have to drag the Expense Date field back to the Pivot Table Columns area in order to get the Months field back. That to me makes no sense. Why would Excel drop the Months field because the data source has blank rows in it? Give it a try.

Thanks,

John

Hi Cliff,

Sorry to bother you again, but I tried the steps on your mockup and got the same results. Months disappears from the columns area after deleting from the data source and refreshing the Pivot Table. So, I'm thinking that I'm having a problem with my version/copy of Excel. Any suggestions?

John

Only other thing that I can think of is that you're doing the delete of the underlying data incorrectly. Look very carefully at my first screen shot and note exactly where the Table now ends.

You need to highlight the said 8 rows then:-

• right click
• then click:-

Delete

• to get to my first screen shot THEN refresh the Pivot Table.

Hi Cliff,

Thanks for the quick response. Yes I tried it in a blank worksheet and got the same results. I guess I didn't give you all of the details. My apologies. In one worksheet I have the data and

1. convert the data table to an Excel Table Insert>Table
2. create the Pivot table pivot Chart Insert>PivotChart>PivotChart & PivotTable
3. place the Pivot Table and Pivot Chart in an Existing Worksheet other than the sheet where the data source resides
4. drag the Project Date field (2/14/22 format) to the Columns area which also gives me the Months. I have Disable automatic grouping of date/time... unchecked
5. I uncheck the Expense Date field. I also have the Project field in the Rows area and the Expense Amount field in the Values area.
6. I go back to the data source Excel Table and highlight and delete the last 8 rows of data with the delete key
7. then I go back to the worksheet with the Pivot Table and Pivot Chart and right click on the Pivot Table and do a Refresh. That's when the Pivot Table loses Months. It disappears from the Columns area altogether

I have also tried creating both the Pivot Table and Pivot Chart separately and get the same results. I'm in the process of creating courseware where I would like to show the students what the Pivot Table and Pivot Chart look like when blank data exists in the data source. If you need further detail, please let me know. Thanks for the help.

John

Dear John,

I did a test and I can't reproduce the issue in our environment.

In my environment, the source data is like the following.

After I remove some data, the Month group is kept.

May I know whether the issue happens suddenly? If yes, what did you do before the issue occurred?

If you copy the source data to a newly created file and do a test again, what's the result?

If it is convenient, I'd like you to open Excel>click on File>Account and upload a full screenshot of the Account page for our reference.

Note: Remove any private information before uploading the screenshots.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator