CSV Calculated Column

A calculated column is a new column worked out from the ones already in your file. Type an expression such as `unit price` * qty, or something with a condition in it, and the column appears immediately. Arithmetic, comparisons, text joining, dates and around forty functions are available. Nothing is uploaded: the expression is evaluated in this browser tab.

Want to write real SQL against the file instead? Query it with DuckDB.

The version of this feature that runs out

Most browser tools that offer a calculated column accept four operators and a pair of brackets. price * qty works. (a + b) / 2 works. Then a real job turns up and it stops.

The real jobs are always the same handful. A discount that only applies above a threshold, which needs a condition. A full name built out of two columns, which needs string joining. An age in years from a date of birth, which needs date arithmetic. A total that has to survive an empty cell, which needs a default. A figure rounded to two decimals before it goes into an invoice, which needs a function. None of those fit into four operators, so the reader gives up and opens a spreadsheet, which is where the leading zeros get eaten.

So the language on this page is a real one. It has precedence, brackets, comparisons, boolean operators, string concatenation, literals, backticked identifiers and about forty functions. It is parsed once into a tree and then evaluated per row, so a hundred thousand rows parse the expression once rather than a hundred thousand times.

Worked example: line totals, then a rule

This is the file behind the example button, orders.csv:

order_id,product,unit price,qty,discount,ordered_on
1001,Keyboard,49.99,3,0,2024-01-15
1002,Monitor,229.00,1,10,2024-02-02
1003,Cable,7.50,12,0,2024-02-19
1004,Docking station,149.00,0,5,2024-03-04
1005,Mouse,24.95,4,,2024-03-21

Note the column called unit price, with a space in it, and the blank discount on the last row. Start with the obvious expression, named line_total:

`unit price` * qty

The new column reads 149.97, 229, 90, 0, 99.8. Two things to notice. 229.00 * 1 comes back as 229, because the result is a number the tool computed rather than text it copied; if you want two decimals, ask for them. And 49.99 * 3 is 149.97, not 149.97000000000003, because results are rendered to fifteen significant digits and the floating point debris is dropped.

Now the rule. Charge the discount, round the money, and fall back to zero when the discount cell is empty:

ROUND(`unit price` * qty * (1 - COALESCE(discount, 0) / 100), 2)

That gives 149.97, 206.1, 90, 0 and 99.8. Without the COALESCE the last row would come back blank, because arithmetic against an empty cell answers blank rather than pretending the missing value was a zero, and the stats strip would say so: 1 blank (a source cell was empty).

One more, to show conditionals and text together:

IF(qty = 0, 'backorder', UPPER(LEFT(product, 3)) & '-' & qty)

KEY-3, MON-1, CAB-12, backorder, MOU-4. The & operator joins text; IF only evaluates the branch it takes, which matters as soon as the other branch would divide by that zero.

The language, in full

Operators, loosest binding first. Everything on one line binds equally and evaluates left to right, except the caret.

OR
AND
NOT
=  ==  <>  !=  <  <=  >  >=
&                      text joining
+  -
*  /  %
^                      right associative: 2^3^2 is 512
-x  +x                 unary sign
(  )  function(a, b)

Unary minus binds tighter than the caret, so -2 ^ 2 is 4 rather than -4. That is what Excel and Google Sheets do, and the people writing these expressions are coming from a spreadsheet rather than from Python. Write 0 - 2 ^ 2 if you want -4.

Values you can write directly:

Numbers: 42, 1.5, .75.
Text in single or double quotes: 'EU', "paid". A doubled quote inside is a literal one, so 'it''s here' is it's here.
TRUE, FALSE and NULL, in any case.
Column names, written exactly as the header spells them. A name with a space, a hyphen or a bracket in it goes in backticks: `unit price`.

The functions:

Conditionals. IF(test, yes, no), IFS(test1, result1, test2, result2, ...), COALESCE(a, b, ...) for the first value that is not blank, ISBLANK(x), and AND, OR, NOT as functions as well as operators.
Numbers. ROUND, ROUNDUP, ROUNDDOWN (each takes an optional digit count), ABS, FLOOR, CEIL, SQRT, POWER, MOD, SIGN, MIN and MAX over any number of arguments, NUMBER to force a text value into a number.
Text. LEN, UPPER, LOWER, PROPER, TRIM, CONCAT, LEFT, RIGHT, MID (one-based, like a spreadsheet), SUBSTITUTE, CONTAINS, STARTSWITH, ENDSWITH, FIND. LEN('café') is 4, because characters are counted rather than UTF-16 code units.
Dates. YEAR, MONTH, DAY, HOUR, MINUTE, TODAY(), NOW(), TEXT(date, pattern), and DATEDIFF in two spellings: DATEDIFF(start, end) for whole days, or DATEDIFF('month', start, end) for day, week, month, year, hour, minute or second.

Blank is not zero, and an error is not a blank

This is the design decision worth arguing about, so here is the argument. A revenue figure of 0 and a revenue figure nobody recorded are different facts. A tool that treats an empty cell as zero will happily report that a region sold nothing, and it will be wrong, and the report will get sent.

