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.

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.

   

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

   

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.

   

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

   

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

   

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.