[Fixed] ‘Add This Data to the Data Model’ Missing in Excel

A pivot table is a widely used tool in Microsoft Excel. In order to do data analysis, pivot tables help in many ways. A way to make pivot tables more useful is to add the pivot table data to the data model. However, the option called “Add this Data to the Data Model” can go missing for a number of reasons. In this article, we will seek the reasons for the missing option and try to restore it if it’s possible.

We are looking at some website performance data to analyze. From the admin console, we have downloaded a CSV file that we have opened in Excel for further analysis. There are page names, visit count, bounce rate in percentage, average time spent, and the countries people visited the website from. Let’s try to create a pivot table from this data.

➤ Go to the Insert tab from the ribbon, and select PivotTable.

➤ In the pivot table creation window, the option that says “Add this data to the data model” is missing.
➤ The file is saved as a “comma-separated values” file. These files are severely crippled in terms of features, but it is an open format, which is why a lot of programs output this type of file.
➤ The issue can also happen if the workbook is saved as xls format for compatibility.
➤ In order to bring the option back, we have to save the file as xlsx file.
➤ Go to File from the ribbon, and select Save As.
➤ Now, from the dropdown, select Excel Workbook (*.xlsx) and hit Save.

➤ Go to Insert > PivotTable again, and check the pivot table creation window.

➤ The option will be available now. Check the box to add the data to the data model, and press OK to create the pivot table.

There are multiple ways to create a pivot table. If we don’t select the proper option to create the pivot table, we cannot add the data to the data model. Ironically, the way Excel recommends us to create the pivot table does not include the option to add the data to the data model.

➤ Select the data you want to create a pivot table with.
➤ Go to Insert > Recommended PivotTables.

➤ In this dialog box, there is no option to add the pivot table to the data model. To get that option back, we need to create the pivot table manually.
➤ Click on the PivotTable icon on the left of the same Tables group. Now you can find the option to add the data to the data model.

The data model option is not available in all versions of Excel. Look at the example below:

➤ Here, we are opening the Excel file in Microsoft Excel 2010. The option to add the data to the data model is not available here.

➤ If you are using Office on a Mac, the option will not be available to you either.
➤ To fix the issue, upgrade your older office to a newer version, at least 2013. In case you are on a Mac, you can use emulation programs like Parallels to run Microsoft 365.

Frequently Asked Questions

What does “add this data to the data model” mean in Excel?

Microsoft Excel provides data models that can be used to analyze the pivot table data in Power Pivot for advanced processing. If you check that box during the creation of your pivot table, the data will be available in the data model for further manipulation.

Why is data missing in my PivotTable?

You have probably added some filters to your pivot table. If the field you are looking for is in the Filters section, the data will not be visible in the table. Move that to the Columns or Rows section to show the data. If the field is not in that section, clear other filters from the Data tab to show all other data.

How to fix PivotTable data source?

Go to the PivotTable Analyze tab, and find Change Data Source from the Data group. A window will pop up showing the original data source of the pivot table. Change the data source of the pivot table to the new range.

How do I add data to a pivot?

If you have already created the pivot table, you can refresh the pivot table to add data to the pivot table. To add data that does not belong in the existing data source, you can change the data source of the pivot table as well. However, it is always better to create a new pivot table by going to Insert > PivotTable to keep the structure right.

How to reset pivot data?

Go to the PivotTable Analyze tab. Then, from the Actions group, select Clear > Clear All. This will reset the pivot table, and you can add the required data in the sections you want.

Wrapping Up

In this article, we have explored possible solutions to why the “Add this data to the Data Model” option may be missing from the pivot table creation window. If you are still facing the error and have not found a solution in this article, feel free to leave your feedback in the replies. We will get back to you soon and try to fix the issue with your workbook. Stay tuned, and we will see you in another Excel tutorial.