Worked Examples Moving Between Excel and SQL Side by Side

Worked examples of one task in Excel and SQL: ranking, two-key joins, running totals, deduplication, distinct counts and percentiles, with the differences.

17 min read

Summarize in AI

Quick Summary

Most spreadsheet tasks have a direct SQL form, and the places where results differ are ties, blanks, ordering, type coercion and rounding. Work an example in both, compare row counts and totals on the same data, and decide which tool should own the logic once the data or the audience grows.

Key takeaways

Each common spreadsheet task, such as a lookup, a running total or a ranking, has a SQL form, and writing both against the same small data shows exactly where they diverge.
Ties, blanks, text versus numbers and the order of rows are where Excel and SQL quietly give different answers.
Window functions replace the dragged-down formula, and GROUP BY with FILTER replaces the conditional sum.
Comparing row counts, a control total and three hand-checked rows proves a translation, and a mismatch always has a nameable cause.
DuckDB can run SQL directly over a workbook, so you can test the SQL on the spreadsheet's own data before moving anything.
Past a certain size, or when the logic is shared and must be repeatable, the database should own it and Excel should show the result.

People who move between Excel and SQL spend a lot of time translating. A formula that took a minute to write in a sheet has to become a query, or a query's result has to be reproduced in a sheet for someone who does not have database access. The translation is rarely hard, and it is the small differences that cause trouble: a rank that handles ties one way here and another way there, an average that skips blanks in one tool and counts them in the other, a lookup that returns the first match where a join returns all of them. This guide works the common tasks side by side on the same small data, so you can see the pairs, and collects the habits that make a translation trustworthy. The spokes linked throughout take one task each. For the broader mapping of formulas to SQL, translating Excel formulas to SQL is the overview.

One dataset for the whole guide

The examples use a small table of orders. Keep it in mind, and the examples are easy to check by eye.

order_id	customer	region	ordered_on	amount
1	Acme	North	2026-01-05	120.00
2	Bolt	South	2026-01-09	80.00
3	Acme	North	2026-02-11	200.00
4	Cobalt	North	2026-02-14	80.00
5	Bolt	South	2026-03-02	150.00
6	Acme	North	2026-03-20	50.00

In Excel it sits in A1:E7 with headers in row 1, or as a table named Orders. In PostgreSQL it is a table orders with the same columns, ordered_on as date and amount as numeric(10,2). Every example runs against it.

The model behind the differences

Almost every divergence comes from five differences in how the tools think.

Cells versus sets. A spreadsheet formula refers to a cell or a range and is copied down or across; each copy is its own calculation. A SQL query describes a whole result set. The spreadsheet makes order implicit (row 5 is above row 6); a table in SQL has no order unless you ask for one with ORDER BY.

Blanks versus NULL. An empty cell is treated as zero in arithmetic and as an empty string in text. A SQL NULL is unknown, and almost any operation on it yields NULL. SUM ignores NULLs, COUNT(column) skips them and COUNT(*) does not. AVERAGE skips empty cells; AVG skips NULLs; both differ from a table where blank means zero.

Types. Excel coerces freely: the text "12" becomes the number 12 in arithmetic, and a number can match a text key in some functions. SQL is strict about comparing a number to a text value and raises an error or casts, depending on the engine and the setting. The type mismatch is the commonest reason a lookup works in Excel and a join returns nothing (fixing numbers stored as text).

Ties. What does "second highest" mean when two values are tied for first? Excel's LARGE counts duplicates; SQL's dense_rank, rank and row_number each answer differently. The tie decision is a business decision hidden in a function name (finding the second highest value in Excel and in SQL).

Rounding and precision. Excel works in binary floating point with 15 digits shown, and rounds half away from zero. PostgreSQL's numeric is exact decimal, and double precision is floating point like Excel. A total that matches to the cent in one may differ by a tiny amount in the other, and the order of addition can change the last digit.

Keep these five in mind, and most mismatches announce their cause.

Lookups and joins on more than one key

A lookup on one key is XLOOKUP or VLOOKUP, a join in SQL. Two keys is where spreadsheets get awkward. To find a price by product and region, a spreadsheet formula needs a helper column that concatenates the keys, or XLOOKUP over a concatenated array, or SUMIFS as a trick for numeric results:

Text

=XLOOKUP(1, (Prices[product]=A2)*(Prices[region]=B2), Prices[price])

and in SQL:

SQL

SELECT o.*, p.price
FROM orders o
LEFT JOIN prices p ON p.product = o.product AND p.region = o.region;

The differences: the spreadsheet returns the first match and stops, while the join returns every match and multiplies rows when the price table has duplicates. The spreadsheet form shows #N/A for no match; the left join returns NULL. Verify the price table has a unique key before relying on either (joining two sheets on two keys in Excel and in SQL).

