Excel

Hi Hazel,

Thank you for the forum question.

You can create PivotTables from related tables in Excel 2013.

The tables you want to relate to each other must have a related column (like working with Vlookup).

Example:

Table 1

1 James
2 Ann


Table 2

1 Nov 3000
1 Dec 2000
2 Nov 1000
2 Dec 2000


The related columns in the two tables are the first columns (it doesn't have to be the first column). In the second table the number (1 or 2) can be used many times to indicate if the staff member is James or Ann. In the many table the related column is called "The foreign key". In the first table (the one table) the related column is called "the primary key" and the primary key must be unique. This kind of relationship is called a One-To-Many relationship. The value can be one time in the first table but many times in the second table.

Excel can only make relationships between multiple tables (you can relate any number of tables) if the tables are stored in dynamic ranges (click inside the data and click TABLE on the INSERT tab in the TABLES group).

Click RELATIONSHIPS on the DATA tab in the DATA TOOLS group. Click NEW and in the Create Relationship dialog box in the Table list select the table with the foreign key (the many table). In the Column (Foreign) list select the foreign key.

Select the related table and the primary key and click OK.

To create the PivotTable based on the related table you must select the cell where you want the PivotTable.

Click Insert PivotTable and in the Create PivotTable dialog box select the option "Use an external data source". Click Choose Connection.

Click on the Tables tab at the top of the Existing Connections dialog box. Select the option "Tables in Workbook Data Model" and click Open. Clik OK in the Create PivotTable dialog box. You will now be able to select fields in the PivotTable from all related tables.

I hope this make sense.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector