Translating Excel Formulas Into SQL With Worked Examples

Somewhere in your company there is a workbook that calculates something important, and one person understands it. It has a lookup tab, a column of nested IFs, a pivot table and a running total that someone extended by dragging. It works until it has too many rows, or someone wants the same numbers in a report, or the person who understands it leaves. Then the question is how to move the logic into SQL, where it can be tested, versioned and run on a schedule.

This guide is the map for translating Excel formulas into SQL. It sets out the change of mindset, works through the main families of formulas on one small dataset, and ends with a way to prove the translation correct. The focused articles carry the detail: VLOOKUP and XLOOKUP versus JOIN, SUMIF and SUMIFS versus GROUP BY, COUNTIF versus COUNT with a filter, IF and IFS versus CASE, pivot tables versus GROUP BY and CASE, INDEX and MATCH versus a join, running totals versus window functions, removing duplicates versus SELECT DISTINCT, text functions, date functions and why SQL beats formulas on large data. Getting the workbook's data into a database is covered in getting Excel data into a database.

From cells to sets

A spreadsheet formula lives in a cell and refers to other cells. You write it once and drag it down, so row 2 computes from row 2, row 3 from row 3. The logic is per row, the layout is the program, and the results sit next to the inputs. SQL is the opposite: you state what you want from a set of rows, and the engine decides how to produce it. There are no cell addresses, only columns and rows, and a calculation applies to all rows at once.

This changes how you read a workbook. A column of =B2*C2 copied down is SELECT qty * price over a table. A cell with =SUM(D2:D500) is an aggregate over a column. A lookup into another sheet is a join to another table. A block of cells that refer to each other in a chain, each depending on the one above (a running balance), is a window function. A pivot table is an aggregation with grouping.

Before you translate, find out what the workbook actually does. Read the formulas rather than the values (reading only the values and not the formulas), group them by shape (the same formula copied down a column is one rule), and list the inputs: which cells are data, which are parameters such as a tax rate, which are intermediate results. The inputs become tables or constants; the intermediate results become expressions or views; the outputs become queries. Many workbooks turn out to contain fewer than ten distinct rules, repeated over thousands of rows.

A small dataset

All examples use this data, written as common table expressions so you can paste any query into a client and run it:

WITH orders(order_no, customer_id, region, amount, placed) AS (
  VALUES
    (1, 10, 'North', 120.00, DATE '2024-03-01'),
    (2, 10, 'North',  80.00, DATE '2024-03-03'),
    (3, 11, 'South',  45.50, DATE '2024-03-05'),
    (4, 12, 'North', 200.00, DATE '2024-03-09'),
    (5, 11, 'South',  30.00, DATE '2024-03-12')
),
customers(id, name, tier) AS (
  VALUES (10, 'Acme', 'gold'), (11, 'Globex', 'silver'), (12, 'Initech', NULL)
)

Imagine the same data on two sheets, Orders in A:E and Customers in G:I. Each section below shows the Excel formula, the SQL and the result for this data.

Lookups become joins

=VLOOKUP(B2, Customers!G:I, 2, FALSE) finds the customer name for the id in B2. The SQL is a join on the key:

SELECT o.order_no, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_no;

 order_no |  name
----------+---------
        1 | Acme
        2 | Acme
        3 | Globex
        4 | Initech
        5 | Globex

Use LEFT JOIN so that an order without a matching customer still appears, with a NULL name, which is the equivalent of VLOOKUP's #N/A made visible. INNER JOIN would drop the row silently, which is a difference worth choosing deliberately. XLOOKUP and INDEX/MATCH translate the same way. Two traps: VLOOKUP with the approximate-match flag (TRUE, or omitted) finds the largest value not exceeding the key in a sorted table, which is a range lookup and needs a different query (LATERAL with ORDER BY ... LIMIT 1, or a window function); and VLOOKUP returns the first match when keys repeat, while a join returns every match, multiplying rows. Check uniqueness of the lookup key before joining (VLOOKUP and XLOOKUP versus SQL JOIN, INDEX and MATCH versus a join on a key).

Conditional sums and counts become aggregates

=SUMIF(C:C, "North", D:D) sums amounts where the region is North. =COUNTIF(C:C, "North") counts them. In SQL:

