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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Enabling Power Pivot in Excel 2013 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 here Read the tutorial on how to enable Power Pivot...
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 here Read the tutorial on how to enable Power P...
Using Excel Tables in Power Pivot To be able to use Power Pivot, you need to have data imported into the Power Pivot Window first. If your data is in the normal Excel worksheets, then you will not be able to use this data in Power Pivot. Fortunately there are a lot of possible ways Power Pivot can retrieve ...
COUNTROWS in Excel Power Pivot   In Power Pivot, one of the major and more powerful feature are Measures. Measures (also known as Calculated Fields in Excel 2013) are formulas/calculations that are added to a Pivot Table. We will work on a simple example to show you how easy it is to use the COUN...