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.
What You’ll Learn
- What’s a Formula?
- Why It’s Useful
- The Ingredients of a Formula
- The Importance of Value Types
- Nested Inputs, Operators and Functions
- Differences from Excel and Google Sheets
- Practical Examples of Notion Formulas
What’s a Formula?
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:
Why It’s Useful
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:
- Calculate a new value from other properties.
In a list of products, for example, you can calculate Total Cost using Price and Quantity:
- Reformat another property.
Formulas are often used to change the way values are displayed. You can format dates and times to suit your preferences, and you can combine properties, such as first names and last names:
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.”
The Ingredients of a Formula
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:
- Do what? The actions to perform, such as
- To what? The input values on which to take those actions, such as
- When? Any conditions required to take each action, such as
ifthe value of a Progress property is greater than 50%.
- The action to take if the condition is unmet.
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:
Do what? The Actions
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 →
"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" ) →
In addition to the the quantity of arguments, formulas also require the type of arguments, such as
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.
To what? The Input Values
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
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:
- An expression (defined under Nesting) that evaluates to
false, typically using comparison operators (see below) or comparison functions (see the Cheat Sheet).
- A value to return if the condition is
- A value to return if the condition is
Like the arithmetic operators we explored previously (
*, etc.), comparison operators are characters placed between values. Unlike arithmetic operators, comparison operators can only return
false; they compare inputs. For example, the
== operator tests whether the values are equal:
2 == 2 →
Here are the six comparison operators you’ll use in Notion, which are also available in the The Notion Formula Cheat Sheet:
|Greater Than or Equal|
|Less Than or Equal|
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.
The Importance of Value Types
As you work with formulas, you'll want to remain mindful of value types. In the above examples, you've seen
Functions and operators require particular value types. For example, you cannot add
"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
"Age: " + 26 will also throw a
Type mismatch error.
To avoid these errors, Notion offers functions for converting one type two another.
Convert a Text String to a Number
To convert a text
string to a
number, we use the
toNumber( "2" ) →
2 + toNumber( "2" ) →
Convert a Number to a Text String
format() function converts a
number to a text
format( 26 ) →
That's how we were able to add the contextual term to our Age property in our basketball team gallery:
"Age: " + format( 26 ) →
However, the age was actually a property reference:
"Age: " + format( prop( "Age" ) ) →
Notion's Core Value Types
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
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
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.
strings in formulas, you'll surround them in double quotes:
"26" is a
26 is a
strings are left-aligned in table cells. If you see left-aligned numeric characters, you know they form a
string rather than the true
Boolean (True or False)
A value of the
boolean type is either
false. As we saw with the
if() formula, you can produce a
boolean value using comparison operators.
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
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.
Nested Inputs, Operators and Functions
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
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:
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" ) →
Most often, you'll want your first argument to test another property. Here, we return
false (represented by a checkbox), based on the Status of each task:
if( prop( "Status" ) == "Complete", true, false )
You can use the
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 ?.
Differences from Excel and Google Sheets
If you've used formulas in Excel or Google Sheets, you'll want to be aware of a few ways Notion differs.
One Formula Per Property
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" )
The Concatenation Operator
You're used to using
& to join expressions in Excel and Google Sheets. In Notion, you'll use
Practical Examples of Notion Formulas
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.