SELECT
  sum(amount)   FILTER (WHERE region = 'North') AS north_total,
  count(*)      FILTER (WHERE region = 'North') AS north_orders,
  sum(amount)   FILTER (WHERE region = 'South') AS south_total
FROM orders;

 north_total | north_orders | south_total
-------------+--------------+-------------
      400.00 |            3 |       75.50

The FILTER clause is standard SQL supported by PostgreSQL, SQLite and DuckDB; engines without it use SUM(CASE WHEN region = 'North' THEN amount END), which gives the same result. When the formula is copied once per region, the SQL is a single GROUP BY region:

SELECT region, sum(amount) AS total, count(*) AS orders
FROM orders
GROUP BY region
ORDER BY region;

which returns North with 400.00 and 3 orders and South with 75.50 and 2. SUMIFS with several conditions adds AND terms. COUNTIF with a wildcard ("N*") is a LIKE 'N%' (SUMIF and SUMIFS versus GROUP BY, COUNTIF versus COUNT with a filter). Beware of the match rules: Excel's text comparison is case-insensitive, while SQL's depends on the collation and the engine, and PostgreSQL's is case-sensitive.

IF, IFS and nested IFs become CASE

=IF(D2>=100, "large", "small") is CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END. A nested chain or an IFS becomes a CASE with several WHEN branches, evaluated in order:

SELECT order_no, amount,
  CASE
    WHEN amount >= 150 THEN 'large'
    WHEN amount >= 75  THEN 'medium'
    ELSE 'small'
  END AS size
FROM orders
ORDER BY order_no;

 order_no | amount | size
----------+--------+--------
        1 | 120.00 | medium
        2 |  80.00 | medium
        3 |  45.50 | small
        4 | 200.00 | large
        5 |  30.00 | small

A CASE without an ELSE returns NULL when nothing matches, where Excel's IF without a third argument returns FALSE. And IFERROR(x, 0) becomes COALESCE(x, 0) for a null result, or a safe-cast function for a conversion that may fail (IF and IFS versus CASE WHEN in SQL).

Pivot tables become conditional aggregation

A pivot table of total by region and week is a GROUP BY with one column per category:

SELECT region,
       sum(amount) FILTER (WHERE placed < DATE '2024-03-08') AS week_1,
       sum(amount) FILTER (WHERE placed >= DATE '2024-03-08') AS week_2
FROM orders
GROUP BY region
ORDER BY region;

 region | week_1 | week_2
--------+--------+--------
 North  | 200.00 | 200.00
 South  |  45.50 |  30.00

The pivot's columns are fixed in the query, which is the main difference from the spreadsheet where new categories appear as columns automatically. When the set of columns changes, generate the query, use crosstab in PostgreSQL's tablefunc, PIVOT in DuckDB or SQL Server, or return long form (region, week, total) and let the reporting tool pivot (pivot tables versus GROUP BY and CASE).

Running totals and ranks become window functions

A running total in Excel is =SUM($D$2:D2) copied down, with the range anchored at the top. In SQL:

SELECT order_no, placed, amount,
       sum(amount) OVER (ORDER BY placed, order_no) AS running_total,
       rank() OVER (ORDER BY amount DESC)           AS amount_rank
FROM orders
ORDER BY placed, order_no;

 order_no |   placed   | amount | running_total | amount_rank
----------+------------+--------+---------------+-------------
        1 | 2024-03-01 | 120.00 |        120.00 |           2
        2 | 2024-03-03 |  80.00 |        200.00 |           3
        3 | 2024-03-05 |  45.50 |        245.50 |           4
        4 | 2024-03-09 | 200.00 |        445.50 |           1
        5 | 2024-03-12 |  30.00 |        475.50 |           5

The window function states the intent once and cannot be broken by an inserted row or a sort, which are the two ways copied formulas fail. Moving averages use a frame (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), and RANK corresponds to Excel's RANK with the same tie behavior, where tied values share a rank and the next rank is skipped (running totals in Excel versus window functions).

Duplicates, text and dates

Remove Duplicates keeps the first row of each combination. SELECT DISTINCT keeps one copy of each distinct row, which is the same for identical rows and different when you want the first by some order; for that use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1 (removing duplicates in Excel versus SELECT DISTINCT).

