Guide

How to run SQL on an Excel spreadsheet

Most business data lives in Excel workbooks, and that’s also where analysis gets hardest to check. Run SQL over the workbook and the file stays the source of truth. Every question you ask of it is written down and easy to repeat.

Each sheet becomes a table

Open a workbook and every sheet turns into a table. The sheet name is the table name, and the first row becomes the column headers. Three sheets give you three tables, ready to query and join.

SELECT *
FROM Sales
LIMIT 20;

Headers that aren’t on row one

Exported workbooks love to start with a title row, a blank row, and then the actual headers. That throws off every column name. After you open the file, glance at the column names. If they look like data, that’s what happened.

The fix is boring and reliable: delete the extra rows in the sheet and open it again. That beats burying a workaround in a query nobody will reread.

Numbers stored as text

This causes more wrong answers in spreadsheet data than anything else. A column holding "1,200" or " 340 " comes in as text, and a SUM over it either fails or skips rows.

Cast it yourself. Don’t count on the parser guessing 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 a join in disguise. It breaks when someone moves a column, and it’s painful to review. In SQL you state 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 sums give you the same result as a pivot table. Next month you just run the query again instead of rebuilding the pivot 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;