Notion VIP Logo
BulletproofA-to-ZConsultingThe Streamline
0
Subscribe

Meet Notion's Formula Property

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.

What You'll Learn

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:

Calculate Age with Formula

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:

  1. Calculate a new value from other properties.
    In a list of products, for example, you can calculate Total Cost using Price and Quantity:
Calculate Total Cost with Formula
  1. 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."

Label Age with Formula

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:

  1. Do what? The actions to perform, such as add, subtract, format and join.
  2. To what? The input values on which to take those actions, such as "Age: " and 26.
  3. When? Any conditions required to take each action, such as if the value of a Progress property is greater than 50%.
  4. 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

Operators are characters you place between input values. They fall into three categories, two of which define actions (the third is for comparisons):

Arithmetic Operators

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.

Concatenation Operators

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 + 24

"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

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.

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.

Property References

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" ).

Calculate Total Cost with Formula

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.

Literal Values

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" )

Label Property with Formula

Then, we can display the contextualized version (the School → Labeled property) on our Gallery cards:

Label School with Formula

Constants

Notion also includes easy references to mathematical constants, including pi and e. You'll likely use these infrequently, if ever.

When? Conditions

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:

Conditional Emojis with Formula

To create a condition, you'll use the if() function, which accepts three arguments:

  1. An expression (defined under Nesting) that evaluates to true or false, typically using comparison operators (see below) or comparison functions (see the Cheat Sheet).
  2. A value to return if the condition is true.
  3. A value to return if the condition is false.

Comparison Operators

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 == 2true.

Here are the six comparison operators you'll use in Notion, which are also available in the The Notion Formula Cheat Sheet:

OperatorDescriptionExample
==Equal2 == 2true
!=Unequal2 != 2false
>Greater Than2 > 2false
<Less Than2 < 3true
>=Greater Than or Equal2 <= 2true
<=Less Than or Equal2 >= 3false

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 "?."

Conditional Emoji with Formula

Returned Value

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 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.

Convert a Text String to a Number

To convert a text string to a number, we use the toNumber() function:

toNumber( "2" )2.

Therefore: 2 + toNumber( "2" )4

Convert a Number to a Text String

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"

Label Age with Formula

However, the age was actually a property reference:

"Age: " + format( prop( "Age" ) )"Age: 26"

Notion's Core Value Types

Number

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.

String (Text)

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.

Boolean (True or False)

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.

Date

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 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:

Calculate Selling Price with Formula

Nested 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 )
Calculate Complete with Formula

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" )
Calculate Status with Formula

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 ?.

Three Conditional Emojis with Formula

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.

Property References

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.

All-in on
the all-in-one
productivity app.
Subscribe →