Build a Data Model to bring together widespread data for analysis

 

by Kara Hiltz

Application: Microsoft Excel 2013/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201607/datamodel.zip

Ideally, you would keep all of your data in one location. That’s not reality, however, especially when more than one person is involved in collecting and maintaining data. The good news is that you can build a PivotTable to combine data from several locations and analyze it all in one place.

To merge several data tables from various locations into one PivotTable, we’ll:

  • Identify all of the places from which you need to pull data.
  • Create a Data Model to hold all of the data you want to include in your PivotTable.
  • Integrate data from the same workbook, from a different workbook, and from a .TXT file into your Data Model.

PivotTables, PivotCharts, and similar data analysis tools have been combined into one powerful feature in Excel 2013 and Excel 2016. By consolidating these data analysis capabilities into a Data Model, Microsoft has made it easier to work with data from several locations, as shown in Figure A.

When you work with a Data Model, you work in a completely new window with its own Ribbon and tabs. We’ll show you how to create a Data Model and add data from a variety of sources, including from: a separate worksheet, a different workbook, and an external data file.

 

IME18705A

A:

In the PivotTable Field List, you’ll see four data sources listed as potential additions to our PivotTable because we included them all in our Data Model.

Hunt down all of your data

Before you create a Data Model, you need to identify where all of your data resides. In our example, we have daily sales data for a handful of inventory items in three different places: two separate worksheets within the same workbook, a worksheet in a different workbook, and a .TXT file. All of these data tables contain sales information for different dates, as shown in Figure B, but we want to combine this dispersed information.

Once you locate all of the places holding data that you want to include in your Data Model, it’s time to create the Data Model.

Download:To follow along with our example, you can download and extract the files from datamodel.zip using the URL given at the beginning of this article.

 

IME18705B1 IME18705B2 IME18705B3

B:

We will combine data from: two worksheets in the same workbook, a worksheet in a different workbook, and a .TXT file.

 

Start a new Data Model

Your Data Model will allow you to pull data from several sources and analyze it together. In our example, we’ll be able to see sales figures for inventory items over several days, even though this data isn’t all housed in the same worksheet.

To create a Data Model:

  1. Open an Excel workbook containing data that you want to include in your Data Model.
  2. Select any cell within the data table and then switch to the Data tab.
  3. Click on the Manage Data Model button in the Data Tools area. Excel opens the Power Pivot window, in which you work with your new Data Model, as shown in Figure C.

 

IME18705C

C:

The Power Pivot window includes its own Ribbon that has File, Home, Design, and Advanced tabs.

Retrieve your data from various locations

You have a Power Pivot window open now, which will allow you to build and manage your Data Model. You can create PivotTables and PivotCharts as part of the Power Pivot tool. Now you’ll want to add data to your Data Model—even data from external sources. Here are a few examples.

To add data from the same workbook:

  1. Select any cell within the data range you want to include in the Data Model.
  2. Switch to the Power Pivot tab. (If you don’t see this tab, see the “Install Power Pivot” box to learn how to add it.)
  3. Click the Add To Data Model button in the Tables area to open the Create Table dialog box, in which Excel has already guessed your data table range.
  4. Confirm the data range and select the My Data Has Headers check box, if appropriate, as shown in Figure D.
  5. Click OK to add the data range to your Data Model, as shown in Figure E.
     
    Join NowClose
Return to the ExcelSkillsSociety's homepage