layout multiple consolidation range pivot table layout when updating Excel with other language setting

Question

In our business we are using Excel in English and Dutch. When a pivot table with multiple consolidation ranges is set up in the English Excel version, the fields are called: row, column, page1, etc.  When this file opened in the Dutch Excel version and the pivot table is updated, the layout is lost, because the field names are translated to the dutch equivalent and the English ones are therefore not recognized anymore.

Is there a way to make pivot tables with multiple consolidation ranges language independent?

Or is there another way to consolidate multiple ranges that works with Excel in multiple languages?

Answer 1

Hi Sander,

I tried a file from my side:

English version Excel:

Dutch version Excel:

The auto-names like Grand Total will change, but the actual names like Buyer will not change. Can you share a sample of the file so I can check it from my side. You can remove the confidential data if you like. To share the file: Private Message.

Regards,

Alex

Answer 2

Hi Alex,

I have tried to use named ranges for the range used for this multiple consolidation pivot table. I also tried to use custom names for the 'row', 'column' and 'page' fields, but both didn't work; when I opened the file and refreshed the pivot table in Excel that was set up in another language, these fields are not recognised anymore, and my layout is gone. 

Could you please let me know how exactly using named ranges would solve this issue?

Best Regards,

Sander

Answer 3

Hi Sander,

Yes, you can use named ranges. Except for that, you can also share your feedback with Office via the smiley face in Excel.

Regards,

Alex

Answer 4

Thanks for your reply, Alex. 

Are you suggesting to use named ranges as a way to work around this?

I will give it a go.

Answer 5

Hi SanderMonté,

Sorry for the inconvenience but the Pivot Table is an automatically generated table and it follows the language of the Excel client. 

You can name the data first, like use column name and worksheet name. When system language changes, the manually edited information will not change because of it.

We also welcome you to share this need with Office: How do I give feedback on Microsoft Office?

Appreciate your understanding.

Regards,

Alex