Can you connect excel to a database?

In this informative article, we will explore how to create an Excel connection to MS SQL database and load data from SQL Server to Microsoft Excel.

Table of Contents Show

How to create an Excel Connection to SQL database
Refresh/Update an Excel Connection to a SQL database
Change the SQL query statement using external data properties
Can Excel read from a database?
What database can I use with Excel?
Is Excel compatible with SQL?
How do I create a SQL connection in Excel?

Microsoft Excel is a powerful tool to evaluate and analyze the data in various ways. Excel provides features to connect to various databases; for example - MS SQL Server database, Access database, etc. Using these database connections, you can import external data into Excel using SQL query or statements. You can then sort it, analyze it, and perform any calculation that you might need.

This database connection must be validated by a SQL login to ensure that the content of your databases remains secure and safe.

The main advantage of these database connections is - you update or refresh the Microsoft Excel data directly from the database on a real-time basis using the Excel refresh command.

How to create an Excel Connection to SQL database

To create an Excel connection, follow the below-mentioned steps.

Step 1

Open Microsoft Excel file and go to the Data tab on the Excel Ribbon (Under menu bar).

Step 2

Click "From other sources" icon in the "Get External Data" section and select "From SQL Server" on the dropdown menu.

Step 3

After the selection of "From SQL Server", the Data Connection Wizard window opens.

Enter the server name and click on "Use the following User Name and Password" radio button under the Log on credentials and enter the database username and password in appropriate boxes. Then, click the "Next" button.

Server name - Server name box must contain the name of the computer where SQL Server is installed.
User name - your SQL Server user name. Required only for SQL Server authentication.
Password - your SQL Server password. Required only for SQL Server authentication.

Step 4

After clicking on the "Next >" button, a new popup window will open. Select the required database from the “select the database that contains the data you want” dropdown list. After selection of database, a list of tables appears, as in the below-given screenshot. Then, select the required table (which you want to load data) and click on the "Next>" button.

Database - The name of the SQL database to connect to Microsoft Excel.

Step 5

Click on the "Finish" button to load data from SQL Server Database to Microsoft Excel file.

Refresh/Update an Excel Connection to a SQL database

To refresh the Excel Connection follow the below-given steps.

On your worksheet, click any cell that contains your SQL Server data.
On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click "Refresh".

Note

Clicking the "Refresh All" button in the Connections group on the Data tab will instantly update the information from all data sources connected to the excel sheet.

Change the SQL query statement using external data properties

Click any cell that contains your SQL Server data.
In the Connections group, click the arrow next to Refresh All, and click Connection Properties.

Then Click on connection properties icon (marked in red) to open connection property window and go on the Definition tab,

Modify the Connection string to connect to a different database using a connection string.

Modify the Command Type to SQL for Queries or Table for a table. There are three options (Default, SQL and table).

Modify the Command text to enter a SQL query to select or load particular data.

Click the “OK” button to close the Connection Properties window. Edit other data in the spreadsheet and save the workbook as needed.

That is it for this article. I hope you have learned something new from this article. Please comment below for your feedback and queries.

Can Excel read from a database?

You can also import data into Excel as either a Table or a PivotTable report. Select Data > Get Data > From Database > From SQL Server Analysis Services Database (Import).

What database can I use with Excel?

You can use Excel to connect to a wide variety of data sources including Access, SQL Server and Analysis Services databases, text and XML files, and ODBC and OLE DB data sources. However, you cannot edit the data to change the source data through the Excel user interface.

Is Excel compatible with SQL?

Microsoft Excel can be a powerful tool to evaluate SQL data. In Excel, a connection can be created to directly link to a particular database filtered according to your requirements. This allows you to report SQL data, attach a table of data into Excel, create a pivot table and have better manipulation of your SQL data.

How do I create a SQL connection in Excel?

In the SQL Spreads Designer panel on the right side, click the Edit button to open the SQL Server connection dialog. Select if you should connect using your Windows login (Windows Authentication) or enter a user name and password (SQL Server Authentication).