[email protected] +1.303.779.0344

Adding or Editing Formulas in Smartsheet

How to set up a formula in Smartsheet

Adding formulas in Smartsheet is a straightforward process. This article will provide you with tips on how to set up your formulas correctly.


Compatible Field Types

The following types of Smartsheet fields support formulas:

  • Text/Number (Can not be used in resource management or dependencies)
  • Dropdown List
  • % Allocation
  • Contact List Fields (Can not be used in resource management)
  • Symbol
  • Checkbox
  • Date (Can not be used in resource management or dependencies)

Smartsheet Formula Operators

Below is a list of operators available for use in Smartsheet formulas:

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

Creating a Smartsheet Formula in a Cell

To create a Smartsheet formula in a single cell:

  1. Select the cell you would like to use a formula in.

  2. We will type an equal sign in the selected cell, followed by the calculation.

  3. Once done, press “Enter,” you will see the results populated in the cell with the formula.


Editing a Cell's Smartsheet Formula

To change a formula in a single Smartsheet cell:

  1. Locate the formula in question and double-click on the cell that contains it.

  2. From here, make any changes to the formula and press enter. If no changes are needed, merely press Esc to exit.


Creating a Smartsheet Formula for an Entire Column

While the previous instructions showed you how to create a formula in a single Smartsheet cell, these instructions will show you how to apply a formula to an entire column.

  1. In the column we want to insert a formula into, click on any cell within that column, enter the desired formula, and then hit enter.

  2. Right-click on the cell with the entered formula and select “Convert to Column Formula.”

  3. The formula should now populate in each cell under the column in question. Also, to confirm that the column formula is applied, you should see a formula icon on the bottom left of the column label at the top.


Editing a Smartsheet Column Formula

Once you've created a column formula, it's easy to edit it:

  1. Right-click in any cell within the column that holds the formula and select “Edit Column Formula.” From here, change the formula in the cell and then hit Enter. All the cells should be updated within the column.


Column Formula Limitations

In Smartsheet, formulas that do not reference specific row numbers can be used for column formulas. Below is a table that can be used as a guide when creating column formulas:

Reference Type

Example

Supported?

@row

[Task Name]@row

Yes

Column references

[Task Name]:[Task Name]

Yes

Cross sheet references

{Task Name Column}

Yes

Cell references

[Task Name]1

No

Absolute references

[Task Name]$1

No

Partial-column ranges

[Task Name]1:[Task Name]12

No