The first thing most people do when they need to run a SQL query in Excel is open Power Query Editor and click 'Advanced Editor' — then paste in SELECT * FROM [Sheet1] WHERE... That won’t work. Power Query uses M, not SQL. You’ll get an error or worse: silent wrong results.
Quick Answer
Yes — but only via three supported paths: (1) Microsoft Query (legacy, built-in), (2) Power Query with native SQL pass-through to databases (not Excel sheets), or (3) ODBC + Data Connection with SQL statements in the connection string. You cannot run raw SQL against Excel ranges like A1:C10 using standard formulas or Power Query’s UI alone.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Microsoft Query (SQL Mode) | Data > Get Data > From Other Sources > From Microsoft Query > Excel Files > Select workbook > Write SQL in 'SQL' dialog (Alt+Q, S) | Ad-hoc filtering of local Excel tables without external tools | No JOINs across sheets unless defined as named ranges; no subqueries; case-insensitive only |
| Power Query + SQL Pass-Through | Connect to SQL Server/MySQL/PostgreSQL → use NativeQuery() in Advanced Editor → write real T-SQL or ANSI SQL | Live queries against enterprise databases from inside Excel | Does NOT work on Excel tables as source — only external DBs |
| ODBC + Connection String SQL | Data > Connections > Add new ODBC connection → edit connection string → append ';SQL=SELECT * FROM [Sales$] WHERE Amount > 10000' | Automated refreshable reports pulling filtered Excel sheet data | Fails if sheet name has spaces or special chars unless bracketed; no parameters |
| VBA + ADODB.Recordset | Write VBA that opens ACE.OLEDB provider → executes SQL against ThisWorkbook → returns results to range | Custom dashboards with dynamic WHERE clauses (e.g., user-input filters) | Requires macro-enabled workbook (.xlsm); blocked by many corporate security policies |
Method 1 Deep Dive
Let’s use Microsoft Query — the only method that lets you write SQL directly against your own Excel file.
Open a workbook with this data in Sheet1 (A1:E6):
| ID | Name | Company | Amount | Date |
|---|---|---|---|---|
| 101 | Sarah Chen | Acme Corp | $45,200 | 2024-03-15 |
| 102 | Diego Mora | Nexus Labs | $12,800 | 2024-02-22 |
| 103 | Priya Kapoor | Stellar Inc | $67,500 | 2024-04-01 |
| 104 | Marcus Bell | Acme Corp | $29,100 | 2024-01-30 |
| 105 | Anya Petrova | Nexus Labs | $33,400 | 2024-03-28 |
Now go to Data > Get Data > From Other Sources > From Microsoft Query > Excel Files. Browse to your file. In the Table Selection window, check Sheet1$. Click SQL (or press Alt+Q, then S). Paste this:
SELECT Name, Company, Amount FROM [Sheet1$] WHERE Amount > 30000 AND Company = 'Acme Corp'
Click OK. It returns Sarah Chen and Marcus Bell — correct. But here’s the surprise: column names in your SQL must match the *exact header text* in row 1 — including spaces and punctuation. If your header says “Sale Amount ($)”, you must write [Sale Amount ($)] in SQL. No aliases allowed in the SELECT clause before the FROM.
Method 2 Deep Dive
This one works only if you have live access to a database — but it’s the only way to run full ANSI SQL with JOINs, GROUP BY, and CTEs.
Assume you’re connected to a SQL Server instance with two tables: sales and customers.
In Power Query Editor, go to Advanced Editor. Replace the default code with:
let
Source = Sql.Database("prod-db.internal", "analytics", [Query="SELECT c.name, s.amount, s.date
FROM sales AS s
JOIN customers AS c ON s.customer_id = c.id
WHERE s.date > '2024-01-01'"])
in
Source
This pulls live data — no caching. You’ll see the results in Power Query. Click Close & Load.
Important: Do not try to wrap Excel ranges in Excel.CurrentWorkbook() and then apply NativeQuery(). It throws error 'The current data source does not support native queries.' That’s the #1 reason people think SQL-in-Excel doesn’t work — they’re misapplying the method.
Cheat Sheet
| Action | Shortcut / Path | Notes |
|---|---|---|
| Open Microsoft Query SQL dialog | Alt+Q, S | Only works after selecting Excel file source |
| Edit existing ODBC connection string | Data > Connections > Right-click conn > Properties > Definition tab > Connection string | Append ;SQL=... at end — no line breaks |
| Force refresh all queries | Alt+F5 | Critical for ODBC+SQL connections — manual refresh required |
| Check if sheet name is valid in SQL | Use brackets: [Q1 Report$], not Q1 Report$ | Spaces, hyphens, and parentheses require brackets |
| Test SQL syntax locally | Paste into Notepad first — avoid Word-style quotes | Smart quotes break everything. Use straight ASCII quotes only. |
