Applies to
- Pro
- Business
- Enterprise
Automatic Format and Formula Autofill
Automatic formatting and formula autofill are features that persist patterns of formatting or formulas when you add content to a sheet.
Who can use this?
Plans:
- Smartsheet
- Pro
- Business
- Enterprise
Formulas always automatically format and autofill, when specific conditions exist in the sheet. This also occurs in newly added rows submitted from a form. This behavior will continue even if columns are hidden or locked; however, it is possible to override these changes.
Conditions that trigger automatic formatting
You will see automatic formatting occur when you type in a newly inserted or blank row that is:
- Directly between two others that have the same formatting applied.
- At the topmost row of the sheet if it's above two others that have the same formatting applied. (This includes rows inserted from a form.)
- At the bottommost row of the sheet if it's below two rows that have the same formatting applied. (This includes rows inserted from a form.)
- Above or below a single row that is between blank rows and has formatting.
Example:
Conditions that trigger formula autofill
You will see formulas populate automatically when you type in a newly inserted or blank row that is:
- Directly between two others that contain the same formula in adjacent cells.
- At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
- At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
- Above or below a single row that is between blank rows and has formulas.
Example:
Form submissions will always be fully outdented. If the last row in the sheet is indented in hierarchy, a form submission inserted at the bottom of the sheet will not auto fill the formulas or indentation of the previous rows.
If you create a formula that references an entire column, new rows added to the sheet from a form submission will be included in the formula. (More on forms here.)
For example, this COUNTIF formula =COUNTIF([In Stock?]:[In Stock?], 1) counts all checked cells in the In Stock? Checkbox type column and will remain updated as new rows are added to the column from a Form.
See the Create and Edit Formulas in Smartsheet article for more information about referencing a whole column in a formula.
When pasting multiple rows at one time, the top row needs to be pasted into a new row for the content to auto-fill
Can I override the Automatic Formatting or Formula Autofill functionality?
If you prefer not to use the automatic formatting or autofilled formula, here are your options for overriding the behavior:
- Use the formatting buttons on the toolbar or keyboard shortcuts to manually apply a different format.
- Type over a formula.
- Clear the cell contents: right-click and click Clear Cell Contents.
- Click Undo on the toolbar immediately after the autofill has been applied. (This command won’t be available after you save the sheet.)