How to calculate in added columns

When creating a chart, you have the option to add a column in step 2: Check & Describe:

Added columns are perfect to perform calculations on your uploaded columns. For example, you can sum up numbers, create conditions, or trim text. In this example, we multiply the data from two columns with each other and name this new column "GDP":

This article explains which expressions are allowed. They will remind you of Excel, LibreOffice or Google Sheets formulae – and you're right, we took some inspiration. You can also always prepare the final data before uploading it to Datawrapper. 

At the end of the article, you find a few more examples to give you a better understanding of what's possible. 


You can use the following values in your formulae:

  • numbers, e.g. 3 or 3.14
  • text, e.g. "quick brown fox"
  • column names, e.g. gpd_per_capita
Then you can use various expressions to process these values.
👉 The following tables show just a selection of the possible expressions. You can find an always up-to-date, complete list in this Github README.

Expressions for numbers

Expression Explanation Example
+ - * / % ^ simple math operators (use parentheses for grouping) value ^ 2 or (value + 3) * 5
ABS absolute number ABS(value) or ABS value
SQRT square root SQRT(value) or SQRT value
SIN, COS, TAN, ASIN, ACOS, ATAN trigonometric functions COS(value)
LOG, LOG2, LN, LG, LOG10, LOG1P logarithmic functions LOG10(value)
MIN, MAX minimum / maximum MAX(value, foo, 42)
SUM sum SUM(value, foo, 42)
MEAN, MEDIAN returns the median or mean of the given numbers
MEAN(y, x)
ATAN2 calculate angle ( Wikipedia) ATAN2(y, x)
SIGN sign of number; returns 1 if number is positive, -1 if number is negative SIGN(value)
ROUND round number to decimal ROUND(value) or ROUND(value, decimals)
FLOOR, CEIL, TRUNC other ways of rounding FLOOR(value)
PI, E constants PI * value
NUMBER convert text to number NUMBER(text) or NUMBER text
RANDOM generate a random real number between 0 and 1 when used without arguments, or between 0 and the passed number RANDOM() or RANDOM(10)

Expressions for text

Expression Explanation Example
LOWER lowercase a string LOWER("Hello World") > hello world
UPPER uppercase a string UPPER("Hello World") > HELLO WORLD
TITLE convert a string to title-case. Like  PROPER, but better for headlines.
TITLE("IDA lloyd-webber") > Ida Lloyd-webber
PROPER convert a string to title-case. Like  TITLE, but better for names. PROPER("IDA lloyd-webber") > Ida Lloyd-Webber
LENGTH length of a text LENGTH name or LENGTH(name)
TRIM remove whitespace from the beginning and end of text TRIM(name) or TRIM name
SUBSTR extract substring from text SUBSTR(name, start, length)
CONCAT concatenate multiple texts CONCAT(firstname, " ", lastname)
SPLIT split text into parts by a substring
SPLIT(name, " ")
JOIN join parts back into text with a glue string (e.g. a comma). You can define a second glue for the last join (e.g. "and").
JOIN(parts, ",")
REPLACE replaces all occurrences of substring in text REPLACE(country, "United States", "USA")
REPLACE_REGEX like REPLACE, but interprets the search string as regular expression
REPLACE_REGEX("hello 123 world", "[0-9]", '#')
TEXT convert number to text TEXT(value) or TEXT value

Expressions for dates

Expression Explanation Example
DATE get a current date; or create date from passed year, month and day values (the month parameter is interpreted as 1 = January, 2 = February...); or create date from date as text
DATE() or DATE(2020, 5, 1) or DATE(text)
YEAR extract year from date YEAR(date)
MONTH extract month from date (1 = January, 2 = February...) MONTH(date)
DAY extract day of the month from date DAY(date)
HOURS extract hours from datetime HOURS(date)
MINUTES extract minutes from datetime MINUTES(date)
SECONDS extract seconds from datetime SECONDS(date)
WEEKDAY day of the week (0 = Sunday, 1 = Monday...) WEEKDAY(date)
DATEDIFF calculate number of days between two dates DATEDIFF(first_date, second_date)
TIMEDIFF calculate number of seconds between two dates TIMEDIFF(first_date, second_date)

Logical expressions

Expression Explanation Example
IF if-else statement IF(condition, yay, nay) or condition ? yay : nay
NOT, ! invert expression NOT(condition) or NOT condition or !condition
and logical and condition1 and condition2
or logical or condition1 or condition2
>, <, ==, <=, >=, != comparisons
in check if a substring is included in text; or check if a value is included in parts created by SPLIT "John" in name or "John" in parts
ROWNUMBER current rownumber, starting at 0 ROWNUMBER → 0, 1, 2, ...


Each number column comes with aggregation values written like this: columnname_aggregation. For example, to get a column with the minimum value of your column "percent_change", insert "percent_change__min" into the formula field: 

You can also calculate with these values. Here's an overview of all available aggregations: 

Aggregation Explanation Example
__min returns the minimum value in the column gdp__min
__max returns the maximum value in the column
__sum returns the sum of all values in the column gdp__sum
__mean returns the mean (= average) of all values in the column gdp__mean
__median returns the median of all values in the column gdp__median

This can be helpful for conditions:
IF(salary > salary__mean, "earns more than the average", "earns less than the average")

This gives you a column that shows whose salaries are greater or smaller than the average one.
__min and __max also work for dates. This is helpful, e.g., to find out how many days have passed since the date in your data that's furthest in the past: 
DATEDIFF(date__min, date)<br>

Let's look at some more examples of how to use these expressions: 

Example 1: "pos./neg." with IF

Let's say you have a table with percentage changes, like -5% or +100%. It's sufficient for your reader to know if the change is positive or negative, so you want to print a column that says "pos." or "neg." depending on the change. To do so, add a new column and use the following formula:

IF(percent_change > 0, "pos.", "neg.")

Example 2: Line breaks with JOIN/SPLIT

Let's say you uploaded a column with country names. For your tooltips, you want more line breaks in your long country names. Add a new column and insert the following formula:

JOIN(SPLIT(country, " "), "<br/>")

You could also use REPLACE() – sometimes there are multiple ways to achieve something.

Example 3: First names with SPLIT

SPLIT is also a good way to just get the first (second, third, etc.) words when you have multiple ones. Let's say you upload a list of the first and last names of The Beatles, but you just want their first names. Try the following formula: 

(SPLIT(the_beatles, " "))[0]

This will take the first word (that's a position zero) of your split words:

Example 4: Jitter with RANDOM()

Let's say you upload a dataset with chocolate rated with grades from 1-10. You want to display it on a scatterplot, but all the chocolates would then lay exactly on top of each other. (We wrote about this problem in this blog post.) It's time to introduce some jitter. To do so, use the following formula:

rating + (RANDOM() / 4) - 0.125

This divides a random number between 0 and 1 (e.g. 0.4) by 4 (→ 0.1) and then subtracts 0.125 (→ -0.025):