Add data to Data Model option is greyed out

Add this data to the Data Model check box is greyed out in Excel 2016

Posted by

Add data to Data Model option is greyed out

A problem I ran into when creating a new query in my freshly installed Microsoft Excel 2016 was that I could not import data to the Data Model. This is set in the ‘Load To’ dialog when you open a new Workbook and choose ‘New Query’ to import data from a file or database.

The solution.

Well the solution in my case anyway. Again, mind that this is for Microsoft Excel 2016. I do not know the previous versions behavior.

The solution lies in the default file type and extension set in the configuration to save your Workbook.

How to change this?

  1. From the top menu you open the ‘File’ menu.
  2. There you choose ‘Options’.
  3. In the ‘Excel Options’ dialog you choose ‘Save’ on the left hand side.
  4. In the ‘Save workbooks’ section (the first section) you will find the ‘Save files in this format: ‘ drop down where you can choose the different types of files.
  5. Choose ‘Excel Workbook (*.xlsx) and you are good to go.

Setting default file type for saving Excel 2016 workbook

I hope that this helped you. Do not hesitate to post a comment.

11 comments

    1. Hi Maya, did you manage to find the solution for your case? It would be great if you could share so I can add it to the post. Thanks!

  1. “Add this data to the Data Model” not shown in the Create Pivot Table dialogue screen. Excel for Mac version 15.35

    can you help? Thanks!

    1. Hi Juan, I am afraid I cannot reproduce your problem. I do not have the same configuration. I was checking the 15.35 support requests but couldn’t find anything either. I know that in Excel 2013 you had to activate the Power Pivot Add-in. I do not know if there any changes there for version 15.35. Please inform me if you find a solution.

  2. Hi there, I just figured out another option that worked for me. I ran into the same issue where ‘Distinct Count of Account Name’ only showed up sometimes and ‘Add this data to the Data Model’ would be grayed out. They seem to be directly correlated.
    For me the fix was I had to SAVE the xls document rather than what I was doing which was exporting a report to xls from another program and trying to build the Pivot Table in that document – I hadn’t saved it I was just working on the exported file. Once I exported the report into xls, SAVE, then build the Pivot Table I was given the option of Add this data to the Data Model and Distinct Count of Account Name showed up.
    The original file I was trying to work from with an error was a read-only so once I finally saw that I saved and everything worked. I hope that helps someone else!

    I originally tried to follow your instructions above but my settings were already configured this way so it did not work for me.

  3. I found in other webpages that the trouble was the Compatibility Mode… First, I just saved it as you stated and nothing happened, but then I just restarted the program so it wasn’t in that C. Mode and I was good to go 🙂

  4. I am using Microsoft Excel Version 14.0.7188.5000 part of Microsoft office standard 2010.
    When i am trying to add a pivot table the” Add this data to data model is not visible”.
    Please help to get this option activated. how to get it?

Leave a Reply

Your email address will not be published. Required fields are marked *