How to Connect Microsoft Excel to an SQL Server

A live connection to SQL Server keeps spreadsheet data fresh

by
Tim Perdue
Writer
Former Lifewire writer Tim Perdue is a leader in information technology with more than 20 years of IT experience in corporate IT and financial systems management.
our editorial process
Tim Perdue
Updated on January 21, 2020

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.

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

Connect Excel to a SQL Server Database

You must have a valid link to a SQL Server to connect with Excel.

  1. Collect the information needed to set up the Excel to SQL Server connection:

    • SQL Server Name
    • Database Name
    • Table or View

  2. Open Excel and create a new workbook.

  3. Go to Data and select Get Data > From Database > From SQL Server Database. This option opens the Connect to Database Server dialog box.

    Import data from sql server

  4. Enter the Server name. Specify advanced options, including a specific database name, then select OK.

    database selection

  5. The next screen offers three options for authentication, based on the options in the right sidebar. In most cases, select the Windows tab then select the radio button for Use my current credentials. Select Connect.

    If the database administrator provided a username and password, select Use the following User Name and Password to enter the provided credentials.

    sql server selection

  6. From the Navigator, pick a specific object, then select Load from the bottom of the screen.

    sql server navigator in excel

  7. The wizard exits, displaying the contents of the selected database object.

    Excel with SQL Server connection.

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.

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.