So arithmetic touching an empty cell answers blank, and the count of those blanks appears in the stats strip above the table. If a missing value really does mean zero in your file, say so in the expression: COALESCE(discount, 0). It is four more characters and it makes the assumption visible to whoever reads the expression next.

An error is separate again. A cell holding n/a in a column you are multiplying, or a division whose divisor turned out to be zero, is not a missing value: it is a row the expression could not be applied to. Those are counted on their own, and the first few distinct failures are quoted back with the row number they first hit, counting the header as row 1 so the numbers match your spreadsheet. row 4: "abc" is not a number, so the calculation failed is more use than a column of empty cells.

The When a row fails control decides what lands in the cell: nothing, the reason (prefixed with #ERROR so it is greppable), or a zero. Switching between them recomputes at once, so the usual flow is to write the reason into the cells, look at the rows that failed, fix the expression or the source, and switch back.

When the expression itself is wrong

Two kinds of mistake never reach the rows at all, because there is no point running an expression a hundred thousand times to discover it was misspelled.

A syntax error comes back with the expression printed and a caret under the character it stopped on, plus the list of columns in your file:

The expression stops early.

`unit price` * 
               ^

Columns in this file: order_id, product, unit price, qty, discount, ordered_on

A name that is not a column is caught the same way, before evaluation: This name is not a column in the file: prise. followed by the columns that are, and a reminder that a name with a space in it goes in backticks. Nearly every report of "the calculated column is empty" is one of these two, and both of them now say what happened.

One more safety note, because people reasonably ask. There is no eval and no new Function anywhere in this. The expression is tokenized and parsed into a tree, and the evaluator only knows how to do the operations listed above. An expression pasted from somewhere else cannot read the page, the clipboard, your other tabs or the network, because there is no way to express any of those things in the language.

Details that will save you an afternoon

Comparisons switch on the values. If both sides read as numbers they are compared numerically, so qty > 100 does not decide that "9" beats "100" the way a text comparison would. Otherwise they are compared as text.
ROUND rounds half away from zero. ROUND(2.5) is 3 and ROUND(-2.5) is -3, which is what a spreadsheet does. ROUND(1.005, 2) is 1.01, because the decimal point is moved through the number's own text rather than by multiplying by 100, which is the trick that stops binary floating point turning 1.005 into 1.00499999999999989.
TODAY() is fixed for the whole run. Every row of one calculation agrees about what today is, so a file processed at midnight cannot come back with two different dates in it.
Ambiguous dates follow the column. 03/04/2024 is read day-first or month-first depending on what the rest of the column looks like, decided the same way the date normalizer decides it. The Ambiguous dates control pins it when you know better.
There are no aggregates. No SUM over the column, no reference to another row. An expression sees one row at a time. For totals and group-bys, use the pivot table or SQL in the browser.
A name collision is resolved, not ignored. Calling the new column product when there is already a product gives you product_2, so nothing in the source file is overwritten.

Frequently Asked Questions

What can I actually write in the expression box?

Arithmetic with + - * / % and ^, brackets, comparisons, AND, OR and NOT, text joining with &, quoted text, TRUE, FALSE, NULL, and around forty functions including IF, IFS, COALESCE, ROUND, ABS, MIN, MAX, LEN, UPPER, LOWER, CONCAT, LEFT, RIGHT, MID, SUBSTITUTE, CONTAINS, DATEDIFF, YEAR, MONTH, DAY and TODAY. Column names are written as the header spells them.

How do I use a column whose name has a space in it?

Put it in backticks: `unit price` * qty. A bare name can only hold letters, digits, underscores and dots, which is what keeps a b from parsing as one thing when you meant two. The error message for an unknown name reminds you of this and lists the columns the file actually has.

Why is my calculated column blank on some rows?

Because a cell the expression reads is empty, and arithmetic against an empty cell answers blank rather than treating the missing value as zero. A total of zero and a total nobody recorded are different facts. Wrap the column in COALESCE(column, 0) if a blank really does mean zero in your file.

What happens to a row the expression cannot handle?

It is counted as an error, separately from the blanks, and the first few distinct failures are quoted back with the row number they first hit. The When a row fails control decides what goes in the cell: nothing, the reason prefixed with #ERROR, or a zero.

Is dividing by zero a blank or an error?

An error, with the message divided by zero and the row number. Silently blanking it hides a real problem in the data. If you want the safe version, write it explicitly: IF(qty = 0, 0, total / qty). IF only evaluates the branch it takes, so the division never runs on those rows.

Can I sum a column or refer to another row?

No. The expression sees one row at a time, so there are no aggregates and no lookups across rows. That is a deliberate limit rather than an oversight: totals and group-bys belong in the pivot table, and anything that needs a join belongs in SQL in the browser, both of which are on this site.

Is it safe to paste an expression somebody sent me?

Yes. There is no eval and no new Function anywhere in the implementation. The expression is parsed into a tree and the evaluator only knows the operators and functions documented on this page, so there is no way to express reading the page, the clipboard, your other tabs or the network.

Does the file get uploaded?

No. This page has no upload endpoint. The file is read, the expression is parsed, and every row is evaluated by JavaScript in your own tab. Nothing is stored between visits, so reloading the page gives you an empty box again.

One expression, one new column

Free, no account, no upload. Type the formula, watch the column appear, take the CSV.

Back to the calculator