Add this data to the Data Model check box is greyed out in Excel 2016
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?
- From the top menu you open the ‘File’ menu.
- There you choose ‘Options’.
- In the ‘Excel Options’ dialog you choose ‘Save’ on the left hand side.
- 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.
- Choose ‘Excel Workbook (*.xlsx) and you are good to go.
I hope that this helped you. Do not hesitate to post a comment.
This did not work for me.
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!
“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!
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.
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.
Thanks for sharing Eileen!
Thank you, Eileen. It worked for me.
Brilliant, it worked! Yay! thanks a lot!
Thank you! This did the trick
it worked for me thanks Eileen – it appears that this functionality is available for certain types of files only. At least i cant do it for CSV files
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 🙂
Thanks for sharing Dey!
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?
design tab > summarize with pivot table the the “add this data to the data model” is ENABLE
im using excel 2016
I could kiss you!!
design tab > summarize with pivot table, then “add this data to the data model” is ENABLE
im using excel 2016
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
Worked! Thanks
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.
This was very helpful, thanks!
Very helpful! It was excel compatibility mode. Thanks you!
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.
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.
Worked for me. Thank you.
Worked great thank you for posting ..