This article is made  for those who regularly export data from some database to Excel in order to analyze them or process some kind of automated report (using formulas connected to imported dataset) like I do regularly for some of my clients. Usually, we insert the data into Excel simply by copying it from some company CRM/IT system. If you know basics of SQL language, you can do better! So, how to execute SQL Servwer query in Excel?

SQL Query in Excel – Example

The task is to connect to SQL Server database and execute a sql query below which contains sales by months, products and product categories

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 on how to run SQL query in Excel

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

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 authentication). Database admin should provide you with these. Click Advanced options

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)

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

Sometimes you need to execute SQL script with parameters, in use cases like these please use this tutorial – How to Execute SQL Procedure in Excel incl. Parameters with Example

In case you want to dive into SQL language deeper, please check SQL Tutorials category on my blog where you can find a page providing you a navigation to your learning journey.