Try the Excel Course for Free!

Add Excel Tables to a Data Model- Instructions

/ / Excel 2013, Excel 2016, Latest, Microsoft, Office 2013, Office 2016, Office 365
Add Excel Tables to a Data Model- 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: Overview

            You can manually add Excel tables to a data model within a workbook. You can then use them as the data source for PivotTables, PivotCharts and Power View reports. Before doing this, however, first create the necessary tables within Excel. Also, to add Excel tables to a data model, 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. 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, 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.

            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.

Add Excel Tables to a Data Model- 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- Instructions: A picture of the “Workbook Connections” dialog box in Excel, showing references to Excel tables in the data model.

            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: Instructions

  1. Open the Excel 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: 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. This video on how to add Excel tables to a data model is from our complete Excel tutorial, titled “Mastering Excel Made Easy v.2016-2013.”

 

Online Payments

 

 

 

Join Our Mailing List:

*Email:
First Name:
Last Name:

Your information is safe with us.

Recent Posts

Archives

TOP
Sale!    Save $100 Today 0 Days 9 Hours 12 Minutes 43 Seconds      $199 $99 Access to Entire Library
See Deals