Running totals and ranks

A running total in Excel is a formula dragged down with an expanding range:

Text

=SUM($E$2:E2)

In SQL it is a window function:

SQL

SELECT order_id, ordered_on, amount,
       sum(amount) OVER (ORDER BY ordered_on, order_id) AS running_total
FROM orders;

The window version is a single expression with an explicit order; the spreadsheet version depends on the rows being sorted the way you see them. The tiebreaker order_id matters when two orders share a date: without it, the running total's order within the date is not defined. For a total that restarts per month, PARTITION BY date_trunc('month', ordered_on) does what a new range anchor does in the sheet (running total of monthly sales both ways in Excel and SQL).

Ranks follow the same split. RANK in Excel gives tied values the same rank and skips the next numbers (1, 2, 2, 4); SQL rank() does the same, dense_rank() does not skip (1, 2, 2, 3) and row_number() never ties. Choose by the question: "top three customers" with a tie for third may need to return four rows (rank customers by spend in Excel and SQL with ties handled).

Deduplication and distinct counts

Removing duplicates in Excel is a command (Data, Remove Duplicates) that keeps the first row it meets, in sheet order, and cannot tell you why. In SQL you choose the row to keep:

SQL

SELECT DISTINCT ON (customer) *
FROM orders
ORDER BY customer, ordered_on DESC, order_id DESC;

This keeps the latest order per customer, in PostgreSQL's DISTINCT ON form; a portable version uses row_number() OVER (PARTITION BY customer ORDER BY ordered_on DESC) = 1. The spreadsheet equivalent for "latest per key" is a sort, then Remove Duplicates on the key, then relying on the sort having been right (remove duplicates keeping the latest row both ways).

Distinct counts: =COUNTA(UNIQUE(range)) against count(DISTINCT customer). Per month, the spreadsheet needs a UNIQUE over a FILTER; SQL puts count(DISTINCT customer) in a GROUP BY (count distinct customers per month both ways in Excel). Blank handling matters: COUNTA(UNIQUE(...)) counts a blank as a value if it appears, and count(DISTINCT x) ignores NULLs.

Text, lookups with ranges and percentiles

Text work is where syntax differs most and meaning least. Joining values with a separator is TEXTJOIN(", ", TRUE, ...) in Excel and string_agg(customer, ', ') in SQL; the SQL needs an ORDER BY inside the aggregate if you care about order, and DISTINCT if you want each once (concatenate text with a separator both ways in Excel). Splitting is TEXTSPLIT against string_to_array or split_part (splitting text by delimiter in Excel and SQL queries).

A lookup that finds the band a value falls into, such as a tax rate by income, is an approximate match in Excel (XLOOKUP with match mode -1) and a range join in SQL (ON o.amount >= b.low AND o.amount < b.high). The sheet silently returns the nearest lower band even if the table has a gap; the join returns nothing for the gap (lookup with approximate match versus SQL range join).

Percentiles are the sharpest case. Excel has PERCENTILE.INC, PERCENTILE.EXC, QUARTILE and MEDIAN, which use interpolation rules; PostgreSQL has percentile_cont (interpolated) and percentile_disc (an actual value). Two tools can be right and disagree at the third decimal because they define the percentile differently (percentile calculations compared across Excel and SQL).

Proving a translation

A translation is only done when you have shown that the two answers agree on the same data, or can say exactly why they do not. A short procedure works for any task.

Use a small, fixed dataset that includes the awkward cases: a tie, a blank, a duplicate key, a month boundary, a text that looks like a number.
Compute the answer by hand for at least three rows.
Run both versions and compare the row counts first, then a control total (the sum of one numeric column), then the three hand-checked rows.
Name every difference. If the sheet shows 5 rows and the query 6, find which row and why: a tie, a NULL, a duplicate.
Scale up to the real data and repeat the counts and the total.
Record the decision on each divergence: which behavior is the intended one, written next to the code.

You can run the SQL over the sheet's own data before any database is involved. DuckDB reads a workbook range directly:

SQL

INSTALL excel; LOAD excel;
SELECT customer, sum(amount) AS total
FROM read_xlsx('orders.xlsx', sheet = 'Orders')
GROUP BY customer
ORDER BY total DESC;

That lets you iterate on the SQL against the spreadsheet's numbers and compare with the sheet's own totals, then repeat the same query on the database copy (querying an Excel file with SQL using DuckDB).

Which tool should own the logic

A spreadsheet owns logic well when the data is small, the audience is the person who built it and the result is looked at, not shipped. SQL owns logic well when the data is large, many reports share a definition, the result must be reproduced exactly next quarter or the logic needs review and tests. The hand-off is gradual: a formula that people trust becomes a query with a test beside it, the sheet reads the query's output and the formula goes away (when SQL beats formulas once the data grows lists the signs, and replacing a fragile Excel model with a query shows a migration).

