Share via

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

Anonymous
2019-05-02T14:04:07+00:00

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?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

10 answers

Sort by: Most helpful
  1. Anonymous
    2019-05-15T07:25:24+00:00

    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

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2019-05-14T13:06:32+00:00

    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

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2019-05-07T04:28:00+00:00

    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

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2019-05-06T07:08:42+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2019-05-03T00:53:18+00:00

    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

    Was this answer helpful?

    0 comments No comments