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 From Other Sources > From SQL Server. 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, select Use the following User Name and Password to enter the provided credentials.
Select Next to open the Select Database and Table dialog box.
Select the database (AdventureWorks in this example) from the Select the database that contains the data you want dropdown list. Make sure Connect to a specific table is checked.
Locate the view (Sales.vIndividualCustomer in this example) from the list and choose it. Select Next.
The Save Data Connection File and Finish dialog box opens. Enter a new name and path in the File Name field. Give it an easy to understand name in the Friendly Name box. Once everything is set, select Finish to open the Import Data dialog box.
Select Table and choose to put the data in either an Existing worksheet or a New worksheet.
Select OK to create an Excel list and import 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.

Go to Data.
In the Get & Transform group, select the New Query dropdown arrow and select From database > From SQL Server Database. If you don't see the New Query option, select the Get Data dropdown arrow.
In the SQL Server database dialog box, enter the name of your server (ex. MTP\SQLEXPRESS) in the Server text box. Give your database a friendly name in the Database text box.
If you want your imported data to use a native database query, select Advanced options and enter in the query in the SQL statement field.
Once you have all the parameters set, select OK.
The SQL Server database login opens. Choose 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, select 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 select OK in the message confirmation box.
Your data imports into Excel once the 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, select Table, and then select Refresh.

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.

In 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.