Overview of formula fields in Base

Help Center

AI Assistant

Help Center AI Assistant is now available

Got questions about Lark? Use our AI chat to find the answers.

Click and hold to drag

Got It

Try Now

Overview of formula fields in Base

3 min read

I. Intro

Formula fields are a type of field in Base that offers an extensive range of functions for automatic calculations, enhancing data analytics and boosting efficiency. Over 50 functions are currently supported to meet your analytical needs, from basic to advanced.

II. How to add a formula field

Open a base, select a table, and click the + icon in the top row of the table to create a new field.

250px|700px|reset

Double click any cell in the formula field to open the formula editor.

Note:

The formula entered in the formula field applies to all cells in the field. For more details, see Base function dictionary.

Only formula fields support the input of formulas. For more details, see Use fields in Base.

You can click Basic in the lower-right corner of the formulas editor to perform basic arithmetic (add, subtract, divide, multiply) on two number-type fields. Click the operator symbols to change the arithmetic operation.

250px|700px|reset

III. Key concepts

Formula components

Component	Description	Example
Table	A fundamental structure for storing data. Can be used in formulas.
Field	The value of each row in current column. Can be used in formulas.	[Field]
Table.field	All values in current field. Can be used in formulas.	[Table].[field]
Brackets	Used in formulas to reference tables or fields in Base.	SUM([Table].[Field])
Operators	Logic symbols or operators that make the formula work.	+, -, &, <, >, =, !=, %, >=, <=, and more.
Function	Predefined formulas based on mathematical rules for performing calculations.	SUM(2, 101) = 103
Parameters	Numbers or text used in the calculation.	In TEXT(value, format), "value" and "format" are the 2 parameters in this formula.
String	Text used in a formula.	In LEFT("Base", 2), "Base" is a string.
Array	A range of values.	LIST(1,2,3)

250px|700px|reset

Formula rules

Reference data in fields

Reference fields from current table: Select the fields under Fields.

250px|700px|reset

Reference fields from another table: Select the table under Tables, then select the fields you need.

250px|700px|reset

[Field] and [Table].[Field]

[Field]: When you only select [Field] in a formula, [Field] refers to the value of the corresponding cell in the column.

250px|700px|reset

[Table].[Field]: When you select [Table].[Field] in a formula, [Table].[Field] refers to all of the values in the column.

In the following example, the cells in column 2 show all of the values from column 1.

250px|700px|reset

IV. Frequently used operators

Type	Operator	Description
Numeric operators	+	Adds 2 numerical values
		-	Subtracts 1 numerical value from another
		*	Multiplies 2 numerical values
		/	Divides 1 numerical value by another
Text concatenator	&	Concatenates 2 text strings When used between a numerical value and text, the numerical value and the text will be concatenated
Logical operators	>	Greater than
		>=	Greater than or equal to
		<	Less than
		<=	Less than or equal to
		=	Equal to
		!=	Not equal to
		&&	And
		\|\|	Or

V. FAQs

How do I express "not equal to" in a formula?

Enter != in the formula.

How do I use a formula to find null values?

Enter ="" in the formula, or use the ISBLANK() function.

How do I add emojis to a formula?

Mac: In the formula editor, right-click and select Emoji & Symbols.

Windows: In the formula editor, press the Windows key and . (period) at the same time to use the emoji keyboard.

Can I change a formula field to a different field type?

Yes, you can change formula fields to text, numbers, multiple options, single option, and other field types. Only the results will be retained and not the formula itself, which means the formula will no longer work.

What do I do if the return value is empty or wrong for my sum calculation?

You may have selected the wrong field type for the sum calculation. For example, text fields cannot be used. Change the field to a number-type field and try again.

How do I change the content format after a formula calculation is completed?

To adjust the format of return values after the formula calculation is completed, double-click the formula field, and select a format under Field format. Depending on the field format you've selected, you may also select a number format under Number format.

How do I convert a formula field to another field?

Double-click the formula field and select a new field type under Field type.

Note:

After you change to another field type, automatic calculations will no longer be performed.

The return values of the formula field can come in different formats, so they might not be compatible with some field types.