Stop Using Excel Filters — SQL Works Better (Here’s How)

The first thing most people do when they need to filter or join sales data across sheets is write a bunch of nested IFs or drag AutoFilter. That’s usually the wrong move — especially when you’ve got 50k rows from CRM and ERP systems. SQL isn’t just for databases. It lives inside Excel — quietly, powerfully, and often misconfigured.

Power Query SQL vs. Legacy MS Query

Criterion	Power Query (Get & Transform)	Legacy MS Query (ODBC)
Setup time	2 clicks → Data tab → From Table/Range	8+ steps: Data → Get Data → Legacy Wizards → ODBC → DSN config
SQL editing	Hidden behind Advanced Editor (Ctrl+E); syntax highlighted, auto-indent	Raw text box with zero validation; no autocomplete, no error hints
Handles 100k+ rows?	Yes — loads into memory only on refresh, not load	Often crashes Excel; requires manual chunking
Joins multiple Excel sheets	Yes — use Table.NestedJoin or Merge Queries	No — only external sources or single-sheet ranges via named ranges
Refreshes with new data	Yes — automatic if source range expands (with dynamic tables)	No — must re-run query manually; breaks if sheet names change

When to Use Power Query SQL

Use Power Query when your source lives in Excel itself — like pulling order history from Sheet1 and matching it to customer info in Sheet2.

Example: You have Sheet1 (Orders) with columns A:D — OrderID (A2:A1042), CustomerID (B2:B1042), Amount (C2:C1042), Date (D2:D1042). And Sheet2 (Customers) with A:C — CustomerID (A2:A87), Name (B2:B87), Region (C2:C87).

Do this: Select any cell in Sheet1 → Data tab → From Table/Range → OK → Advanced Editor → paste this:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Joined = Table.NestedJoin(Source,{"CustomerID"},Customers,{"CustomerID"},"Customers",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined,"Customers",{"Name","Region"})
in
    Expanded

Result lands in a new worksheet. No VLOOKUP. No volatile formulas. Just clean, reusable logic.

When to Use Legacy MS Query

Use legacy MS Query only when connecting to an actual SQL Server, Oracle, or Access .mdb file — and you need to push filtering *to the server* before pulling data.

Example: Your finance team stores daily GL entries in SQL Server. You don’t want all 2M rows — just Q1 2024 transactions over $10,000.

Do this: Data → Get Data → Legacy Wizards → From Database → Microsoft SQL Server → enter server name → select database → click Next → type this in the SQL box:

SELECT t.Account, t.Amount, t.PostDate, u.UserName
FROM dbo.GL_Transactions t
JOIN dbo.Users u ON t.UserID = u.UserID
WHERE t.PostDate BETWEEN '2024-01-01' AND '2024-03-31'
  AND t.Amount > 10000

Then click OK → Load. This runs on the server. Excel never sees the other 1.98M rows.

Surprising tip: If you paste that same SQL into Power Query’s Advanced Editor *after connecting to SQL Server*, it’ll still run on the server — but you get Power Query’s UI, parameters, and error handling. So skip legacy MS Query entirely unless you’re stuck on Excel 2010.

The Hybrid Approach

Combine both methods when you need SQL-level control *and* Excel-native agility.

Scenario: Sales team updates a local Leads.xlsx file weekly (12K rows). Marketing sends a WebEvents.xlsx log (85K rows). You need to flag leads who visited pricing page within 48 hours of lead creation.

Step 1: In Power Query, load Leads.xlsx as Leads. Promote headers. Change Date column to DateTime.

Step 2: Load WebEvents.xlsx as Events. Filter for Page = "pricing.html" only. Keep EventTime, LeadID.

Step 3: In Advanced Editor, add this merge step:

let
    ...
    Merged = Table.Join(Leads,{"LeadID"},Events,{"LeadID"},JoinKind.Inner),
    Filtered = Table.SelectRows(Merged,
        each [EventTime] >= [CreatedDate] and [EventTime] <= [CreatedDate] + #duration(2,0,0,0))
in
    Filtered

This runs entirely in Power Query — no SQL Server needed. But it uses SQL-like logic (date math, joins, filters) — and refreshes with one click.

Performance Benchmarks

Task	Power Query SQL	VLOOKUP + FILTER	Legacy MS Query	Power Pivot DAX
Join 65K orders + 9K customers	1.8 sec (refresh)	42 sec (calc + recalc)	Crashed Excel (2019)	3.1 sec (model load)
Filter 120K web logs for 3 URLs	0.9 sec	18 sec (array formula)	2.3 sec (server-side)	1.4 sec
Calculate YoY % from 2022–2024 sales	2.1 sec (Group By + custom column)	67 sec (SUMIFS across 3 tabs)	N/A (no date math support)	1.6 sec
Append 3 monthly reports (25K rows each)	0.4 sec (Append Queries)	Manual copy-paste → 3 min	Not possible	0.7 sec

Next step: Open any Excel file with two related tables. Try this now — no setup required.

Step	Action	Result	Shortcut
1	Select cell A1 in your main table	Excel detects full data range	Ctrl+A (twice)
2	Data tab → From Table/Range	Power Query Editor opens	Alt+A,T
3	Home tab → Advanced Editor	Blank script window appears	Ctrl+E
4	Paste sample SQL (see above)	Results update instantly	Ctrl+V