Microsoft Excel Connections to SQL databases

Microsoft Excel can be a powerful tool to evaluate SQL data.  In Excel, a connection can be created to directly link to a particular database filtered according to your requirements.   This connection must be validated by a SQL login to ensure the contents of your databases

remain secure. The connection can be refreshed to update the content from the SQL database.

Create an Excel Connection to a SQL database

To Create an Excel Connection:

  • Open Microsoft Excel
  • Select the Data tab
  • Click From other sources
  • Select From Data Connection Wizard
  • Select Microsoft SQL Server
  • Click Next
  • Enter the SQL Server Name
  • Select credentials to use
  • Click Next
  • Select the database and primary table you would like to use
  • Click Next
  • Enter File Name
  • Enter Friendly Name
  • Click Finish
  • Enter cell to start the data connection
  • Select how to display the data (Table, Pivot Table, Pivot Chart and Table)
  • Click Ok
  • Enter your credentials
  • Click Ok

Refresh an Excel Connection to a SQL database

To refresh the Excel Connection:

  • Select the Data tab
  • Select Refresh All

Update Excel Connection(s) to a SQL database

Update the connection:

  • Update an Excel Connection:
  • Select the Data tab
  • Select Connections
  • Select the Connection
  • Select the properties button
  • Modify the settings required
  • Select the Definition tab
  • Modify the Connection string to connect to a different database
  • Modify the Command Type to SQL for Queries or Table for a table
  • Modify the Command text to enter a SQL query to select particular data

Share this post

Related Posts