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

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.

You may also like...

25 Responses

  1. Maya says:

    This did not work for me.

    • Wim says:

      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!

  2. Juan says:

    “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!

    • Wim says:

      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.

  3. Eileen says:

    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.

  4. Dey says:

    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 🙂

  5. VIJAY says:

    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?

    • joan caingat says:

      design tab > summarize with pivot table the the “add this data to the data model” is ENABLE

      im using excel 2016

  6. Jess says:

    I could kiss you!!

  7. joan caingat says:

    design tab > summarize with pivot table, then “add this data to the data model” is ENABLE

    im using excel 2016

  8. joan caingat says:

    im using excel 2016
    save as ‘Excel Workbook (*.xlsx)

    from DESIGN TAB click Summarize with PivotTable, dialog box will appear and
    the “add this data to the data model” will ENABLE

  9. Filipe says:

    Worked! Thanks

  10. Dan says:

    Wow….I spent an hour trying to figure out why that box was greyed out, and it was because I had not saved the CSV export as xlsx. Amazing. Thank you sir.

  11. Yashas says:

    This was very helpful, thanks!

  12. Jacqueline Coolidge says:

    Very helpful! It was excel compatibility mode. Thanks you!

  13. Michelle says:

    This helped me mostly however, I will point out that if your Excel file is not in .xlsx or Excel Workbook (and in Excel 97-2003 for example), you’ll still be stuck. Once I saved my file in a newer version of Excel, the checkbox was no longer grayed out.

  14. Ed says:

    Readers Digest Version.
    Save spreadsheet in .xlsx format, then create your pivot table. Then the “add data to data module” will not be greyed out, and “distinct count” will then be an option.

  15. Ryan Rodehorst says:

    Worked for me. Thank you.

  16. Damien says:

    Worked great thank you for posting ..

Leave a Reply

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