Connect Excel to Salesforce using Power Pivot

Power Pivot is a built-in component that offers advanced capabilities for data modeling and analysis. It enables you to create complex data models, connect large datasets, and perform advanced calculations with DAX (Data Analysis Expressions).

To import Salesforce data into Excel using Power Pivot:

1. Select File > Options.

2. Select Add-ins > Manage > COM Add-ins > Go, then click OK.

Select COM Add-ins in Excel Options

3. Select Microsoft Power Pivot for Excel, then click OK.

Select Microsoft Power Pivot for Excel in COM Add-ins

4. Select Power Pivot > Manage.

Select Power Pivot > Manage in Excel

5. Select Get External Data > From Other Sources.

Select Get External Data > From Other Sources in Power Pivot

6. Select Others (OLEDB/ODBC), then click Next.

Select Others (OLEDB/ODBC) in Table Import Wizard

7. Enter a name for a connection, then click Build.

Specify a connection string in the Table Import Wizard

8. Select one of the following options to specify the source of data:

  • Use data source name – Select your DSN.

  • Use connection string – Enter your connection string.

Specify the data source name in Data link Properties

9. Click OK.

The connection string appears in the Connection String field.

Specify the Connection string and click Next

10. Click Next.

11. Select your preferred method for importing data, then click Next.

Select the method to import data in Table Import Wizard

12. Select the tables you want to import, then click Finish.

Select Others (OLEDB/ODBC) in Table Import Wizard

A success message confirms a successful data import.

Table Import Wizard Success message

13. Click Close.

The data is displayed in a Power Pivot worksheet.

Power Pivot for Excel table data