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 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.

    In this Article:

    Note that function delimiters will differ depending on your language settings. In languages where a comma is used as a decimal separator, the function delimiter is a semicolon.

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

    Formula Operators

    You can use the following operators in Smartsheet formulas.

    <Lesser than
    >Greater than
    >=Greater than or equal to
    <=Less 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 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).

    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 Data in Another Sheet

    You can create a reference to an individual cell, ranges of cells, or entire columns. For more information on referencing data from other sheets, read our Formulas: Reference Data from Other Sheets article.

    Reference Individual Cells

    While building your formula, you can select a cell to reference it and work with that cell’s data in your formula. (You can also manually type the column name and row number to reference the cell.)

    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 contains any special characters or numbers, you must enclose it in brackets to avoid ambiguity:

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

    =[Q1]1 + [Q2]1

    =[Risk/Issue]5 + [Contingency/Mitigation]5

    Create an Absolute Reference

    You may have situations, such as when referencing a table with the VLOOKUP 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 VLOOKUP 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
    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)
    An individual cell, range of cells, or complete columns from another sheetType the name of a previously created reference to another sheet, wrapped in curly braces.=COUNT({my_sheet1 Range1})

    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.

    Perform Calculations with Dates

    You can place a formula in one date cell that adds or subtracts numbers from dates in other cells. Numbers are treated as days, unless date functions are used. More information on date functions can be found in the Functions List.

    NOTE: You can't place formulas in date columns being used for dependencies. See Enabling Dependencies & Using Predecessors for more on dependencies.

    The following table contains examples of using dates in formulas. (The dates in the table below are in mm/dd/yy format.) 

    15/19/17=Date1 - 5Subtracts 5 days from the date5/12/17
    212/10/17=Date2 + 5Adds 5 days to the date12/15/17
    31/20/18=DATE(YEAR(date12), MONTH(date12) + 1, DAY(date12))Add a month to the date with use of the DATE, YEAR, MONTH, and DAY functions2/20/18

    Work with Percentages

    Smartsheet treats percentages as values between 0 and 1. When creating formulas in columns formatted for percent (using the % 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 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.

    Columns and Features that Can't Contain Formulas

    The following columns and features of your sheet cannot contain formulas.


    • Contact columns (for example, Assigned To)
    • System columns (for example, Modified By)
    • Default columns on the right side of the sheet (attachments, comments, row action indicator)
    • Any column being used for dependencies and resource management, such as:
      • Start Date
      • End Date
      • Duration
      • Predecessors
      • % Complete
      • % Allocation


      • Forms
      • Update requests (Formula results can still be viewed in update requests, but they can't contain standalone formulas.)
      • Reports (Formula results can still be viewed in reports, but they can't contain standalone formulas.)
      • Smartsheet Sights™ (Formula results can still be viewed in Sights, but they can't contain standalone formulas.)

      Features that Don't Allow Formula Editing

      Formulas can't be edited from the following features in Smartsheet:

      • Forms
      • Update requests (Formula results can still be viewed in update requests, but the formulas can't be edited.)
      • Reports (Formula results can still be viewed in reports, but the formulas can't be edited.)
      • Sights (Formula results can still be viewed in Sights, but the formulas can't be edited.)


      Was this article helpful?

      Have questions? Let us help.

      Live Chat