Formula Basics

Formulas allow you to perform calculations and lookups within and across multiple sheets. Formulas can be performed on numeric values entered into the formula, or values contained within cells. Formulas also allow you to automate symbols and drop-down fields.

Formulas

Download MP4 of Video(62.35 MB)

In this video, we'll look at how to create a formula and reference cells, columns, and ranges in your sheet. We'll also go through numeric formulas, logic formulas, text formulas, date formulas, and more.

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

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 is entered in a cell.

Required Permissions

You must have Editor- or Admin-level sharing permissions or be 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:

    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.

    Functions

    Formulas can include functions, such as SUM, AVG, or IF. Functions perform calculations with or manipulate data in sheets. For the complete list of functions available in Smartsheet, see the Smartsheet Functions List.

    Formula Operators

    You can use the following operators in Smartsheet formulas.

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

    Tips for Easier Formula Creation

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

    Work with Percentages

    Smartsheet treats percentages as values between 0 and 1. When you create formulas in columns formatted for percent (using the percent-button 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 more about this functionality in the help articles on Drag-fill and  Auto-filling Formulas and Formatting.
     

    Areas Where Formula Use Is Restricted

    These columns can't 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

      You can't create formulas in these features:

      • 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.)
      • Dashboards (Formula results can be viewed in dashboards, but dashboards can't contain standalone formulas.)

      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.)
      • Dashboards (Formula results can be viewed in dashboards, but the formulas can't be edited.)