Missing Excel formulas? Not to worry! We came up with the Formula Column just for you. It's capable of simple mathematical equations and more complex formulas. With our functions, you can create standalone calculations or pull data in from other columns to give you the information you need.

Note: The formula column is only available for the Pro and Enterprise plan. If you were on a Pro plan and downgraded your account, the formula columns you have created will remain intact, but you will not be able to add more formula columns.

How to add and use the column

To add it, click on the + icon located on the top right of your board and select "More columns". Once in the Column Center, search for and select the "Formula" option:

The formula builder will automatically open when you add the Formula Column. If you've exited the formula builder, simply click anywhere within the column to begin building your formula.

To build a formula, you can use any of the available functions or you can use traditional math symbols. The list of available functions is beneath the list of available columns:

If you hover over a function, the display will show you a description of the function and an example of how it works:

To add a formula, you can type into the "Type a formula" field:

Tips on using the Formula Column

Much like formulas in Excel, the Formula Column has its own logic. But not to worry! We're here to help you figure it out.

To begin with, the following are the compatible and incompatible columns--

COMPATIBLE COLUMNS

Check, Country, Creation Log, Date, Dependency, Dropdown, Email, Formula, Hour, Item ID, Last Updated, Link to Item, Long Text, Numbers, Person, Phone, Rating, Status, Text, Timeline, Time Tracking, Vote

Subitem Names and the Count of Subitems are also supported.

INCOMPATIBLE COLUMNS

Autonumber, Color Picker, Files, Link, Location, Mirror, Progress Tracking, Tags, Week, World Clock

Some limitations

1. Any formula you add will apply to the entire column.

2. The Formula Column will read the board horizontally, meaning that it can not execute calculations vertically.

3. The Formula Column can read data from other column cells, but not from the column summary or any other area of the board.

4. It also can not read other boards, so a function like a VLOOKUP is not possible at this time.

Some general rules

Using brackets

When building a formula, you may need to reference a column in order to pull that column’s value into your function. To do that, you’ll need to include the name of the column you’re referencing within these symbols: {}

It is important to use the columns’ name exactly as it appears on your boards. For example, this column:

Must be typed as “{Estimated SP}”. Like this:

SUM({Estimated SP},10)

This will take the value in the “Estimated SP” column and add 10 to it.

Values are case sensitive

Treat values as if they’re case sensitive. So, if your column name is “Estimate SP”, be sure to use a capital E, a capital S, and capital P in your formula.

This is correct: SUM({Estimated SP},10)

This is incorrect: SUM({estimate sp},10)

Note: This rule is especially true if you create a formula that references a Status label.

The comma is dynamic

When used in the formula column, the comma symbol represents something different depending on the function. Take the above formula as an example. In this formula:

SUM({Estimated SP},10)

The comma represents an addition symbol, also known as +.

In the following function:

MULTIPLY({Estimated SP},2)

The comma represents a multiplication symbol, also known as x.

When using the IF function, the comma represents the separation of each component of a conditional statement. More on that later.

Using parentheses

When you open a statement, you must also close the statement. You open a statement with a ( and close it with a ). For example:

MULTIPLY(SUM({Estimated SP},10),2)

The above formula will take the Sum of the Estimated SP column plus 10 and multiply that total by 2.

Using quotation marks

When a statement reads text of any kind, whether a Status label or a Text Column, you must put the text in quotation marks. For example, this formula is looking for the word "Yes" in the Text Column:

IF({Text}="Yes","Awesome!","Too bad.")

Formatting your formula

Using a text editor to write your formula and then copying and pasting will result in the formula builder reading it as illegal. The formatting of quotation marks and commas can impact the effectiveness of your formula. Take a look at the quotation marks in the following.

This is correct: IF({Text}="Yes","Awesome!","Too bad.")

This is incorrect: IF({Text}=”Yes”,”Awesome!”,”Too bad.”)

Using decimals

When multiplying by a decimal, always include a 0 before the decimal. If not, your formula will return an "Illegal Formula" error.

This is correct: MULTIPLY(10,0.25)

This is incorrect: MULTIPLY(10,.25)

About the IF function

The IF function is used to create a conditional statement. If you aren’t familiar with conditional statements, we can help! The IF statement will return or output one of two values. Let’s use this as an example: IF(a=true,b,c)

Or put in simpler terms:

If a is true, then return b.

If a is false, then return c.

The result, whether the formula returns a value of b or c, is conditional upon whether or not a is true.

Let’s look at that using a board and the Formula Column. For this example, we want the Formula Column to read “YAY!” when a task is done. If the task isn’t done, we want the formula to say “Not yet…”. To achieve this, we would use this formula:

IF({Status}="Done","YAY!","Not yet...")

Take a look at the results of this formula:

You'll notice in the above results, that any label that isn't "Done" reads as "Not yet...". If I want to add to the IF statement so that the "Working on it" label outputs "Almost." in the Formula Column, I'll need to build from my original statement.

IF({Status}="Done","YAY!","Not yet...")

becomes

IF({Status}="Done","YAY!",IF({Status}="Working on it","Almost.","Not yet..."))

That means: IF(a=true,b,IF(c=true,d,e))

Or put in simpler terms:

If a is true, then return b.

If c is true, then return d.

If a and c are false, then return e.

You can build on this nesting of IF() statements endlessly. But remember! Anytime you open a parenthesis, you must close it. See the colorful parenthesis in the formula above for an example of how this works.

Add a total in the footer of the formula

Just as with the Numbers Column, you can see the overall calculation for each group.

By clicking on the column summary, you will also be able to change the unit and the function as below:

Note: The total only takes into account numeric values. If your column has both numeric and text values, the summary will only count the numeric values. Mixed values like Lea123 will not be taken into account in the total. If all values are non-numeric, your summary will return N/A.

Use cases

The possibilities with the Formula Column are endless! To get an idea of how the Formula Column can work for you, check out our article on Formula Use Cases.

For a list of all of the available functions, check out our Formula Library.

You're well on your way to becoming a formula master! But if you need help, check out the Formula Use Cases article or reach out to our support team at support@monday.com.