Formula Basics

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

The sheet Owner and Admins can create and edit formulas in locked and unlocked fields. The Owner, Admins, and Editors can create and edit formulas in unlocked fields.

Formulas and functions allow you to perform calculations and lookups within a single sheet or across multiple sheets. Formulas also allow you to automate symbols and drop-down fields.

Both manually entered values and cell references can be used to build a formula. You can add formulas to specified fields, or apply a uniform calculation to an entire column. You can also create formulas in the Sheet Summary to save space in your grid. 

Formulas can include functions, such as SUM, AVG, and IF. There are also several unique functions exclusive to Smartsheet, including hierarchy functions like CHILDREN. Check out the Functions List to see all that are available. 

Learn about the syntax to create and edit a formula in Smartsheet.

Create and edit formulas in Smartsheet

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

The sheet Owner and Admins can create and edit formulas in locked and unlocked fields. The Owner, Admins, and Editors can create and edit formulas in unlocked fields.

Create a formula

You can create a formula in the following cell types:

  • Cells not used for dependencies or resource management. Learn how to enable dependencies and use predecessors.
  • Text/Number (as long as they're not used in dependencies or resource management)
  • Contact List cells (as long as they're not used in resource management)
  • Date (as long as they're not used in dependencies or resource management)
  • Dropdown List
  • Checkbox
  • Symbol
  • % Allocation

To place a formula in a cell:

  1. Select the desired cell.
  2. Type an equal sign in the cell, and then type the calculation.
    This image shows =5+3 in a grid cell.
  3. After you’ve typed in your formula, press Enter.
    This image shows 8 in a grid cell.
    You can now see the result in the cell containing the formula.

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.

Functions

Formulas can include functions, such as SUM, AVG, or IF. Smartsheet Functions performs calculations or manipulate data in sheets.

Formula operators

You can use the following operators in Smartsheet formulas.

Symbol
 
Description
+Add
-Subtract
*Multiply
/Divide
^^Exponent
<Less than
>Greater than
>=Greater than or equal to
<=Less than or equal to
=Equal to
<>Not equal to

Edit an existing formula

To edit an existing formula:

  1. Double-click a cell containing the formula to open it for editing.
  2. Make your desired formula changes, and then press Enter.

    If you change your mind about editing the formula, press the Esc key to exit edit mode and revert to the pre-edited formula.

Tips for easier formula creation

Use column formulas

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

Use the sum tool

If you're not sure which function to use, you can select the down arrow on the right of the Sum button in the toolbar and select a function.

The Sum button will attempt to provide you with a formula based on the cell you have selected in the sheet and any hierarchical relationships with the selected cell. For example, selecting the cell of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the cell. 

After you create a formula, you can modify it at any time by double-clicking in the selected cell or by pressing F2 (fn + F2 on a Mac).

Work with percentages

Smartsheet treats percentages as values between 0 and 1. When you create formulas in columns formatted for percent (using the This image shows the percent icon found on the grid view. Percentage Format button in the toolbar), use decimal values. 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 manually type the formula in each cell, use the following methods to quickly copy the formula to other areas of your sheet:

  • Drag-fill—You can drag from the lower-right corner of a selected cell to copy a formula across contiguous cells in the sheet. As you copy, the formula will automatically change its respective cell references.
  • 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.

Learn how to drag-fill and auto-filling formulas and formatting.
 


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 formulas in these features:

  • Forms
  • Update requests (You can still view formula results in update requests, but they can't contain standalone formulas.)
  • Reports (You can still view formula results in reports, but they can't contain standalone formulas.)
  • Dashboards (You can still view formula results in dashboards, but dashboards can't contain standalone formulas.)

 You can't edit formulas from the following features in Smartsheet:

  • Forms
  • Update requests (You can still view formula results in update requests, but the formulas can't be edited.)
  • Reports (You can still view formula results in reports, but the formulas can't be edited.)
  • Dashboards (You can still view formula results in dashboards, but the formulas can't be edited.)

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