How to run SQL on an Excel spreadsheet

Excel workbooks are where most business data actually lives, and they are also where analysis becomes hardest to audit. Running SQL over a workbook keeps the file as the source of truth while making the questions asked of it explicit and repeatable.

Each sheet becomes a table

When a workbook is opened, every sheet is parsed into its own table with the sheet name as the table name and the first row as column headers. A three-sheet workbook gives you three tables you can query and join immediately.

SELECT *
FROM Sales
LIMIT 20;

Deal with headers that are not on row one

Exported workbooks often start with a title row, a blank row, and then the real headers. That shifts every column name, so the first thing to check after opening is whether the inferred column names look like data.

When they do, the fix is to remove the preamble rows in the source sheet and reopen — reliable, and it keeps the transformation visible instead of hidden in a query nobody will re-read.

Numbers stored as text

This is the single most common source of wrong answers in spreadsheet data. A column containing "1,200" or " 340 " parses as text, and any SUM over it either fails or silently ignores rows.

Cast explicitly rather than hoping the parser guessed right.

SELECT
  SUM(CAST(REPLACE(TRIM(amount), ',', '') AS DOUBLE)) AS total_amount
FROM Sales;

Replace VLOOKUP with a JOIN

A VLOOKUP chain across sheets is the spreadsheet equivalent of a join, but it breaks when columns move and it is difficult to review. The SQL version states the relationship once.

SELECT
  s.order_id,
  s.amount,
  p.product_name,
  p.category
FROM Sales s
JOIN Products p
  ON s.product_id = p.product_id;

Pivot without a pivot table

Conditional aggregation produces the same result as a pivot table and, unlike one, survives being re-run against next month's file without being rebuilt by hand.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4
FROM Sales
GROUP BY region
ORDER BY region;

Mixing a workbook with other files

Because every opened file becomes a table in the same session, a sheet from a workbook can be joined directly to a CSV export from another system without converting either one first.