Data Import & Export

Merge Salesforce Tables with Power Pivot Relationships in Excel

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

Connect Salesforce Opportunities, Accounts, and Users data in Power Pivot using shared ID columns.

Merge Salesforce Tables with Power Pivot Relationships in Excel
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
The user wants to analyze opportunities alongside account information and owner names.
Observed behavior
A Data Model with relationships should allow opportunity and account owner details to be used in a PivotTable.
Before you start

Load the Opportunities, Accounts, and Users tables into Excel's Data Model. Check that related ID columns have matching data types and that Users[User ID] and Accounts[Account ID] contain unique values.

Solution 1Recommended

Create Power Pivot relationships for opportunities and accounts

Use matching Salesforce IDs to connect the tables for PivotTable analysis.

Relationships let you analyze fields from multiple tables without physically merging them into one table.

1
Open the Data Model

In Excel, select Power Pivot > Manage, then choose Diagram View to see the loaded tables.

2
Link opportunity owners

Drag Users[User ID] to Opportunities[Opportunity Owner ID] to create the owner relationship.

3
Link accounts to opportunities

Drag Accounts[Account ID] to Opportunities[Account ID]. Accounts must have one row per Account ID.

4
Add account owners

If you also need account owner names, connect Users[User ID] to Accounts[Account Owner ID]. If that creates an ambiguous relationship path or cannot be used as needed, use the separate Users lookup described below instead.

5
Build a PivotTable

In the Power Pivot window, select Home > PivotTable and add fields from Opportunities, Accounts, and Users to the PivotTable.

Create Power Pivot relationships for opportunities and accounts
Check relationship direction: Users and Accounts are lookup tables on the unique side of their respective relationships. Matching IDs must use compatible data types.
Free Microsoft Office alternative

Work with Salesforce spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for working with exported Salesforce spreadsheets. Its familiar interface and Excel format compatibility make it easy to move existing files, although it does not fix Excel Power Pivot relationships.

Open and review exported Salesforce spreadsheet dataCompatible with Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interfaceMove existing Office files into a lightweight application
microsoft office alternative - wps office

Frequently Asked Questions

Which columns link Opportunities to Accounts?

Create a relationship between Opportunities[Account ID] and Accounts[Account ID] in Power Pivot > Manage > Diagram View. Accounts[Account ID] must be unique.

How do I show both opportunity and account owner names?

Link Opportunities[Opportunity Owner ID] to Users[User ID]. For account owners, link Accounts[Account Owner ID] to Users[User ID], or use a separate Users reference for the account owner role if the model has an ambiguous path.

Why can't I create a relationship using the ID columns?

Check that the lookup column contains unique values and that both ID columns have compatible data types. You can inspect or change column types in Power Query Editor.

Does a Power Pivot relationship combine the tables into one?

No. Relationships connect tables within the Data Model so you can use their fields together in a PivotTable; they do not create a single merged table.