Have you gotten overwhelmed with the amount of Tables whenever you used Power Pivot?

As I got accustomed to Power Pivot, my data model expanded and the number of relationships connecting these Tables together grew to a larger amount.

Fortunately, Power Pivot has the Diagram View feature, which gives you a birds eye view of your entire data model.

What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales Table.

Linking Excel Tables 01

The crucial thing here is the relationship between these two tables. The ID column of the Sales Table related to the ID column of the Name Table.

You can see that each employee is uniquely identified by the ID number, which is also used in the Sales Table.

To make this example easy to understand, we have already linked these Tables to our Data Model, and we have already setup the necessary relationship between these two Tables.

*** You can view the exact step-by-step tutorial of how this was done here: Linking Excel Tables in Power Pivot.

 

Now you can download the prepared workbook and follow the steps to see how you too can use the Diagram View easily:

DOWNLOAD EXCEL 2013 or 2016 WORKBOOK

DOWNLOAD EXCEL 2010 WORKBOOK

 

STEP 1: Open the Power Pivot Window.  Go to Power Pivot > Manage.

(For Excel 2010, go to PowerPivot > PowerPivot Window)

Diagram View 01

 

STEP 2: This will open Power Pivot Window.  Your two Tables will already be loaded there.

Names Sheet:

Diagram View 02

 

Sales Sheet:

Diagram View 03

 

STEP 3: Go to Design > Manage Relationship.

Diagram View 04

 

The Relationship between the Sales Table and Name Table should be there:

EXCEL 2016 VIEW:

Diagram View 05

 

EXCEL 2013 & 2010 VIEW:

Manage Relationships - Excel 2013

Close out of this.

 

STEP 4: Go to Home> Diagram View.

Diagram View 06

 

You are now shown an easy to read Diagram representing your Data Model and its relationships:

EXCEL 2016 VIEW:

Diagram View 07

EXCEL 2010 & 2013 VIEW:

Diagram View - Excel 2010 & 2013

 

Diagram View

 

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Enabling Power Pivot in Excel 2016 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.Read the tutorial on how to install Power Pivot in Excel 2010 hereRead the tutorial on how to enable Power P...
Installing Power Pivot in Excel 2010 Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.Read the tutorial on how to enable Power Pivot in Excel 2013 hereRead the tutorial on how to enable Power Pivot ...
Linking Excel Tables in Power Pivot  When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data.What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales...
Power Pivot is now available in all versions of Ex... Microsoft have just announced that Excel Power Pivot will now be available in all Windows editions of Excel, which is awesome news and a step in the right direction.Here is what they announced: I’m happy to announce that your input has had another direct impact on the produc...