Notion's integration of relational databases and hierarchical pages makes it a uniquely powerful tool for organization, productivity and collaboration. The Formula property within those databases bolsters Notion's power even further.
Formulas can be infinitely complex or as simple as 2 + 2 (literally). With a brief introduction and some practice, Notion users at every level can use formulas to bolster their workspaces.
If you're new to formulas, this guide provides an introduction to the Formula property using simple, understandable terms and practical examples. Read it in full for a fundamental understanding, or visit Simple, Useful Formula Examples, where you can memorize and copy common functions.
If you're familiar with formulas, you might jump around this resource to fill any gaps in your knowledge, particularly the section with Differences from Excel and Google Sheets. You'll certainly want to reference The Notion Formula Cheat Sheet, where you'll find technical details for, and examples of, every Notion function, operator and constant.
As you explore Notion formulas, I welcome your questions @WilliamNutt, or feel free to respond to the newsletter anytime.
Like Text, Number, Date and other basic properties, Formula properties contain a value for each item in a database. While you manually input that value for most properties, Formula properties automatically generate their values by transforming other properties in useful ways. To define those transformations, you write a formula.
For example, say you have a database of players on a basketball team that includes a Birthday (Date) property. With a formula, you can subtract the birthday from today's date to determine each player's age:
The utility of Notion's Formula property is virtually endless; you'll start with simple, common applications and gradually expand the way you use it.
At the highest level, you'll use Formula properties to achieve one of two outcomes:
Reformatting is particularly useful for displaying properties in your Galleries. For example, you can append context to a date property, which would otherwise stand alone ambiguously. In the example below, the age would would have no meaning without "Age."
When you create a Formula property, you can click any cell within that column to Type a formula. For every formula, you'll define one or more of these elements:
add, subtract, format and join."Age: " and 26.if the value of a Progress property is greater than 50%.For each item in your database, the formula will return a value.
Your formulas will often include multiple actions, input values and conditions. Here's a breakdown of each:
In your formulas, you can define the actions to perform on your inputs using operators and functions.
Operators are characters you place between input values. They fall into three categories, two of which define actions (the third is for comparisons):
You're likely familiar with + for adding and - for subtracting. These are arithmetic operators. We also have * for multiplying and / for dividing, among a few others. You'll find them all in The Notion Formula Cheat Sheet.
As we saw above, the + character between two numbers returns their sum. However, when you place the same character between two strings of text, it concatenates that text, or merges them. Therefore:
2 + 2 → 4
"Age: " + "26" → "Age: 26"
Because the "26" is surrounded with quotes, it is technically a text string, not a number. You'll learn more about value types shortly.
Functions offer predefined actions to perform on their inputs, also known as their arguments. They are formatted as a keyword followed by parentheses, such as format(). Within those parentheses, you insert your arguments, separated by commas, such as add( 2, 2 ).
Most formulas accept a specific number of arguments, some of which are optional. For example, the contains() formula tests whether one text string (the first argument) contains another text string (the second argument):
contains( "North Carolina", "Carolina" ) → true.
In addition to the the quantity of arguments, formulas also require the type of arguments, such as numbers versus text strings. We've dedicated a section to this important concept.
The Notion Formula Cheat Sheet offers a comprehensive list of formulas with a description, argument list and example for each. You may notice that there is a formula for each operator. Above, we saw add( 2, 2 ), which is the same as 2 + 2.
As you've learned, the actions taken by operators and functions are performed on input values. In the case of functions, those input values are known as arguments. You can supply those input values as property references, literal values or constants.
Most often, you'll use the values of other properties as your inputs. In other words, you'll reference those properties.
To do so, you'll use the prop() function. For its argument, you'll include the name of the property within quotation marks. For our Total Cost calculation, we referenced the Price and Quantity properties: prop( "Price" ) and prop( "Quantity" ).
Remember, there is an iteration of each formula for each item in the database. When an iteration references another property, it uses the instance of that property for the same database item. In the example above, each iteration of the formula multiplies the price and quantity for the product on the same row.
In some cases, you'll enter a fixed value for an input rather than referencing another property. Unlike property references, this value will be the same for all items in the database.
As we did with age, we can add a contextual term to the School property of our basketball players. The appended text, "School: ", is a literal value that's unchanged for all players. Meanwhile, the school itself is a reference to the School property, which differs from player to player:
"School: " + prop( "School" )
Then, we can display the contextualized version (the School → Labeled property) on our Gallery cards:
Notion also includes easy references to mathematical constants, including pi and e. You'll likely use these infrequently, if ever.
In many of your formulas, you'll want to return values based on whether certain conditions are met. In this simple example, the formula returns an emoji based on the value of the Mood property:
To create a condition, you'll use the if() function, which accepts three arguments:
true or false, typically using comparison operators (see below) or comparison functions (see the Cheat Sheet).true.false.Like the arithmetic operators we explored previously (+, -, *, etc.), comparison operators are characters placed between values. Unlike arithmetic operators, comparison operators can only return true or false; they compare inputs. For example, the == operator tests whether the values are equal: 2 == 2 → true.
Here are the six comparison operators you'll use in Notion, which are also available in the The Notion Formula Cheat Sheet:
| Operator | Description | Example |
|---|---|---|
== | Equal | 2 == 2 → true |
!= | Unequal | 2 != 2 → false |
> | Greater Than | 2 > 2 → false |
< | Less Than | 2 < 3 → true |
>= | Greater Than or Equal | 2 <= 2 → true |
<= | Less Than or Equal | 2 >= 3 → false |
Revisiting our Moods example, you can see how we constructed the formula:
if( prop( "Mood" ) == "Happy", "?", "?" )
In other words, "If the Mood property is "Happy," return "?"; otherwise, return "?."
For each item in your database, a Formula property evaluates its contents (the conditions, actions and input values) to return its value.
As you work with formulas, you'll want to remain mindful of value types. In the above examples, you've seen number, text, date and true/false values.
Functions and operators require particular value types. For example, you cannot add 2 (a number) to "2" (a text string, as indicated by the quotation marks). The formula 2 + "2" will throw a Type mismatch error.
Similarly, you cannot concatenate (merge) a number with a text string. "Age: " + 26 will also throw a Type mismatch error.
To avoid these errors, Notion offers functions for converting one type two another.
To convert a text string to a number, we use the toNumber() function:
toNumber( "2" ) → 2.
Therefore: 2 + toNumber( "2" ) → 4
The format() function converts a number to a text string:
format( 26 ) → "26"
That's how we were able to add the contextual term to our Age property in our basketball team gallery:
"Age: " + format( 26 ) → "Age: 26"
However, the age was actually a property reference:
"Age: " + format( prop( "Age" ) ) → "Age: 26"
A number is — surprise! — a numeric value. It can be an integer or a floating point number (containing decimals), and either positive or negative.
Values within Number properties are of the number type. Therefore, when you reference those properties as formula inputs, they are of the number type. You can convert them to strings using the format() formula.
When a table cell displays a number, the number is right-aligned. You can format it in various ways, including percents and currencies, by hovering your cursor over the value and clicking the 123 button.
A string is one or more characters that represent text. Those characters may all be numeric, which can cause some confusion. For example, you can type 12345 in both a Text property (the string value type) and a Number property (the number value type). When you reference those properties in your formulas, the value type will match the property type.
For this reason, it's important to use true Number properties for numbers. Entering numeric characters within a Text property will prohibit you from using them in calculations. It will also disrupt your sorting.
When typing strings in formulas, you'll surround them in double quotes: "26" is a string, while 26 is a number.
Additionally, strings are left-aligned in table cells. If you see left-aligned numeric characters, you know they form a string rather than the true number type.
A value of the boolean type is either true or false. As we saw with the if() formula, you can produce a boolean value using comparison operators.
In Notion, boolean values render checkboxes in your databases. Therefore, if your formula returns a boolean value, the field will display a checkbox — checked if true; unchecked if false.
In Notion, a date is a value created with a Date property, a Created Time property, or a Last Edited Time property — all of which you can reference within functions. The now() function also serves as a date. It always reflects the current time.
Within your formulas, you'll often use functions, operators and input values within functions. This is known as nesting.
When you combine inputs, operators and functions in a way that returns a single value, they form an expression. An expression can be as simple as 2 or as complex as an elaborate combination of nested formulas — so long as it evaluates to a single value.
Therefore, a function's arguments can be more than a simple property reference or literal value; any expression can serve as a function's argument so long as it returns the correct value type.
Take the simple formula add( 100, 20 ). Because arguments can be expressions, that simple formula could become add( 100, multiply( 100, .2 ) ), where the second argument, 20, is the output of a the multiply() function.
Taking it one step further, the input values could be references to other properties:
add( prop( "Cost of Goods" ), multiply( prop( "Cost of Goods" ), prop( "Markup" ) ) )What we've done is calculate the Selling Price of a product at a variable markup:
if()One of the most common uses of nesting is with the if() function. Remember, if() tests a boolean expression (the first argument). If it's true, it evaluates the second argument; otherwise, it evaluates the third argument:
if( 2 == 1, "Equal", "Unequal" ) → "Unequal"
Most often, you'll want your first argument to test another property. Here, we return true or false (represented by a checkbox), based on the Status of each task:
if( prop( "Status" ) == "Complete", true, false )
You can use the and() and or() functions to test two expressions. Below, we mark each order as "Complete" if it's both paid and shipped; otherwise it's "In Progress."
if( and( prop( "Paid" ), prop( "Shipped" ) ), "Complete", "In Progress" )
At times, you'll want more than just a true option and a false option. We can add a third emoji to our moods database, for example:
if(prop("Mood") == "Happy", "?", if( prop( "Mood" == "Okay", "?", "?" ) )In other words:
if Mood is "Happy," display ?;
otherwise, if Mood is "Okay," display ?;
otherwise, display ?.
If you've used formulas in Excel or Google Sheets, you'll want to be aware of a few ways Notion differs.
In Excel and Google Sheets, you add formulas at the cell level. Because Notion's tables are true databases, you add formulas at the property (column) level, and they apply to each item in the database. If you've used ARRAYFORMULA() in Google Sheets to fill a formula down a column, that is much how Notion works.
You reference individual cells in Excel and Google Sheets; in Notion, you reference other properties. For each item in the database, its instance of your formula will reference the properties for that item.
To reference those properties, you'll use the prop() function, with the name of the property applied as the argument in double quotes: prop( "Property Name" )
You're used to using & to join expressions in Excel and Google Sheets. In Notion, you'll use +.
In Simple, Useful Formula Examples, you'll find many of the above examples, plus a handful of others, to reference as you dive deeper into the Formula property. Also be sure to keep The Notion Formula Cheat Sheet handy, and never hesitate to tweet me with questions.