Even after the move, the spreadsheet stays valuable as the place to explore and present. The aim is a single definition of each number, in the place where it can be tested.

Habits that keep both sides in agreement

Some practices prevent the slow drift that makes the sheet and the database disagree.

Write the definition once, in words, next to both implementations: "Active customer: at least one paid order in the last 90 days." When the two disagree, the sentence decides which is wrong. Keep the fixture data and the expected answers in the repository so that a change to either side is checked. Prefer exact decimal types in SQL and rounding at the end in both; floating point noise looks like a bug. Use ORDER BY with a tiebreaker everywhere in SQL, and sort explicitly before relying on position in the sheet. Check types at the boundary, when data enters the database from a workbook and when it leaves (validating row counts after an Excel import).

A full example: the monthly customer summary, both ways

Pick a task that uses several of the pieces at once. The sales lead wants, for each customer, the number of orders, total spend, the date of the latest order and the customer's rank by spend, for the first quarter of 2026.

In Excel, the usual route is a pivot table for the counts and totals, then formulas beside it. The customer list comes from =SORT(UNIQUE(Orders[customer])). Orders are =COUNTIFS(Orders[customer], F2, Orders[ordered_on], ">="&Start, Orders[ordered_on], "<"&End). Spend is the same with SUMIFS. The latest order is =MAXIFS(Orders[ordered_on], Orders[customer], F2, Orders[ordered_on], ">="&Start, Orders[ordered_on], "<"&End). The rank is =RANK.EQ(H2, H$2:H$4, 0). Five formulas, each with its own copy of the date conditions, and the quarter bounds sit in two named cells so that the next analyst changes only those.

In SQL the whole thing is one statement:

SQL

SELECT customer,
       count(*)                      AS orders,
       sum(amount)                   AS spend,
       max(ordered_on)               AS latest_order,
       rank() OVER (ORDER BY sum(amount) DESC) AS spend_rank
FROM orders
WHERE ordered_on >= DATE '2026-01-01' AND ordered_on < DATE '2026-04-01'
GROUP BY customer
ORDER BY spend_rank, customer;

On the sample data, Acme has three orders totalling 370.00, Bolt two totalling 230.00 and Cobalt one at 80.00. Ranks are 1, 2 and 3, and the latest order dates are 2026-03-20, 2026-03-02 and 2026-02-14. Both tools give the same answer, and the comparison is worth making anyway. Count the rows: three customers each. Total the spend: 680.00. Check Acme's latest date by eye.

Now introduce an awkward case. Add a customer, Delta, with an order of 80.00 on 2026-03-25. Cobalt and Delta now tie at 80.00 for third. RANK.EQ gives both rank 3, and the SQL rank() does too, so the two still agree. Change the SQL to row_number() and they diverge: one of the two now ranks fourth, decided by the ordering you wrote or by chance. That is the kind of divergence a translation test is for. Decide which is wanted ("ties share a rank") and write it beside the query.

Last, the quarter boundary. Add an order on 2026-03-31 at 23:30 stored as a timestamp. In Excel a date-only comparison with "<"&End where End is April 1 includes it; a sheet where the filter used <=DATE(2026,3,31) would drop it because the cell holds 46112.98 and the comparison value is 46112.0. The same trap exists in SQL if the column is a timestamp and the filter says <= '2026-03-31'. The half-open range, used in both, is the habit that makes the two tools agree.

Blanks and NULL in more detail

The blank versus NULL gap deserves its own paragraph, because it changes totals and counts without any error.

In Excel, a truly empty cell is ignored by SUM, AVERAGE, COUNT, MIN and MAX, and counted by neither COUNT nor COUNTA. A cell containing an empty string (from a formula returning "") is counted by COUNTA and ignored by SUM, but =A1+1 on it raises #VALUE! because the empty string is text. A cell with 0 is a value like any other and is included in AVERAGE.

In SQL, NULL is skipped by SUM, AVG, MIN, MAX and count(column), and counted by count(*). An empty string is a value, not NULL, and is counted by everything. Comparisons with NULL produce unknown, so WHERE x <> 5 drops rows where x is NULL, which surprises people who expected "not 5" to include missing values. Use IS DISTINCT FROM when you want NULL handled as a value.

When data crosses from a workbook into a database, decide the mapping once: empty cell to NULL, never to an empty string or zero, unless the column's meaning says otherwise. When it crosses back, NULL becomes an empty cell. A pipeline that maps blanks inconsistently produces averages that depend on how the file was saved.

