Create and Edit Formulas in Smartsheet

Use formulas to calculate numeric values or automate aspects of your sheet. You can create formulas, for example, to sum a range of expenses, or to check a box when a specific value enters a cell.

NOTE: You must be an Editor, Admin, or the sheet Owner in order to create and edit formulas in a sheet. Editors can only create and edit formulas in unlocked cells. Details about locked cells can be found in our Locking and Unlocking Columns and Rows article.

Prefer video? Check out our Formulas video in the Video Center.

    In this Article:

    Additional Resources

    This article provides information on creating and formatting formulas. If you're looking for details on the functions available in Smartsheet, or formula error messages, check out the following articles:

    Work with Examples Right in Smartsheet

    To see detailed examples—including syntax and parameter requirements—and to practice using formulas, try the Smartsheet Formula Examples template.


    Create a Formula

    You can create a formula in the following column types:

    • Text/Number columns not being used for Dependencies or Resource Management. Check out our Using Dependencies and Resource Management Overview articles for more information.
    • Dropdown columns
    • Symbol columns

    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.
      5+3
    3. After you’ve typed in your formula, press [Enter].
      8
      The result is displayed in the cell containing the formula.

    Formula Operators

    You can use the following operators in Smartsheet formulas.

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

    Use the Sum Tool

    If you're not sure which function to use, you can click the down arrow on the right of the Sum button sum icon in the left 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).

    Edit an Existing Formula

    To edit an existing formula:

    1. Double-click the cell containing the formula to open it for editing (or press [F2]).
    2. Make your desired formula changes, and then press [Enter].

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

    Cell References in a Formula

    To include values from other cells on the sheet in a formula, use cell references.

    Reference Individual Cells

    To reference a cell in a formula, type the column name followed by the row number.

    For example, the formula in the Inventory Value column of the following inventory management sheet will multiply the value from row 1 of the Price column to the value in row 1 of the Stock column:

    Individual reference formula

    The formula returns $2,994.00, the total worth of that item:

    Individual reference result

    Reference Column Names that Have Spaces or End in Numbers

    If a column name contains spaces or ends in a number, enclose it in brackets to avoid ambiguity:

    =[Annual Budget]1 + [Annual Budget]2

    =[Q1]1 + [Q2]1

    Create an Absolute Reference

    You may have situations, such as when referencing a table with the LOOKUP function, where you need to prevent Smartsheet from automatically updating cell references when a formula is moved or copied. Create an absolute cell reference. (You can find details on the LOOKUP function in the Function Reference article.)

    To create an absolute reference, type a $ (dollar sign) in front of the column name or row number in the cell reference of your formula. For example, if you move or copy the following formula, the column names and row numbers will not change for the cell references:

    =$[Column A]$1 * $[Column B]$1

    The following formula has absolute references to only the row numbers. If you move or copy the formula, the column references will change respectively based on the formula’s new location:

    =[Column A]$1 * [Column B]$1

    The following formula will maintain absolute references to the columns. If you move or copy the formula, the row numbers will change respectively based on the formula’s new location:

    =$[Column A]1 * $[Column B]1

    Reference a Range of Cells in the Same Column

    To reference a range of cells, type a : (colon) between the two cell references.

    For example, the formula at the bottom of the Inventory Value column of the following inventory management sheet will sum the values from row 1 to row 6 in the same column:

    Sum Range

    The formula returns $40,763.75, the total of all inventory values:

    Sum Range Result

    Reference a Whole Column

    You can reference an entire column in a formula, including all populated cells in the column. For example, the following formula sums all values in the Annual Budget column and will remain updated as new rows are added to or removed from the column:

    =SUM([Annual Budget]:[Annual Budget])

    NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula. 

    Reference a Range Across Multiple Columns

    To reference a range of values in a row across multiple columns, reference the first and last column in the row.

    For example, the formula in the Total Stock column of the following inventory management sheet will sum the values from the Stock A, Stock B, and Stock C columns on row 1:

    Sum Across Columns

    The formula returns 998, the total stock from the three locations:

    Sum Across Columns Result

    Summary of Reference Types

    Here’s a cheat sheet you can use as you build formulas of your own.

    To refer to thisFormat it this wayExamples
    Individual cellColumn name, row number.=Budget1
    Column name contains a space or ends in a numberEnclose the column name in brackets.=[Column A]1 =DAY([Q1]1)
    Absolute reference (always refers to that specific cell, row, or column)Type a $ symbol in front of the column name, row number or both.=$[Column A]$1 =[Column B]$1 =$[Column C]1
    Multiple, discontinuous cellsUse a comma between cell references.=SUM(Budget1, Expenses4, [Projected Earnings]20)
    A range of cells in the same columnReference the first cell in the range, then the last cell, separated by a : (colon).=SUM(Budget1:Budget12)
    An entire column (including any newly added cells)The column name separated by a : (colon).=SUM(Budget:Budget)
    A range of cells across multiple columnsReference the upper-rightmost cell, then the lower-leftmost cell, seperated by a : (colon).=SUM(January1:March5)

    Reference Children, Parents, and Ancestors with Hierarchy Functions

    Hierarchy functions allow you to include cells in other functions based on their indent level in a sheet. You can place a hierarchy function inside of another function, for example, to automatically reference all indented child cells underneath a parent, even as new child rows are added to the parent row.

    See Hierarchy: Indenting & Outdenting Rows for more information on creating parent and child row relationships.

    Check out each function's respective Help Center article for details on their usage:

    CHILDREN FunctionReference all child cells underneath a parent.
    PARENT FunctionReference the direct parent cell of a child cell.
    ANCESTORS FunctionReference all parent cells to a child cell.

    Copy a Formula with Drag-Fill or Auto-Fill to Save Time

    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. Check out the Using Copy and Paste article for more information.
    • 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 more about this functionality in our Auto-filling Formulas and Formatting article.

      TIP: If you don’t want the cell references in your formula to change as the formula is copied, create absolute cell references.

    Back to Top