Set the Data Source for Excel PowerPivot Files

When we develop sample excel spreadsheets we use sample data on our development machines. For these spreadsheets to be useful to you, the data connection needs to be changed to point to your database. The steps below outline how to adjust this connection.

  1. Open the sample file in Excel.
  2. Click the PowerPivot Ribbon and click Manage (See Notes below for info on PowerPivot availability and activation).

    Click image to enlarge
  3. In the Data Model editor, click existing connections.

    Click image to enlarge
  4. On the Existing Connections dialog, click edit.

    Click image to enlarge
  5. On the Edit Connection dialog, use the Server Name drop down to select your Microsoft SQL Server Instance. If you do not know your server name, find this in Comsense Advantage by clicking Help > About Comsense Advantage listed as SQL Server Instance.

    Click image to enlarge
  6. When you change the Server Name the Database name will clear out. From the Database Name drop down select ComsenseDataWarehouse.
  7. Click Save.
  8. Click Close on the Existing Connections dialog.
  9. Click the drop down under Refresh and click Refresh All.

    Click image to enlarge
  10. The Data Refresh dialog will appear and show you progress. Click close when complete.

    Click image to enlarge
  11. Close the PowerPivot for Excel editor to return to Excel, using either File > Close or click X.
  12. Save the Excel spreadsheet.
  13. To refresh the data in the spreadsheet in the future, simply click Refresh All on the Data Ribbon in Excel.

    Click image to enlarge



  • Only certain editions of Excel include the PowerPivot feature see this article on PowerPivotPro for more info.
  • To enable the Excel PowerPivot feature see Microsoft's instructions here.
  • If you receive an error attempting to refresh connect or refresh the data, see the article Installing SQL Client Libraries for help.
Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.