Text functions map by name with differences in indexing and edge cases: LEFT, RIGHT and MID become left, right and substr; LEN becomes length; TRIM also collapses internal runs of spaces in Excel, which SQL's trim does not; SEARCH and FIND become strpos or position; CONCATENATE and & become || or concat; TEXT(x, "000") becomes lpad or a formatting function (text functions in Excel versus SQL string functions). Date functions follow the same pattern: DATE, YEAR, MONTH, EOMONTH, NETWORKDAYS and DATEDIF have equivalents, with EOMONTH and the working-day calculation needing more than a function call (date functions in Excel versus SQL date functions).

Blanks, nulls and zeros

This is the quiet source of mismatches. In Excel, a blank cell is treated as zero in arithmetic and as an empty string in text functions. In SQL, NULL propagates: NULL + 5 is NULL, and sum skips nulls while + does not. Translate each place where the workbook relies on blank behavior deliberately:

• Arithmetic on a possibly blank cell: COALESCE(x, 0) + y.

• COUNT(range) counts numbers only; COUNTA counts non-empty cells; COUNTBLANK counts empty ones. In SQL, count(x) counts non-null values, count(*) counts rows.

• AVERAGE(range) ignores blanks, as SQL's avg ignores nulls; if the workbook has zeros in place of blanks, the averages differ.

• Comparisons: =A2="" is true for blank and empty strings; SQL needs x IS NULL OR x = ''.

Test the translation on rows with missing values, because that is where it diverges.

Rounding and precision

Excel's ROUND rounds half away from zero: ROUND(2.5, 0) is 3 and ROUND(-2.5, 0) is -3. PostgreSQL's round on numeric does the same; on double precision it rounds half to even, so round(2.5::float8) is 2. SQL Server and MySQL follow their own rules by type. Excel calculates in floating point with 15 displayed digits, and SQL numeric is exact. A sum of prices that Excel shows as 475.5 may be 475.49999999999994 inside, while numeric gives exactly 475.50. When you compare the two, round both to the cent. Decide on one rounding rule for money (rounding each line to cents before summing, or summing and then rounding) and write it down, because the two give different totals on real data.

Inventory the workbook first

Before writing any SQL, list what the workbook contains. A formula count by shape reveals how much logic there really is. With openpyxl, collapse the row numbers in each formula so identical formulas copied down a column become one pattern:

import re
from collections import Counter
from openpyxl import load_workbook

wb = load_workbook("model.xlsx")
shapes = Counter()
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            if cell.data_type == "f":
                shape = re.sub(r"(?<![A-Za-z])(\$?[A-Z]{1,3})\$?\d+", r"\1#", str(cell.value))
                shapes[(ws.title, cell.column_letter, shape)] += 1

for (sheet, col, shape), n in shapes.most_common(10):
    print(n, sheet, col, shape)

