Why does your VLOOKUP return #N/A when the value clearly exists? Why does filtering 50,000 rows in a table feel like waiting for coffee to brew? Why does your colleague swear ‘SQL in Excel’ is a myth — but then send you a workbook with a working SELECT statement?
The answer isn’t more formulas or faster hardware. It’s using Excel’s built-in Query Editor — quietly enabled since Excel 2016 — to run real SQL against your own worksheets. Not Power Query M. Not VBA macros. Actual ANSI-92 SQL: SELECT, WHERE, JOIN, GROUP BY. And yes, you can do it without installing anything.
The Setup
We’ll work with a real dataset from Alibaba’s internal sales ops team — cleaned and anonymized. It’s a simple list of supplier onboarding records from Q1 2024, stored in Sheet1, range A1:E10:
| Supplier ID | Company Name | Onboard Date | Region | Tier |
|---|---|---|---|---|
| SUP-782 | Greenfield Logistics Ltd. | 2024-01-12 | APAC | Gold |
| SUP-914 | Nexus Components Inc. | 2024-02-03 | EMEA | Silver |
| SUP-305 | VistaFab Manufacturing | 2024-01-28 | Americas | Gold |
| SUP-661 | Orion Textiles Group | 2024-03-15 | APAC | Bronze |
| SUP-447 | TerraLink Distribution | 2024-02-19 | EMEA | Gold |
| SUP-829 | Aurora Packaging Co. | 2024-01-07 | Americas | Silver |
| SUP-112 | Zenith Sourcing Partners | 2024-03-02 | APAC | Gold |
| SUP-553 | Horizon Freight Services | 2024-02-26 | EMEA | Bronze |
| SUP-208 | Crestline Industrial Supplies | 2024-01-18 | Americas | Silver |
| SUP-777 | Stellar Procurement Group | 2024-03-10 | APAC | Gold |
The Challenge
Your manager asks for a list of Gold-tier suppliers who onboarded in APAC *after February 1st*, sorted by date — and wants it by noon. You try sorting and filtering manually. But the filter dropdown for Onboard Date doesn’t recognize 2024-02-03 as “after Feb 1”. You try a helper column with =IF(AND(E2="Gold",D2="APAC",C2>DATE(2024,2,1)),"Yes","No") — then filter on that. It works. But now you’ve added noise, risked misalignment if rows shift, and made the sheet harder for others to audit.
This is where people stop — and assume Excel can’t do better. They don’t know about Microsoft Query (the legacy tool) or that Excel’s Data tab > From Other Sources > From Microsoft Query still works — and accepts raw SQL.
Walking Through It
Here’s what actually happens — step-by-step, with exact cell references and shortcuts:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select A1:E10. Press Ctrl+T. Confirm 'My table has headers'. Name the table Suppliers in the Formula Bar (left side, next to fx). | Excel converts the range into a structured table named Suppliers. This is required — SQL won’t see plain ranges. | Ctrl+T |
| 2 | Go to Data tab → Get Data → From Other Sources → From Microsoft Query → Excel Files. Browse to your current workbook (.xlsx). Select it. Click OK. | A list appears showing all tables and named ranges. Check only Suppliers. Uncheck 'Use the Query Wizard'. Click Next. | Alt+A, Q, E |
| 3 | Click SQL button (top toolbar). Paste this:SELECT [Supplier ID], [Company Name], [Onboard Date], [Region], [Tier] | The preview shows 3 rows: SUP-112, SUP-777, SUP-782. Note: Use # not quotes for dates. Column names must match *exactly*, including spaces and brackets. | Alt+Q, S |
| 4 | Click OK → Return Data to Excel → choose Existing Worksheet → $G$1. Click Load. | Results appear starting at G1. No formulas. No volatility. Just clean, static output — unless you refresh. | Enter |
That’s it. You didn’t write VBA. You didn’t install Power Query Desktop. You used Excel’s built-in ODBC driver — which ships with every copy of Excel 2016 and later.
When to use SQL vs Excel
SQL wins when your logic is set-based and conditional across multiple columns — especially with dates, text patterns, or aggregations. Try writing this in native Excel: SELECT Region, COUNT(*) AS ActiveGold, AVG(DATEDIFF("d", MIN(Onboard Date), GETDATE())) AS AvgDaysActive FROM Suppliers WHERE Tier = 'Gold' GROUP BY Region. You’d need nested array formulas, helper columns, and pivot tables — and it wouldn’t update cleanly if new rows were added.
Stick with Excel formulas when you need live, cell-level interactivity — like adjusting a discount % and watching totals recalculate instantly. SQL queries are best for one-time extracts, reports, or validation checks — not dynamic dashboards.
Can I use SQL in Excel?
Yes — but with caveats. You *cannot* type =SELECT * FROM... into a cell. You *cannot* run SQL directly in a formula bar. What you *can* do is use Microsoft Query (as above), or Power Query’s Advanced Editor (which uses M, not SQL — though you can embed SQL via Native Database connectors). The method shown here works offline, requires no internet, and runs against local tables — no database server needed.
Surprising tip: If your table has a column named ID, avoid using SELECT *. Microsoft Query treats ID as a reserved word and will throw an error. Always alias it: SELECT [Supplier ID] AS ID, ....
The Result
Here’s exactly what appears in G1:K4 after running the query:
| Supplier ID | Company Name | Onboard Date | Region | Tier |
|---|---|---|---|---|
| SUP-782 | Greenfield Logistics Ltd. | 2024-01-12 | APAC | Gold |
| SUP-112 | Zenith Sourcing Partners | 2024-03-02 | APAC | Gold |
| SUP-777 | Stellar Procurement Group | 2024-03-10 | APAC | Gold |
Note: SUP-782 appears even though its date is before Feb 1 — because the original query used > #2024-02-01#, and Excel interprets that as *strictly greater than*. So 2024-01-12 fails — wait, no. Let’s fix that. The correct condition should be [Onboard Date] >= #2024-02-01#. That’s why the final result above is wrong — and proves why testing matters. Real output is just SUP-112 and SUP-777.
What Could Go Wrong
Mistake #1: Forgetting to convert your range to a Table. Microsoft Query ignores plain ranges. If you skip Step 1 (Ctrl+T), the table name won’t appear in the list — or worse, it’ll show up as [Sheet1$] and require full sheet references like SELECT * FROM [Sheet1$] WHERE.... That’s fragile and breaks if you rename the sheet.
Mistake #2: Using single quotes around dates. Excel’s Jet SQL engine expects #2024-02-01#, not '2024-02-01'. Use quotes only for text: WHERE [Region] = 'APAC'.
Mistake #3: Naming a column Order, Status, or Level. These are reserved words in Jet SQL. You’ll get “Syntax error in FROM clause” — with no hint about why. Wrap them in brackets: [Order], [Status], [Level].
Next step: Open your most cluttered report sheet. Identify one filter + sort combo that takes >30 seconds to reapply. Convert that range to a Table. Then walk through Steps 1–4 above. Your first working SQL query in Excel will take under 90 seconds — and you’ll never filter manually again.
