Learn about the syntax to create and edit a formula in Smartsheet.
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.
Create and Edit Formulas in Smartsheet
Formulas can be placed in Contact List fields. Automatically assign people to tasks with a VLOOKUP formula, for example. Note that formulas can’t be placed in Contact List fields being used for resource management.
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 field.
Create a formula
You can create a formula in the following field types:
- Fields not being used for dependencies or resource management. For more information, see Enable Dependencies and Use Predecessors.
- Text/Number (as long as they're not being used in dependencies or resource management)
- Contact List fields (as long as they're not being used in resource management)
- Date (as long as they're not being used in dependencies or resource management)
- Dropdown List
- Checkbox
- Symbol
- % Allocation
To place a formula in a field:
- Select the desired field.
- Type an equal sign in the field, and then type the calculation.
- After you’ve typed in your formula, press [Enter].
The result is displayed in the field 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.
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:
- Double-click a field containing the formula to open it for editing.
- Make your desired formula changes, and then press Enter.
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 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. For more information, see Set Formulas for All Rows with Column Formulas.
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 field you have selected in the sheet and any hierarchical relationships with the selected field. For example, selecting the field of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the field.
After you create a formula, you can modify it at any time by double-clicking in the selected field 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 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 fields without having to manually type the formula in each field, 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 field to copy a formula across contiguous fields in the sheet. As you copy, the formula will automatically change its respective field references.
- Auto-fill—You can have Smartsheet automatically copy a formula to new, vertically adjacent fields that enter the sheet. When auto-filled, the new formula will automatically change its respective field 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 fields can't contain formulas:
- Contact list fields that are being used in resource management
- System fields (for example, Modified By)
- Default columns (attachments, comments, row action indicator)
- The following fields 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 (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.)
Set Formulas for All Rows with Column Formulas
With column formulas, you can apply uniform calculations and expressions to all rows in the sheet. Column formulas will also automatically apply to new rows added anywhere in the sheet. For example, you can:
- Track the date variance for the planned and actual completion of all tasks in a project.
- Configure an INDEX function to automatically populate information about an assigned resource, such as their title, department, and supervisor.
- Automatically calculate totals for quote requests based on information submitted through a quote request form.
Create and edit column formulas
To create a column formula:
- In any cell in your desired column, write the formula you wish to use.
NOTE: Column formulas cannot refer to cells or ranges with specific row numbers such as with absolute references, specific cell references, or partial-column ranges. Use @row, column references, and cross sheet references instead.
- After writing the formula, right-click the cell to open the context menu and select Convert to Column Formula.
The formula will be applied to all cells in the specified column, and it will be automatically filled into any new rows that are added to the sheet.
You can right-click any cell in the column and select Edit Column Formula or Convert to Cell Formula to change or remove the column formula.
Column formula limitations
Only formulas which do not reference specific row numbers can be converted to column formulas. Use the following table as a guide when writing column formulas:
Reference Type |
Example |
Supported? |
---|---|---|
@row |
[Task Name]@row |
✓ |
Column references |
[Task Name]:[Task Name] |
✓ |
Cross sheet references |
{Task Name Column} |
✓ |
Cell references |
[Task Name]1 |
X |
Absolute references |
[Task Name]$1 |
X |
Partial-column ranges |
[Task Name]1:[Task Name]12 |
X |