• Advanced Excel Tutorial

Automatic relationship detection: In Excel 2013, pivoting two or more sets of data was a more difficult operation because you had to use Excel's Relationship tool to define the connecting field names between the separate database tables — a process often referred to as ­joining the databases.

• Excel New Features

• Fundamental Data Analysis

• Powerful Data Analysis

• Powerful Data Analysis – 1

• Powerful Data Analysis – 2

• Other Features

• Advanced Excel Useful Resources

• Selected Reading

Excel 2013 has powerful data analysis features. You can build a data model, then create amazing interactive reports using Power View. You can also make use of the Microsoft Business Intelligence features and capabilities in Excel, PivotTables, Power Pivot, and Power View.

Data Model is used for building a model where data from various sources can be combined by creating relationships among the data sources. A Data Model integrates the tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View.

A Data Model is created automatically when you import two or more tables simultaneously from a database. The existing database relationships between those tables is used to create the Data Model in Excel.

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.

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

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

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.

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

You have imported the data into Excel and the Data Model is created automatically. Now, you can explore data in the five tables, which have relationships defined among them.

Explore Data Using PivotTable

Step 1 − You know how to add fields to PivotTable and drag fields across areas. Even if you are not sure of the final report that you want, you can play with the data and choose the best-suited report.

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.

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.

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.

The PivotTable displays only those regions, which has more than total 80 medals. Purity vst torrent mac.

You could analyze your data from the different tables and arrive at the specific report you want in just a few steps. This was possible because of the pre-existing relationships among the tables in the source database. As you imported all the tables from the database together at the same time, Excel recreated the relationships in its Data Model.

If you do not import the tables at the same time, or if the data is from different sources or if you add new tables to your Workbook, you have to create the Relationships among the Tables by yourself.

Create Relationship between Tables

Relationships let you analyze your collections of the data in Excel, and create interesting and aesthetic reports from the data you import.

Step 1 − Insert a new Worksheet.

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

Step 3 − Now you can create relationship between this new table and the other tables that already exist in the Data Model in Excel. Rename the Sheet1 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.

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

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

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. In this case, Sport should be first and Discipline should be the next, as Discipline will be nested in Sport as a sub-category.

When linking Excel tables to Power Pivot, you add them to the Power Pivot data model. In this scenario, assume that you have added three data sets in three different worksheets: Customers, InvoiceHeader, and InvoiceDetails.

You can find the sample files for this exercise on in the workbook named Chapter 2 Samples.xlsx.

At this point, Power Pivot knows that you have three tables in the data model but has no idea how the tables relate to one another. You connect these tables by defining relationships between the Customers, Invoice Details, and Invoice Header tables. You can do so directly within the Power Pivot window.

If you’ve inadvertently closed the Power Pivot window, you can easily reopen it by clicking the Manage command button on the Power Pivot Ribbon tab.

Follow these steps to create relationships between your tables:

1. Activate the Power Pivot window and click the Diagram View command button on the Home tab.
   The Power Pivot screen you see shows a visual representation of all tables in the data model, as shown.

   You can move the tables in Diagram view by simply clicking and dragging them.
   The idea is to identify the primary index keys in each table and connect them. In this scenario, the Customers table and the Invoice Header table can be connected using the CustomerID field. The Invoice Header and Invoice Details tables can be connected using the InvoiceNumber field.

2. Click and drag a line from the CustomerID field in the Customers table to the CustomerID field in the Invoice Header table, as demonstrated here.
3. Click and drag a line from the InvoiceNumber field in the Invoice Header table to the InvoiceNumber field in the Invoice Details table.
   At this point, your diagram will look similar to the one shown. Notice that Power Pivot shows a line between the tables you just connected. In database terms, these are referred to as joins.

The joins in Power Pivot are always one-to-many joins. This means that when a table is joined to another, one of the tables has unique records with unique index numbers, while the other can have many records where index numbers are duplicated.

A common example is the relationship between the Customers table and the Invoice Header table. In the Customers table, you have a unique list of customers, each with its own, unique identifier. No CustomerID in that table is duplicated. The Invoice header table has many rows for each CustomerID; each customer can have many invoices.

Notice that the join lines have arrows pointing from a table to another table. The arrow in these join lines always points to the table that has the duplicated unique index.

To close the diagram and return to seeing the data tables, click the Data View command in the Power Pivot window.