Formula Basics

Users on an Enterprise plan can use AI to generate formulas. Learn more.

The sheet Owner and Admins can create and edit formulas in locked and unlocked fields, while editors can only create and edit formulas in unlocked fields.

Create a calculation or concatenation formula

Formulas can perform calculations or combine (concatenate) multiple values. 

To place a formula in a cell:

1. Select the desired cell.
2. Type an equal (=) sign in the cell, and type the calculation or concatenation.
3. Press Enter. You will see the result in the cell containing the formula.

Calculation example:

Brandfolder Image

Concatenation example

Brandfolder Image

To reference other cells when entering formulas, just select them. Remember to use quotation marks (“”) when concatenating strings. 

Create a formula using functions

Formulas can include functions, such as SUM, AVG, or IF. The Smartsheet Functions article lists supported functions to perform calculations or manipulate data in sheets.

To place a function in a cell

1. Select the desired cell.
2. Type an equal (=) sign in the cell, and then type the desired function name
3. After you’ve typed in the required syntax for the function, press Enter

You will see the result in the cell containing the formula.

Brandfolder Image

You can place formulas in Contact List cells. Automatically assign people to tasks with a VLOOKUP formula, for example. You can’t place formulas in Contact List cells used for resource management.

Formula operators

You can use the following operators in Smartsheet formulas.

Edit an existing formula

To edit an existing formula:

1. Double-click the cell containing the formula, or select the cell and press F2 (fn + F2 on Mac).
2. Make your desired formula changes, and then press Enter.

If you change your mind about editing the formula:

• If you haven’t pressed Enter yet, press Esc.
• If you have pressed Enter but haven’t saved your changes, select Undo or press Crtl + Z to revert them.

You must do it manually if you saved the changes in your sheet and want to revert them.

You can retrieve previously saved formulas from the Activity Log.

Tips for working with formulas

Use column formulas

Column formulas are the perfect solution when you need a formula applied consistently and uniformly to an entire column. Build your formula in a cell, and quickly convert it to apply to every cell in the column. The column formula will automatically apply regardless of how new rows get inserted into the sheet. Learn how to set formulas for all rows with column formulas.

See all available functions

If you're unsure which function to use or don't know its name, select the down arrow on the right of Sum in the toolbar.
Brandfolder Image

A list of the functions available will be displayed, and if you hover over a function, you will see a brief description of what it does.

Select Learn More to open a help article explaining how to use the function.
 

Work with ranges

In some cases, you might need to work with ranges, and there are several ways to do this:

• When typing a formula, select the group of cells that you want to include in the range, and Smartsheet will automatically enter the range:

  Brandfolder Image

  

For column names with more than one word in it, remember to use square brackets, for example: =SUM([Purchase Cost]2:[Purchase Cost]5)

• Some formulas return a range, so you can use them instead. For example: CHILDREN (), ANCESTORS() and COLLECT() can be used inside other formulas to provide a range. 

Brandfolder Image

Work with symbols and formulas

When you work with symbols and formulas, you must enter the symbol name in double quotes (“”) and use the correct capitalization according to the symbol’s name.

For example, the formula =IF(Status@row="Complete",green,red) displays an error. Correct usage is: =IF(Status@row = "Complete", "Green", "Red")

Symbols' names are displayed when selecting the arrow in a symbol type cell. You can also refer to Available symbols for the symbols column.

Work with percentages

The Percentage Format  Brandfolder Image  displays a percentage value instead of a decimal value, where 0.0 to 1.0 is equivalent to 0% to 100%. 

Formulas use the underlying decimal value, not the displayed percentage value, for calculations and comparisons. For example...

=0.5 + 0.4

...will return 90% in a column formatted for percentage...

=5 + 4

...will return 900% in a column formatted for percentage.

Copy a formula with drag-fill or auto-fill 

If you have a formula that you’d like to use in multiple cells without having to type the formula in each cell manually, use the following methods to copy the formula to other areas of your sheet quickly:

• Drag-fill: From the lower-right corner of a selected cell, drag it to contiguous cells to copy the formula. As you copy, the formula will automatically change its respective cell references.

  Brandfolder Image

  

  To create an absolute reference, type $ (a dollar sign) in front of the column name and/or row number in the cell reference of your formula.

• Auto-fill: You can have Smartsheet automatically copy a formula to new, vertically adjacent cells that enter the sheet. When auto-filled, the new formula will automatically change its respective cell references.
   

Areas where formula use is restricted

These cells can't contain formulas:

• Contact list cells that are being used in resource management
• System cells (for example, Modified By)
• Default columns (attachments, comments, row action indicator)
• The following cells being used for dependencies and resource management:
  • Start Date
  • End Date
  • Duration
  • Predecessors
  • % Complete

You can't create or edit formulas in these features:

• Forms
• Update requests
• Reports
• Dashboards

You can view formula results in update requests, reports, and dashboards, but you can’t edit them or enter any formula directly.

Still need help?

Use the Formula Handbook template to find more support, resources, view 100+ formulas, a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community