Pivot Chart data labels disappear (Excel 2003)

Topic

New Reply

I have and excel custom toolbar which contains a macro that is used for taking a large data sheet and making both a pivot table and pivot chart from the data in that sheet. The macro when run adds the data labels to the two series of data showing in the pivot chart. Once the macro is done running , if you use one of the drop down fields on the pivot chart to sort by a different means. It causes the data labels to no longer be visible. What options are available to keep the data labels visible after changing any one of the pivot chart fields? Any help with this would be greatly appreciated.

Reply | Quote

Replies

Could you post a copy of the workbook (zipped if necessary)?

Reply | Quote

Hans,

I can’t post the original file due to sensitive data in the file. The original is basically a database dump into excel containing data in columns A – CZ with about 53K rows. I’ve created a quick mock up which is attached and even this mock up has the same issue. If you attempt to change any of the fields on the pivot chart, you will lose the data labels. It would be easy to create a macro in the sheet that can add back in the labels but the problem I’m having is the macro used for creating the pivot chart is not contained within the actual sheet with the pivot chart. The macro for creating the chart is contained within a custom toolbar.

Our current process is take this DB dump each month and run the macro that can pivot out the data and chart it in a pivot chart that we can use to filter the data from many different ways. I’d like to avoid having to go into the finished sheet each time to add in another macro that can add back the labels each time the chart fields are changed. I’m open to any suggestions you may have. As always thanks for your help!

Reply | Quote

I don’t know how you have applied the data labels, but they do disappear when I change the structure of the pivot table/chart. But if I add them again interactively, through the Data Label tab of Chart | Chart Options…, they stick when I change the structure of the pivot table/chart.

Reply | Quote

Hans thanks for the suggestion. I initially was just going into each series collection and adding the lables that way. I was not aware I could do it the way you suggested. I’ll give that a try and see if that works. Thanks for the help!!

Reply | Quote