Averages show the effect most. Three orders of 100, 200 and a blank give an Excel AVERAGE of 150 (the blank is ignored), a SQL avg of 150 (the NULL is ignored) and a "blank means zero" average of 100. All three are defensible. Only one matches the business question.

Dates, times and time zones

Dates are numbers in Excel and typed values in SQL. In Excel, adding 30 to a date gives a date 30 days later; in PostgreSQL, date + 30 works for a date, and timestamp + interval '30 days' for a timestamp. Month arithmetic differs: EDATE clamps to the end of the month, PostgreSQL's date + interval '1 month' does the same (January 31 plus one month is February 28), so the two agree there, though other engines vary.

Excel dates carry no time zone. Database timestamptz values are instants, displayed in the session's zone. A workbook that exports a timestamptz as text in one zone and reads it back as a naive date shifts evenings into the next day. Set the zone in the query (AT TIME ZONE), state it in the header, and test the boundary dates (date formulas for month ends and business days in Excel and designing a report query before touching Excel cover both sides).

Precision and rounding

Excel keeps 15 significant digits and shows what the format says. PostgreSQL numeric keeps exactly what you gave it, and round(numeric, 2) rounds half away from zero. Excel's ROUND does the same. Python's round does not (it rounds half to even), which matters if a script sits between the two. When a total differs by a cent between the sheet and the query, check three things in order: the types (float versus numeric), the point where rounding happens (per line or on the total) and the number of rows (a duplicate or a missing one moves the total far more than rounding does) (rounding and formatting numbers for finance readers).

Common translation errors and their symptoms

A short list of what goes wrong, and how it shows up:

A lookup translated to a join multiplies rows when the lookup table has duplicates, so totals grow. Compare row counts before and after the join.

A conditional sum translated without the date range or with a closed upper bound drops or double counts boundary days. Compare monthly totals across the boundary.

A COUNTIF on text with wildcards translated to equality returns fewer rows. Look for * and ? in the criteria.

A COUNTIF(range, ">5") on a column that holds numbers stored as text returns zero in Excel and the right count in SQL after a cast. The sheet is wrong, not the query.

A text comparison that is case-insensitive in Excel is case-sensitive in PostgreSQL's =. Use lower() on both sides or ILIKE.

A formula that skips hidden rows (SUBTOTAL, AGGREGATE) has no SQL equivalent, because SQL has no hidden rows. The sheet's answer depends on a filter state that the query cannot see.

Each of these is found quickly if you compare counts and a control total before reading any detail.

Teaching the translation to a team

If your team is stronger in Excel than SQL, the quickest way to build fluency is to translate their own workbooks. Pick one workbook per week. Have the owner explain what each formula answers, write that as a sentence, then write the query together. Run both and compare. The owner learns SQL through a problem they already understand, and the team ends up with a library of tested queries for their real reports. Keep the pairs together in the repository: the question, the formula, the query and the result on the sample data. That collection becomes both the training material and the regression test for later changes.

Avoid translating everything. A formula that adds a margin column to a table of twenty rows has no need to become SQL. Translate the calculations that other people depend on, that run on a schedule or that have grown past what a sheet handles comfortably.

Checking at scale

A translation that passes on six rows can still fail on six hundred thousand, so test at the size you will run. Load the real data into a scratch table, run the query, and compare its totals with figures from the sheet or the source system. Look at the extremes: the customer with the most orders, the month with the fewest, the single biggest amount. Extremes expose type problems (a text amount that sorts as text), time zone problems (a spike at midnight) and duplicates (a count that is exactly double).

Time the query too. A statement that returns in 40 milliseconds on the sample may need an index at full size, and a plan that scans the whole table on every run is a cost you want to see before the report is scheduled. Compare the plan with what you expected, and add the index or the pre-aggregation that the plan suggests. Spreadsheets hide this cost by recalculating in the background, and databases show it by making you wait.

Finally, make the comparison repeatable. A small script that runs both implementations, one reading the workbook and one querying the database, and prints the differences, can run before each release of the report. The first time it flags a surprise, it pays for itself.

Keep one more habit in mind: write down the question before the formula. Translations go wrong most often when the person doing them cannot say, in a sentence, what the number is supposed to mean, because then there is nothing to test the two versions against. A one-line statement such as "customers with at least one paid order in the quarter, ranked by spend, ties sharing a rank" settles most of the disagreements before they start.

When the two tools should not give the same answer

There are cases where agreeing is the wrong goal. The spreadsheet's rounding to what is displayed is sometimes what the reader wants, while SQL should carry full precision. A lookup that returns the first match may be exactly the business rule ("take the first rate on file") or a bug waiting for a duplicate. A formula built for a one-off need not match a query built to be repeated. The task is not to make them identical but to know where they differ and to choose, on purpose, which behavior is correct for the question. When the choice matters, write it down.