Home › Guides › SQL on an Excel spreadsheet

How to Run SQL on an Excel Spreadsheet

Excel is excellent until the question gets complicated. Past a certain point you are maintaining a nest of SUMIFS and VLOOKUP formulas that nobody — including you, next month — can read. SQL expresses the same questions in a line or two.

When SQL starts to win

Roughly at the point where any of these becomes true:

You are copying a formula down forty thousand rows and waiting for it
You need data from a second sheet or a second workbook
The answer requires grouping, and the pivot table cannot quite express it
You want the same answer again next month without rebuilding anything
Someone sorted the sheet and your lookups silently returned the wrong values

Loading the workbook

Open the tool and drag your .xlsx, .xls or .xlsm file onto the File Select panel.
Pick the worksheet in the Sheet dropdown. Only the sheet you select is loaded.
If the real headers are not on row 1 — a title block above them, say — set Skip Rows to the number of junk rows, and leave Header Row ticked.
Note the letter in the Alias column. budget.xlsx becomes B. That is your table name.

SELECT * FROM B LIMIT 20

Your formulas, in SQL

Excel	SQL
`=SUM(C:C)`	`SELECT SUM(amount) FROM B`
`=SUMIF(A:A,"West",C:C)`	`SELECT SUM(amount) FROM B WHERE region = 'West'`
`=COUNTIF(A:A,"West")`	`SELECT COUNT(*) FROM B WHERE region = 'West'`
`=AVERAGEIF(…)`	`SELECT AVG(amount) FROM B WHERE …`
`=COUNTA(UNIQUE(A:A))`	`SELECT COUNT(DISTINCT region) FROM B`
A pivot table	`SELECT region, SUM(amount) FROM B GROUP BY region`
`=VLOOKUP` / `=XLOOKUP`	A `JOIN` — see the joining guide
`=FILTER(…)`	`SELECT * FROM B WHERE …`
Sort + top rows	`SELECT * FROM B ORDER BY amount DESC LIMIT 10`
`=IF(x>100,"big","small")`	`CASE WHEN amount > 100 THEN 'big' ELSE 'small' END`

Where SQL pulls decisively ahead is combining them. This is a pivot table, a filter and a sort at once:

SELECT region,
               COUNT(*) AS deals,
               SUM(amount) AS revenue,
               AVG(amount) AS avg_deal
        FROM B
        WHERE status = 'closed' AND amount > 0
        GROUP BY region
        HAVING SUM(amount) > 10000
        ORDER BY revenue DESC

Drop in an .xlsx and query it. Your workbook is never uploaded.

Open the tool →

Excel quirks that cause trouble

Quirk	What happens, and what to do
Merged cells	Only the first cell of a merge holds the value; the rest read as empty. Unmerge before exporting, or expect gaps.
Two-row headers	A header split across rows cannot both be read. Flatten it to a single row in Excel first.
Numbers stored as text	Excel's little green triangle. These arrive as text, so `WHERE amount > 100` will not match. Either fix it in Excel or use `CAST(amount AS NUMBER)`.
Leading zeros	A code like `00042` is deliberately kept as text so it is not corrupted. Compare it quoted: `WHERE code = '00042'`.
Dates	Excel dates can arrive as serial numbers or as text depending on the cell format. Check with `SELECT order_date FROM B LIMIT 5` before filtering, and compare as text when they are text: `WHERE order_date >= '2024-01-01'`.
Total rows at the bottom	A “Grand Total” row becomes a data row and skews your aggregates. Exclude it: `WHERE region IS NOT NULL AND region <> 'Total'`.
Formulas	The calculated value is read, not the formula. That is almost always what you want.

Getting it back into Excel

Run your query, click Export, choose Excel. You get an .xlsx of the result set on a single sheet, generated in your browser and saved straight to your downloads folder. The original workbook is never modified and never uploaded.

OmniSelect FileSQL