Try the Excel Course for Free!

Add Excel Tables to a Data Model in Excel 2016- Instructions

/ / Excel 2013, Excel 2016, Latest, Microsoft, Office 2013, Office 2016
Add Excel Tables to a Data Model in Excel 2016- Instructions: A picture of the “Workbook Connections” dialog box in Excel, showing references to Excel tables in the data model.

Add Excel Tables to a Data Model in Excel 2016: Overview

            You can manually add Excel tables to a data model in Excel 2016 or earlier workbooks. You can then use them as the data source for PivotTables and PivotCharts. Note that this process has changed in Excel 2019 and Excel for Office 365. To learn how to add Excel tables to a data model in Excel 2019 or Excel for Office 365, please refer to our newer blog post, titled “Add Excel Tables to a Data Model in Excel 2019,” instead.

            Before doing this, however, first create the necessary tables within Excel. Also, to add Excel tables to a data model in Excel 2016 or earlier, ensure the information is formatted as a table in the worksheets. You can only add table data formatted as a table with a referenceable “Table Name” value to a data model. Also note, however, that the tables can be available within any opened workbook in Excel. They do not necessarily need to appear in the workbook into which they are added to the data model.

            First, open the tables to make them available in Excel Then open the workbook within which you want to add Excel tables to a data model in Excel 2016 or earlier. Then click the “Data” tab within the Ribbon. Click the “Connections” button within the “Connections” button group on the “Data” tab. Doing this then opens the “Workbook Connections” dialog box. This dialog box normally displays available workbook connections. It also displays where those connections are used within the workbook.

            To add Excel tables to a data model in Excel 2016 or earlier, click the drop-down to the right of the “Add…” button in the “Workbook Connections” dialog box. From the drop-down menu that appears, select the “Add to the Data Model…” command. When you do this, the “Existing Connections” dialog box appears.

Add Excel Tables to a Data Model in Excel 2016- Instructions: A picture of the “Workbook Connections” dialog box in Excel, showing references to Excel tables in the data model.

Add Excel Tables to a Data Model in Excel 2016- Instructions: A picture of the “Workbook Connections” dialog box in Excel 2016, showing references to Excel tables in the data model.

            Click the “Tables” tab within the “Existing Connections” dialog box. A list of the available Excel tables within any opened workbooks then appears. Select the desired table to add to the data model. Then click the “Open” button to add that table to the data model within the workbook. The table reference then appears within the “Workbook Connections” dialog box.

            Repeat this process by clicking the “Add…” button’s drop-down menu again. Repeat the same steps again to continue adding Excel tables to a data model. When finished, click the “Close” button within the “Workbook Connections” dialog box to close it.

            You can then select the data model as the data source when creating PivotTables and PivotCharts. Within the “Create PivotTable” or “Create PivotChart” dialog boxes, select the “Use an external data source” option button. Then click the “Choose Connection…” button to open the “Existing Connections” dialog box.

            Select the “Tables” tab. Then click the “Tables in Workbook Data Model” choice in the “This Workbook Data Model” shown. Then click the “Open” button to return to the “Create PivotTable” or “Create PivotChart” dialog boxes. Finally, finish creating the desired objects.

Add Excel Tables to a Data Model in Excel 2016: Instructions

  1. Open the Excel 2016 or earlier workbooks that contain the Excel tables to add to the data model.
  2. Open the workbook that contains the data model to which you want to add Excel tables.
  3. To add Excel tables to a data model in that workbook, click the “Data” tab within the Ribbon.
  4. Click the “Connections” button within the “Connections” button group on the “Data” tab to open the “Workbook Connections” dialog box.
  5. Click the drop-down arrow to the right of the “Add…” button in the “Workbook Connections” dialog box.
  6. Select the “Add to the Data Model…” command from the drop-down menu that appears.
  7. Click the “Tables” tab within the “Existing Connections” dialog box.
  8. A list of the available Excel tables within any opened workbooks appears.
  9. Select the table to add to the data model.
  10. Then click the “Open” button to add that table to the data model in the workbook.
  11. The table reference appears within the “Workbook Connections” dialog box.
  12. Repeat steps 5 through 11 to add any other Excel tables you want to the data model.
  13. When finished, click the “Close” button in the “Workbook Connections” dialog box to close it.
  14. To select the data model as the data source when creating PivotTables and PivotCharts within the “Create PivotTable” or “Create PivotChart” dialog boxes, select the “Use an external data source” option button.
  15. Then click the “Choose Connection…” button to open the “Existing Connections” dialog box.
  16. Select the “Tables” tab.
  17. Then click the “Tables in Workbook Data Model” choice within the “This Workbook Data Model” shown.
  18. Then click the “Open” button to return to the “Create PivotTable” or “Create PivotChart” dialog boxes.
  19. Finally, finish creating the desired objects.

Add Excel Tables to a Data Model in Excel 2016: Video Lesson

            The following video lesson, titled “Creating a Data Model from Excel Tables,” shows how to add Excel tables to a data model in Excel 2016 or earlier. This video on how to add Excel tables to a data model in Excel 2016 or earlier is from our complete Excel tutorial, titled “Mastering Excel Made Easy v.2016-2013.”

TOP