How to Execute SQL Query in Excel (Office 365)? Tutorial with Examples

Last modified date:

This article is made especially for those who regularly export large data volumes to Excel in order to analyze them or process some kind of regular report. Usually, we insert the data into Excel simply by copying it from some factory system. But data can also be inserted into excel in more suitable ways which will save your time. The most suitable one is PowerPivot or Power Query, but there are some other possibilities. If you know basics of SQL language, you can prepare SQL Query in Excel and create fully automated report.

SQL Query in Excel – Example

Task Assignment

The task is to connect into SQL Server SQL database and execute a sql query which contains sales in relation to months, products and product categories. After that, it is needed to create a connection to the database and prepare a simple dataset for the report.

Script:

SELECT
    [b].[CalendarYear],
    [b].[MonthNumberOfYear],
    [c].[EnglishProductName],
    [d].[EnglishProductSubcategoryName],
    SUM([a].[SalesAmount]) AS [SalesAmount]
FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] [a]
       JOIN [AdventureWorksDW2014].[dbo].[DimDate] [b]
         ON [a].[OrderDateKey] = [b].[DateKey]
       JOIN [AdventureWorksDW2014].[dbo].[DimProduct] [c]
         ON [a].[ProductKey] = [c].[ProductKey]
       JOIN [AdventureWorksDW2014].[dbo].[DimProductSubcategory] [d]
         ON [c].[ProductSubcategoryKey] = [d].[ProductSubcategoryKey]
GROUP BY
    [b].[CalendarYear],
    [b].[MonthNumberOfYear],
    [c].[EnglishProductName],
    [d].[EnglishProductSubcategoryName];

Steplist

1 – Open excel and set up a sheet where the data will be connected to the database later on

2 – Lets go to card Data – from other sources – from SQL Server

datasource sql query - how to execute sql query from excel

3 – Write down name of the SQL Server instance where the data are stored. If it is needed to connect to the database, your Windows account should have appropriate user permissions (in case of Windows system verification). Database admin should provide you with these. Click Advanced options

connect to sql server - how to execute sql query from excel

4) Insert our script into the SQL statement section

5) Table will load into Excel after confirmation of following window as a result of SQL query or you can edit/transform data using power query if you want (Transform data button)

sql script preview - how to execute sql query from excel

6) After confirmation, data has been successfully loaded into Excel sheet as result of our SQL script

data has been successfully loaded - how to execute sql query from excel_

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Excel tutorials

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.