CSV Column Calculator
Type a formula using column names in square brackets and see the new column straight away. Use arithmetic, comparisons, if, round, text joining and simple date functions.
Drag a file here or choose one
Column names go in square brackets, for example [Unit price]. Use [#3] for the third column. Operators: + - * / ^ and & to join text, with = <> < > <= >= to compare, and and, or, not. Functions: if(test, yes, no), round(x, places), floor, ceil, abs, sqrt, pow, mod, min, max, sum, avg, len, upper, lower, trim, left, right, concat, fixed(x, places), contains, isblank, year, month, day and days(end, start). As in a spreadsheet, -2^2 is 4.
Worked examples
Line total
Item,Price,Quantity Tea,2.50,12 Coffee,3.10,8
Item,Price,Quantity,Total Tea,2.50,12,30 Coffee,3.10,8,24.8
Formula [Price] * [Quantity]. 2.50 times 12 is 30 and 3.10 times 8 is 24.8.
A label with if
Name,Score Ann,42 Bob,37
Name,Score,Result Ann,42,Pass Bob,37,Fail
Formula if([Score] >= 40, "Pass", "Fail").
About CSV Column Calculator
Most calculations on a table are one line: a total from a price and a quantity, a price with VAT added, a full name from two columns, a flag for orders over a limit. Type the formula once, with column names in square brackets, and it is worked out for every row. There is no need to copy it down, and the result appears as you type.
You can use the usual arithmetic with brackets, comparisons and and, or and not, joining text with an ampersand, and functions such as if, round, floor, ceiling, abs, sqrt, min, max, sum, avg, upper, lower, trim, left, right, len, contains and the date functions year, month, day and days. Text that looks like a number, including amounts with currency signs and thousands commas, is read as a number, and a blank cell counts as zero in arithmetic. A row that cannot be worked out, such as dividing by zero, is reported for that row only.
How to use CSV Column Calculator
- Paste your CSV or open a file.
- Type a formula using column names in square brackets, for example [Price] * [Quantity].
- Name the new column, choose where it goes and how many decimal places to round to.
- Download the table with the new column.
Common uses
- Working out line totals from price and quantity
- Adding VAT to a net price
- Joining first and last names into one column
- Labelling rows as pass or fail, or big or small, with if
Frequently asked questions
How do I refer to a column?
Put its name in square brackets, such as [Unit price]. The match ignores capital letters. You can also use the position, as in [#3] for the third column.
How does rounding work?
Like a spreadsheet: halves round away from zero, so round(2.5) is 3 and round(1.005, 2) is 1.01. A negative number of places rounds to tens or hundreds.
What is -2^2?
As in a spreadsheet, the minus sign applies first, so -2^2 is 4. Powers are worked out from left to right, so 2^3^2 is 64. Use brackets to be sure of your meaning.
How do I join text?
With an ampersand: [First name] & " " & [Last name]. The function concat does the same for a list of values.
What happens if a row has bad data?
That row is left blank, or marked #ERROR if you choose, and the first fault is described with its row number. The rest of the column is still worked out.