Excel tables appear in PivotTable fields list RRS feed

  • Question

  • Hi,

    I have an excel 2016 workbook that has 5 excel tables. Only one of these tables (DisplayAmount) is linked to the datamodel. The datamodel also contains 2 other tables that are NOT in excel (dim_Calendar & dim_Projects). After creating the pivot table if I show the field list I can see the 2 tables in the datamodel (yellow disk on their icon) and also the linked table (icon has coloured blue bar at top) but I can also see the excel tables which I do not want to see. The datamodel tables can easily be hidden by going into the datamodel and selecting "hide from client" but there is no way to hide the 4 excel tables from the list. 

    I have seen a "workaround" to mark the tables I want to see as "Active" and then only look at the active tab but surely there is a way to hide the unwanted excel tables?

    to recreate this problem:

    1. create a table in excel and do NOT link it to anything

    2. open the datamodel and import an external data source

    3. create a pivot table and view field list. the excel table you created in step 1 will be visible on the all tab.

    4. Now try and hide the excel table.......

    hope you can help


    Wednesday, October 12, 2016 1:49 AM


All replies

  • Hi Dale Cameron1,

    As I tested in Excel 2016, I give the following information and suggestions.

    All the table related to Power Pivot table can’t be removed from the Active tab, while we can remove other  tables in your worksheet.

    There isn’t a way to hide any excel table from ALL tab if your 5 excel tables in same worksheet. The ALL tab show all table in your worksheet.  I suggest you put tables unwanted and unrelated with Pivot table to a new worksheet. So they will not be listed in ALL tabs.

    In addition, when you select "hide from client tools", all the columns except measure in table will be hide, rather than a whole table.

    Best Regards,
    Wednesday, October 12, 2016 12:43 PM
  • Hi Angelia,

    thanks for your reply. When you say "worksheet" were you referring to the excel workbook? If the 5 tables were in another excel workbook then they would not appear in my pivot table field list? I agree with you on this but unfortunately the 5 tables are all required in this same workbook. 

    This can create confusion for the end user who has 7 tables to view but only 2 of which are any use. Oh well, guess I will have to recommend users only look at the "Active" tab and ensure only the required tables are added to this.

    Best Regards


    Thursday, October 13, 2016 4:09 AM
  • Hi Dale,

    Yes, the worksheet refers to the excel workbook. In excel, it only shows all the table located in same worksheet. It's a  food idea to recommend users only look at the "Active" tab.

    Thank you very much,

    Monday, October 17, 2016 9:12 AM