How to Connect Microsoft Excel to an SQL Server

New & Next

Updated December 23, 2018

Excel spreadsheets typically contain static information, but with a connection to an SQL Server, data can remain current thanks to a back-end database. This article aims at showing how simple it is to configure an Excel spreadsheet with a connection to an existing SQL database that you can provide to your users.

In this example, we are going to use the Adventure Works sample database that Microsoft ships with SQL Server 2008 and higher or can be downloaded here.

Note: Instructions in this article apply to Excel 2016.

Here's How

You will need a few pieces of information to set up the Excel to SQL Server connection.

SQL Server Name: In our example, the SQL Server is MTP\SQLEXPRESS.
Database Name: Our example, we are using the AdventureWorks database.
Table or View: We are going after the view Sales.vIndividualCustomer.
Open Excel and create a new workbook.
Click on the Data tab. Locate the Get External Data group and click on From Other Sources and choose From SQL Server. This opens the Data Connection Wizard.
Fill in the Server Name. In this example, the server name is MTP\SQLEXPRESS. Set the Login Credentials to Use Windows Authentication. The other option would be used if your database administrator provided a username and password for your user. Click Next. This brings up the Data Connection Wizard.
Select the database (AdventureWorks in our example) from the Select the database that contains the data you want drop down box. Make sure Connect to a specific table is checked. Locate the view (Sales.vIndividualCustomer in our example) from the list and select it. Click Finish which brings up the Import Data dialog box.
Check the Table checkbox and choose where you want to put the data (existing worksheet or new worksheet). Click OK which creates an Excel list and imports the entire table into your spreadsheet.
Save your spreadsheet and send to the user. The nice thing about this technique is that your user has access to current data whenever they need it. While the data is saved in the spreadsheet, there is a connection to the SQL Database. Anytime you want to refresh the spreadsheet, right click somewhere in the table and click on Table and then Refresh.

Tips

It's really important that you make sure the user is properly setup in SQL Server. This is the thing that causes issues in most cases using this technique.
Check the number of records that are in the table or view that you are connecting to. If the table has a million records, you might want to filter this down. The last thing you want to do is hang the SQL Server.
On the Connection Properties dialog box, there is an option called Refresh data when opening the file. Consider checking this option. When this option is checked, the user will always have a fresh set of data when opening the Excel spreadsheet.
Consider using Pivot Tables to summarize the data.