XLsheetAI
Excel to SQL: The Formula Translation Guide
- SQL doesn't calculate cell by cell — every query operates on whole sets of rows at once, which is why "just typing the same logic" rarely works on the first try.
- Direct swaps: VLOOKUP → JOIN. SUMIF/SUMIFS → SUM() with WHERE or CASE WHEN. COUNTIF → COUNT() with a condition. Pivot table → GROUP BY with an aggregate function.
- The single most common Excel-to-SQL mistake is a JOIN that silently duplicates rows because the key isn't unique on one side — VLOOKUP can never do this, so it's a genuinely new failure mode.
- Window functions (RANK() OVER, running totals, LAG/LEAD) solve problems Excel usually needs helper columns for, and they're one of the best reasons to reach for SQL once a sheet gets large.
The short answer
Most Excel formulas have a direct SQL equivalent, mapped in the table below. The mismatch people hit isn't missing functionality — it's that Excel formulas reference specific cells, while SQL always operates on a full column across every row that matches a condition. Once that shift clicks, most Excel-to-SQL translation becomes mechanical, with two real exceptions: JOIN behavior and window functions, both covered further down.
Why Excel formulas don't translate 1:1
An Excel formula like =B2*C2 is anchored to row 2; dragging it down changes which row it points to. SQL has no equivalent of "the current row" written into the query text — price * quantity in a SELECT statement is evaluated for every row in the result set simultaneously, not one at a time in sequence. There's nothing to drag, because the calculation already applies everywhere it needs to.
The second shift is evaluation order. Excel evaluates a formula the instant you finish typing it. SQL evaluates a query in a fixed logical order — FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY — regardless of the order you typed the clauses in. That's why a column alias defined in SELECT can't be reused in the same query's WHERE clause: WHERE runs before SELECT does.
The complete Excel-to-SQL translation table
These are the formulas people search for most when moving from spreadsheets to a database, matched to the SQL pattern that behaves the same way against a real table — not just the keyword with the closest-sounding name.
| Excel formula | SQL equivalent | Note |
|---|---|---|
| VLOOKUP / XLOOKUP | JOIN (usually LEFT JOIN) | Matches rows on a shared key across two tables |
| SUMIF | SUM(CASE WHEN condition THEN value ELSE 0 END) | Or filter with WHERE first, then plain SUM() |
| SUMIFS | SUM(...) with multiple WHERE conditions or GROUP BY | Each extra IF condition becomes another AND clause |
| COUNTIF / COUNTIFS | COUNT(CASE WHEN condition THEN 1 END) | Or COUNT(*) after filtering with WHERE |
| AVERAGEIF | AVG(CASE WHEN condition THEN value END) | NULLs from unmatched rows are excluded automatically |
| IF | CASE WHEN condition THEN result ELSE result END | The standard conditional building block in SQL |
| Nested IF (3+ levels) | CASE WHEN ... WHEN ... WHEN ... END | Add another WHEN clause per condition, no real nesting needed |
| IFERROR | COALESCE() or TRY_CAST() | COALESCE substitutes a fallback for NULL; TRY_CAST avoids type-conversion errors |
| CONCATENATE / & | CONCAT() or || | || is the ANSI standard; CONCAT() is more widely supported across engines |
| UNIQUE / Remove Duplicates | SELECT DISTINCT | Applies to the whole row unless you name specific columns |
| Pivot table (group + aggregate) | GROUP BY with SUM/COUNT/AVG | Some engines also offer a dedicated PIVOT operator |
| RANK.EQ | RANK() OVER (ORDER BY col DESC) | Add PARTITION BY to rank within groups, like per region |
| Running total (SUM($A$1:A1)) | SUM(col) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) | No fill-down needed — every row calculates its own total |
GROUP BY and the SELECT ordering trap
SQL enforces a rule Excel has no equivalent for: every column named in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. Selecting a raw column that isn't grouped throws an error in most databases, because SQL has no way to know which of the many matching rows' values you actually want for that column.
This is the single most common first error for someone writing their first grouped query — the instinct from a pivot table is "just show me these columns," but SQL needs to be told explicitly whether each extra column is a grouping key or something to aggregate.
JOINs, explained through VLOOKUP
An INNER JOIN behaves like a VLOOKUP that quietly drops any row with no match at all — only rows present in both tables survive. A LEFT JOIN behaves like a VLOOKUP that keeps every row from the first table and fills in NULL wherever the second table had nothing to match, which is the closer analogy to how most people actually use VLOOKUP in practice.
The real difference — and the one with no Excel precedent — is what happens when the join key isn't unique. VLOOKUP always returns exactly one value, even if multiple rows could technically match; it just grabs the first one it finds. A JOIN returns every matching pair, so if a customer ID appears three times in the second table, that customer's row from the first table gets returned three times too. This is the most common reason a query's row count balloons unexpectedly — it's not a bug, it's the join key being less unique than assumed.
Window functions: the feature Excel doesn't really have
A running total in Excel means writing =SUM($A$1:A1) in the first row and dragging it down, with the anchored start and the moving end doing the work. A ranking means RANK.EQ against a fixed range that has to be reselected if rows are added. Both approaches work, but both depend on manual range management that breaks if the sheet's shape changes.
SQL's window functions solve the same problems without any dragging or reselecting. SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) computes a running total per row automatically. RANK() OVER (PARTITION BY region ORDER BY sales DESC) ranks every row within its own region in one pass. LAG(amount) OVER (ORDER BY month) pulls the previous row's value for month-over-month comparisons — the SQL answer to an Excel formula referencing "the cell one row up," except it keeps working correctly no matter how the data is resorted or filtered afterward.
A worked example: monthly sales by category
In Excel, a monthly total per category usually means a SUMIFS referencing both a category column and a date range, repeated for every month-category combination, or built through a pivot table instead. The SQL version states the grouping once and lets the database do the repetition:
| Goal | SQL |
|---|---|
| Total sales per category per month | SELECT category, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total FROM sales GROUP BY category, DATE_TRUNC('month', order_date) |
| Only categories over $10,000 that month | Add: HAVING SUM(amount) > 10000 |
Notice the filter on the aggregated total uses HAVING, not WHERE — WHERE filters rows before grouping happens, HAVING filters groups after the SUM is already calculated. That distinction has no Excel equivalent, because a pivot table's value-area filter always operates on the already-summarized numbers.
NULL is not the same as Excel's blank cell
An empty Excel cell behaves like zero in arithmetic and like an empty string in text formulas, quietly, without complaint. SQL's NULL doesn't behave like anything — it represents "unknown," and any calculation touching a NULL returns NULL rather than a number. 5 + NULL is NULL, not 5, and NULL = NULL evaluates to unknown rather than true, which is why filtering for missing values needs WHERE column IS NULL instead of WHERE column = NULL.
This trips up aggregate functions in a specific way: AVG() and COUNT() both silently exclude NULL rows rather than treating them as zero, so an average calculated in SQL can differ from the same-looking AVERAGEIF in Excel if some of the underlying cells were blank versus genuinely zero. When the distinction matters, wrap the column in COALESCE(column, 0) to force NULLs to zero before aggregating, matching Excel's blank-as-zero behavior on purpose instead of by accident.
Subqueries: nesting a query inside a query
Excel's version of "calculate something, then use that result in another formula" usually means a helper column holding the intermediate value. SQL's subquery does the same job without a separate column at all — a query can sit inside another query's WHERE, FROM, or SELECT clause, and it runs first, feeding its result into the outer query.
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'West') is the SQL equivalent of first filtering a customer list, then using that filtered list to filter orders — two SUMIFS-style steps in Excel collapsed into one query. Once a subquery starts feeling natural, a JOIN is usually the cleaner way to express the same logic, but subqueries are the easier first step for anyone still thinking in terms of separate helper-column stages.
Still building the Excel side of this by hand?
Describe the formula you need in plain English and XLsheetAI writes it for you, explains the logic, and lets you practise it hands-on — the exact groundwork worth getting right before it becomes a SQL query.
Coming from Power BI instead of a database? See the Excel to DAX translation guide, or check XLOOKUP vs VLOOKUP before you translate a lookup formula into a JOIN.
FAQ
What is the SQL equivalent of VLOOKUP?
A JOIN. VLOOKUP pulls a matching value from a second table based on a shared key, which is exactly what a JOIN does between two tables on a matching column, except a JOIN can return matches from both tables at once instead of one column at a time.
How do I write a SUMIF or SUMIFS in SQL?
Two common patterns: filter the rows first with a WHERE clause and then SUM(), or keep all rows and use SUM(CASE WHEN condition THEN value ELSE 0 END) so several conditional sums can sit side by side in one query.
Why does my JOIN return more rows than either table has?
The join key isn't unique on at least one side, so every match multiplies rather than replaces. A VLOOKUP always returns exactly one value per row; a JOIN returns one row per matching pair, so if a key appears three times on one side, every row on the other side gets tripled.
What's the SQL version of a pivot table?
GROUP BY combined with aggregate functions like SUM, COUNT, or AVG produces the same grouped totals a pivot table shows. Some databases also offer a PIVOT operator that reshapes rows into columns directly, but GROUP BY alone covers most pivot-table use cases.
Does Excel have anything like SQL window functions?
Not natively. Window functions like RANK() OVER or a running total with ROWS BETWEEN require formula workarounds in Excel, such as dragging a fill-anchored SUM down a column, where SQL calculates them directly without duplicating or restructuring any data.