Applies to

Smartsheet
  • 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:

    Example of when automatic formatting is triggered

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:
     Example of when formulas autofill is triggered

 

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.)
Was this article helpful?
YesNo