845 Orders F =D#*E#
845 Orders G =IF(F#>=100,"large","small")
845 Orders H =VLOOKUP(B#,Customers!$G:$I,2,FALSE)
1 Summary B =SUM(Orders!F:F)
1 Summary B =SUMIF(Orders!C:C,"North",Orders!F:F)

Three rules are applied 845 times each, and two summary formulas are applied once. That is the whole job: a calculation, a classification and a lookup, plus two aggregates. The regular expression replaces each cell reference with its column letter and a hash, so F2 and F3 look the same; the lookbehind keeps it from touching function names. Run the same census on every sheet and you get a map of the workbook's logic in a page.

Also list the constants that the formulas depend on: tax rates in cells, thresholds in IF conditions, hard-coded numbers inside formulas (=D2*1.2). Each hard-coded number is a parameter that should become a column in a table, a setting or a named constant in the SQL. A workbook with a dozen different literals scattered through its formulas is a workbook whose behavior nobody can state in a sentence.

Names, tables and structured references

Many workbooks use defined names and Excel Tables, and formulas read like =SUM(Orders[Amount]) or =TaxRate*B2. These translate more directly than cell addresses: a table is a SQL table, a column reference is a column, and a defined name is a constant or a one-row settings table (Excel named ranges and tables explained for developers). Structured references with [@Amount] mean "this row's amount", so =[@Qty]*[@Price] is qty * price in a select list. Formulas that use whole-column references (Orders!F:F) include the header and anything below the data; in SQL you have no such ambiguity, so verify that the workbook's range matched the table, because a range that stops at row 500 in a sheet with 845 rows is a bug you are about to inherit or fix.

Cross-sheet references to other workbooks ([Prices.xlsx]Sheet1!B2) are inputs from outside. Treat the other workbook as another data source and load it, or ask what system it is exported from.

Array formulas and the newer functions

Recent versions of Excel have dynamic array functions that return many cells from one formula: FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP and LET. They map to SQL clauses closely, which makes them easy to translate: FILTER(range, condition) is WHERE, SORT is ORDER BY, UNIQUE is DISTINCT, SEQUENCE(n) is generate_series(1, n), and LET is a common table expression or a subquery alias that names an intermediate result. SUMPRODUCT(a, b), a long-standing workhorse for conditional sums over several arrays, is sum(a * b); with conditions inside (SUMPRODUCT((C:C="North")*(D:D))) it is sum(amount) FILTER (WHERE region = 'North').

Older array formulas, entered with Ctrl+Shift+Enter and shown in braces, work on whole ranges and often hide a loop; translate them by asking what the array operation does to each element and what reduces it to a result. Volatile and layout-dependent functions (OFFSET, INDIRECT) build references from text and position; they usually encode a rule about "the last N rows" or "the sheet named in this cell" that is a parameter in SQL (why SQL beats formulas once the data gets large).

Errors and what they mean

Excel's error values (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?) do not exist in SQL. Division by zero raises an error in PostgreSQL and SQL Server and returns NULL in MySQL under some modes. A failed lookup is a NULL from an outer join. A text-to-number failure is an exception or, with a safe-cast function (TRY_CAST, TRY_CONVERT), a NULL. When the workbook wraps a formula in IFERROR(x, 0), decide what the SQL should do: usually COALESCE(x, 0) when the failure is a null, NULLIF(denominator, 0) to avoid dividing by zero, or a safe cast. Be careful with the blanket IFERROR, which hides every error including genuine mistakes. A translation that replicates it faithfully replicates the hiding. Where the workbook's results contain error values, list them: they point at inputs that the original author never looked at.

Organize the SQL

A workbook's logic lives in a chain of dependent cells. Mirror the chain with a chain of views or common table expressions, one per stage: a cleaned input, a calculated column, a lookup, an aggregation. Name each stage for what it holds, not for the cell it replaced. A query that reads orders_clean, then orders_priced, then region_totals is readable by someone who has never seen the workbook, and each stage can be tested on its own with a few rows.

Keep parameters in a table (settings(name, value)) or as query parameters, not as literals inside the SQL. Keep the comparison tests with the SQL: a short script that loads the fixture data, runs the views and asserts the totals from the workbook. When the business changes a rule, change the parameter or the view, rerun the tests, and the test failure tells you which stage moved. The result is a calculation that can be reviewed in a pull request, run on a schedule and explained, which is the reason to translate in the first place.

A plan for a real migration

A workbook worth translating is worth translating in steps. Start by freezing the workbook: copy it, record its output values for a set of known inputs. Then load the inputs, write the SQL for the simplest stage and compare it with the workbook's matching cells, and continue stage by stage. Run both in parallel for a period, comparing outputs each time the process runs, and investigate every difference. Only when they agree for a few cycles, retire the workbook, or demote it to a viewer that reads the SQL results. Communicate with the people who use the workbook before you change anything, since they know which numbers matter and which cells are never touched. Keep the old workbook archived, with its date and the version of the SQL that replaced it.

Prove the translation

A translation is a claim that the SQL gives the same answers as the workbook. Test it:

1. Load the workbook's input data into tables (staging Excel data in a table before cleaning it).

2. Read the workbook's cached results for the output cells with data_only=True (what happens to formulas when code reads an Excel file).

3. Run the SQL and compare, row by row for detail columns and total by total for aggregates, rounded to the cent.

4. Include edge rows: blanks, duplicates, ties, the first and last dates, negative amounts.

5. Keep the comparison as a test that runs when either side changes.

Differences are findings. Sometimes the SQL is wrong; often the workbook is, because of a range that stops one row short, a hard-coded number or a hidden row. Document each difference and decide which side is right with the owner.

Common mistakes in translated SQL

The same few mistakes recur when people translate their first workbook, and knowing them in advance saves a round of debugging.

Joining on a non-unique key. VLOOKUP returns the first match, and a join returns all of them. If the customers table has two rows for one id, every order for that customer doubles, and the totals are wrong without any error. Check that the lookup key is unique before you join, and add a constraint if it should be.

Filtering in the wrong place. A condition in the WHERE clause of a query with a left join removes the unmatched rows and turns the outer join into an inner one. A condition that belongs to the optional side goes in the ON clause. The symptom is a row count lower than the workbook's.

Forgetting nulls in comparisons. WHERE tier <> 'gold' excludes rows where tier is null, because the comparison is neither true nor false. Excel's <> on a blank cell is true. Write tier IS DISTINCT FROM 'gold' in PostgreSQL, or tier <> 'gold' OR tier IS NULL.

Integer division. Dividing two integers in PostgreSQL, SQL Server and several other engines gives an integer: 1 / 2 is 0. Excel gives 0.5. Cast one side to numeric before dividing.

Ordering by an unstable key. A running total ordered by date alone, with two rows on the same date, gives an arbitrary order and a different running balance between runs. Add a tie-breaker such as the order number to the window's ORDER BY, as the example above does.

Mixing aggregates and detail. A formula that shows each row's amount next to the grand total uses an absolute reference. In SQL that is a window aggregate, sum(amount) OVER (), not a GROUP BY, which collapses the rows.

Time zones in dates. A timestamp compared to a date in a WHERE clause includes only midnight on the end date. Use a half-open range, placed >= '2024-03-01' AND placed < '2024-04-01', which behaves the same for dates and timestamps.

Collation, case and wildcards

Text comparison is the other quiet difference. Excel compares text without regard to case, and treats * and ? as wildcards in functions like COUNTIF and SUMIF. SQL compares according to the column's collation: PostgreSQL's default is case-sensitive, MySQL's default collations are case-insensitive, SQL Server's depend on the database. A translated SUMIF(C:C, "north", D:D) can return zero in PostgreSQL where Excel returned 400. Use lower(region) = 'north' or ILIKE, or a case-insensitive collation, and test with values in different cases. Wildcards translate to LIKE: * becomes %, ? becomes _, and a literal ~* in the criterion needs an escape. Leading and trailing spaces matter in SQL comparisons for most engines and are invisible in a sheet, so trim on load.

Sorting differs too. Excel sorts text by its locale's rules; SQL sorts by collation, and ORDER BY name can put uppercase letters before lowercase ones, or ignore accents, depending on the setting. If an order is part of the output, state the collation.

Where the work should run

Translating a formula into SQL does not tell you where it should run. For a one-off analysis, run it in a client against a copy of the data. For a recurring report, put the logic in views or a transformation tool, and let a scheduler refresh the output. For a calculation that feeds an application, run it where the data lives. The advantage of SQL is that the work happens near the data, in an engine built for sets, with indexes and a planner; a workbook pulls the data to the client and recalculates cell by cell. A calculation that takes a minute in Excel over 300,000 rows typically takes a fraction of that in a database, though I have not measured this for a specific case and the difference depends on the formula, the engine and the indexes.

Maintenance matters more than speed. SQL in version control with tests has an owner, a history and a review; a workbook on a shared drive has the last person who saved it. When the logic is important enough to move, it is important enough to give an owner.

A cheat sheet of equivalents

When a formula should not be translated into SQL

The workbook is an interactive model. Scenario analysis, goal seek and what-if tables are the spreadsheet's strength. Keep them there and feed them from the database.

The audience edits the logic. If finance changes a rate or a rule every week, moving it into a query puts an engineer in their way. Parameterize the SQL, or keep the formula.

The calculation is small and used once. A thirty-row model for a decision is not worth a pipeline.

The formulas depend on layout. Cells that read "the cell two columns left of the highlighted one" encode a procedure, not a rule. Understand the procedure before translating.

The result is a presentation. Charts, conditional colors and print layouts belong in the report tool. Translate the numbers, not the look.

The functions are documented in the PostgreSQL manuals for window functions and aggregate expressions with FILTER, and the Excel side in Microsoft's function reference. To check a small extract as JSON, use the CSV to JSON converter.