How can I RETAIN formatting in a pivot table?

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

Thnaks in advance for help.

Which date field (column A or B) are you using in the pivot table?. When you are putting the date into the pivot table where are you putting it into - a Row, Column, Page or Data fields?

If you are using a row or column field, have you considered using the pivot table group function, to group by Month then Year?

Originally Posted by Gary Brown

Which date field (column A or B) are you using in the pivot table?. When you are putting the date into the pivot table where are you putting it into - a Row, Column, Page or Data fields?

If you are using a row or column field, have you considered using the pivot table group function, to group by Month then Year?

Hi Gary,

Well...this is weird! I was just going to post a sample of my data and the pivot table, so I copied a portion of it to a NEW file and recreated the pivot table there. And it works! Exactly as I want it. The other file was one someone else had created so maybe there's something hidden in it, which is preventing me from formatting it exactly as I need.

I'm a novice at pivot tables, I have to say, but I've just had a look at the group function that you suggested and that may have possibilities too. If I had the individual date as the ROW, would I have to manually select which cells to group i.e. a month's worth?

I'm attaching the the sample table anyway and would be grateful for advice if you get time. If not, don't worry, as I think I can link my data to a new file and get the pivot table formatted correctly there.

Thank you so much for taking time to look at this.

Pat

If you go to options in pivot table wizard, uncheck AutoFormat table and check Preserve Formatting, that may work for you.

The reason you couldn't group the dates is because the data included non date data. In the sample you attached the the last record under Cancellation Date is (blank) and this is because your data range that you've used is for the range A:D as opposed to A1:D44. If you change the Pivot Table data range accordingly, remove the field (Cancellation Date) from the Pivot Table. Save the file, re-open the file (if you closed it), put the field back in, and then try grouping it should be fine.

The problem is that blank (for a Cancellation Date) cannot be grouped into a day/month/year.

Gary,
Thanks very much for your continued input. I see what you mean about the blank record and it makes sense that it cannot be grouped. The catch is that I do not know in advance how many rows of data I will have at any time. That was why I selected the data source as A:D. But I suppose I could change the pivot table source manually like this. It would only have to be done once a month.

Sorry for being thick about this, but I'm still puzzled by how you group the dates. In the updated sample I've attached (after creating a NEW pivot from A1 to D44), I have MANUALLY selected data for some months and grouped it. I've even worked out how to hide the detail for the group and presumably I could rename the groups.

But is there a way to get Excel to AUTOMATICALLY pivot and group the data by month? Bear in mind that there will be different numbers of dates in each month (these cancellations can be seasonal). I would have thought there was a way, but what do I click and where? Ideally, I want to be able to just click Refresh data once a month (or hand this over to my IT-phobic director and have him do it himself once a month so I can get on with other stuff!!)

As I said, I'm eternally grateful. Sites like this and people like you have got me a long way.

Pat.

Originally Posted by BigPat

Gary,
Thanks very much for your continued input. I see what you mean about the blank record and it makes sense that it cannot be grouped. The catch is that I do not know in advance how many rows of data I will have at any time. That was why I selected the data source as A:D. But I suppose I could change the pivot table source manually like this. It would only have to be done once a month.


Pat.

Instead of choosing the A:D range, which gets you in trouble., use a dynamic range
You will find all information you need here :http://www.contextures.com/xlPivot01.html
This will allow the PT range to adapt automatically to your list

Thank you very much for this link. I've learned something from page 1 already and I'm sure the rest of it will prove equally useful.

Merci beaucoup!

Glad to help