Advanced Excel Data Model - Advanced Excel Functions

How to create a Data Model in Advanced Excel?

Data model can be created in Advanced Excel which is used for creating amazing reports with the help of Power View. A model is built using Data Model where by creating relationships among the data sources, data from various sources is combined.

When two or more tables are imported from a database, a Data Model is created. The Data Model is created by the following steps:

Step 1 − Open a new blank Workbook in Excel.

Step 2 − Click on the DATA tab.

Step 3 − In the Get External Data group, click on the option From Access. The Select Data Source dialog box opens.

Step 4 − Select Events.accdb, Events Access Database file.

Select Events accdb

Step 5 − The Select Table window, displaying all the tables found in the database, appears.

Select Events Table

Step 6 − Tables in a database and Excel are similar. Check the ‘Enable selection of multiple tables’ box, and select all the tables. Then click OK.

Enable Selection Multiple Table

Step 7 − The Import Data window appears. Select the PivotTable Report option. This option imports the tables into Excel and prepares a PivotTable for analyzing the imported tables. Notice that the checkbox at the bottom of the window - ‘Add this data to the Data Model’ is selected and disabled.

Import Data Window Appears

Step 8 − The data is imported, and a PivotTable is created using the imported tables.

Create Imported PivotTable

All the data is imported to Excel to create Data Model. The data is explored in tables defined in the relationships built among them.

How to explore Data Using PivotTable in Advanced Excel?

Step 1 – The best-suited report with the data is made available.

In PivotTable Fields, click on the arrow beside the table - Medals to expand it to show the fields in that table. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area.

Step 2 − Drag Discipline from the Disciplines table to the ROWS area.

Step 3 − Filter Discipline to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself.

Step 4 − In PivotTable Fields, from the Medals table, drag Medal to the VALUES area.

Step 5 − From the Medals table, select Medal again and drag it into the FILTERS area.

Medals Table

Step 6 − Click the dropdown list button to the right of the Column labels.

Step 7 − Select Value Filters and then select Greater Than…

Step 8 − Click OK.

Greater Than Value

The Value Filters dialog box for the count of Medals is greater than appears.

Step 9 − Type 80 in the Right Field.

Step 10 − Click OK.

Type 80

The PivotTable displays only those regions, which has more than total 80 medals.

Greater than 80

The data from different tables can be analyzed and in few steps specific reports can be arrived at, because of the pre-existing relationships among the tables in the source database. As all the tables from the database are imported together at the same time, the relationships are recreated by Excel in the Data Model.

If the tables are not imported at the same time or if the data is from different sources, the Relationships need to be created among the Tables.

How to create Relationship between Tables in Advanced Excel?

Step 1 − Insert a new Worksheet.

Step 2 − Create a new table with new data. Name the new table as Sports.

Create New Table

Step 3 – Relationship between the new table is created and the other tables which exist in the Data Model in Excel. The sheet1 is renamed as Medals and Sheet2 as Sports.

On the Medals sheet, in the PivotTable Fields List, click All. A complete list of available tables will be displayed. The newly added table - Sports will also be displayed.

Table Sports

Step 4 − Click on Sports. In the expanded list of fields, select Sports. Excel messages to create a relationship between tables.

Message to Relationship

Step 5 − Click on CREATE. The Create Relationship dialog box opens.

Create Relationship Dialogue Box

Step 6 − To create the relationship, one of the tables must have a column of unique, non-repeated, values. In the Disciplines table, SportID column has such values. The table Sports that we have created also has the SportID column. In Table, select Disciplines.

Step 7 − In Column (Foreign), select SportID.

Step 8 − In Related Table, select Sports.

Step 9 − In Related Column (Primary), SportID gets selected automatically. Click OK.

Step 10 − The PivotTable is modified to reflect the addition of the new Data Field Sport. Adjust the order of the fields in the Rows area to maintain the Hierarchy. Here, Sport should be first and Discipline should be the next, as Discipline will be nested in Sport as a sub-category.

PivotTable New Relationship

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Advanced Excel Functions Topics