Simon Bratina 

Since the issue arises when the pivot tables are refreshing and when editing is enabled in Excel causing them to revert to the source data instead of displaying the translated content here's a few suggestions... although I imagine you have thought of these already:

1. Disable the automatic refresh of pivot tables when the workbook is opened or when editing is enabled.

2. If your client only requires the translated data without needing pivot table functionality, you could copy the pivot table and paste it as values in a new sheet.  This way the translated text would be preserved without the risk of refreshing back to the source data.

3. Instead of linking to an external dataset (if this is the case?), try embedding a snapshot of the data directly in the workbook, which may reduce the likelihood of an automatic refresh.

4. If the pivot table must remain dynamic, maybe use a macro that re-applies the translated content after each refresh.  So store the translated text in a hidden sheet or file and re-inject it into the pivot table fields as needed.

5. Perhaps consider exporting the data to a format that doesn’t rely on pivot tables (like CSV), translate it, and then re-import it if necessary.  This would avoid Excel’s pivot refresh behaviour entirely.

6. For processing Excel files with pivot tables, it might even be best to ensure that any external data dependencies are removed before translation work begins.  So try using "Values Only" sheets for translation if pivot tables or formulas aren’t strictly necessary for the final format.

Just some thoughts... no idea if they'll be helpful or not.

Simon Bratina 

Since the issue arises when the pivot tables are refreshing and when editing is enabled in Excel causing them to revert to the source data instead of displaying the translated content here's a few suggestions... although I imagine you have thought of these already:

1. Disable the automatic refresh of pivot tables when the workbook is opened or when editing is enabled.

2. If your client only requires the translated data without needing pivot table functionality, you could copy the pivot table and paste it as values in a new sheet.  This way the translated text would be preserved without the risk of refreshing back to the source data.

3. Instead of linking to an external dataset (if this is the case?), try embedding a snapshot of the data directly in the workbook, which may reduce the likelihood of an automatic refresh.

4. If the pivot table must remain dynamic, maybe use a macro that re-applies the translated content after each refresh.  So store the translated text in a hidden sheet or file and re-inject it into the pivot table fields as needed.

5. Perhaps consider exporting the data to a format that doesn’t rely on pivot tables (like CSV), translate it, and then re-import it if necessary.  This would avoid Excel’s pivot refresh behaviour entirely.

6. For processing Excel files with pivot tables, it might even be best to ensure that any external data dependencies are removed before translation work begins.  So try using "Values Only" sheets for translation if pivot tables or formulas aren’t strictly necessary for the final format.

Just some thoughts... no idea if they'll be helpful or not.

Hi Paul 

Thanks' for the quick feedback and a number of suggestions.

In the end we did copy "Pivot table results" and pasted them as Static tables with values on additional sheets.

I was looking also in the other options but with now usefull result - as data is cached in the Excel file itself.

I'll check the options again.

Best regards, 

Simon