Merge Salesforce Tables with Power Pivot Relationships in Excel
Question details
Connect Salesforce Opportunities, Accounts, and Users data in Power Pivot using shared ID columns.

- 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.
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.
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.
In Excel, select Power Pivot > Manage, then choose Diagram View to see the loaded tables.
Drag Users[User ID] to Opportunities[Opportunity Owner ID] to create the owner relationship.
Drag Accounts[Account ID] to Opportunities[Account ID]. Accounts must have one row per Account ID.
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.
In the Power Pivot window, select Home > PivotTable and add fields from Opportunities, Accounts, and Users to the PivotTable.

Create a separate Users lookup for account owners
Use a second Users table when the two owner roles cause an ambiguous relationship path.
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.

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.




