How to Connect Microsoft Excel to an SQL Server

Excel spreadsheets typically contain static information. With a connection to an SQL Server, data remains current when you work with a back-end database. It's simple to configure an Excel spreadsheet with a connection to an existing SQL database and provide it to your users.

Instructions in this article apply to Excel 2019, Excel 2016, and Excel for Office 365.

Before You Begin

This tutorial uses the Adventure Works sample database that Microsoft ships with SQL Server 2008 and higher. Or, download the database here.

To follow along with the tutorial, you'll need

Microsoft Excel 2019, Excel 2016, or Excel for Office 365.
SQL Server data including the server, database, and the table or view.

Connect Excel to a SQL Server Database

Collect the information needed to set up the Excel to SQL Server connection:
- SQL Server Name: In this tutorial, the SQL Server is MTP\SQLEXPRESS.
- Database Name: This tutorial uses the AdventureWorks database.
- Table or View: This tutorial uses the view Sales.vIndividualCustomer.
Open Excel and create a new workbook.
Go to Data and select Get Data > From Database > From SQL Server Database. This opens the Connect to Database Server dialog box.
Enter the Server name. In this example, the server name is MTP\SQLEXPRESS.
In the Log on credentials section, choose Use Windows Authentication.

If the database administrator provided a username and password for your user, click on Use the following User Name and Password to enter the provided credentials.
Select Next. This brings up the Select Database and Table dialog.
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 Next.
The Save Data Connection File and Finish dialog pops up. Enter a new name and path in the File Name field. You can also give it an easy to understand name in the Friendly Name box. Once everything is set click Finish which brings up the Import Data dialog box.
Check the Table radio box 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.

There is an alternate way of connecting an SQL database in Excel. Follow the steps below.

Within Excel, click on the Data tab.
Then under the Get & Transform group, click on the dropdown labeled New Query and select From database > From SQL Server Database.
In the SQL Server database dialog, enter the name of your server (ex. MTP\SQLEXPRESS) in the Server field. You can also give your database a friendly name in the Database field.
If you want your imported data to use a native database query, enter in the query in the SQL statement field.
Once you have all the parameters set, click OK.
The SQL Server database login should open up. Choose either Windows to use your Windows credentials or an alternate Windows account. Or select the Database tab to use the SQL authentication server to log in.
Once you've entered your credentials, click Connect.
The Encrypt connection check box is selected by default so that Power Query connects to your SQL database with an encrypted connection. You can uncheck this box and connect unencrypted. If you connect unencrypted, you will need to click OK in the message confirmation box.
Your data should import into Excel once connection has been established.

The nice thing about these techniques 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.