Use or Override Automatic Formatting and Formula Autofill
Automatic formatting and formula autofill are related features that persist patterns of formatting or formulas when you add content to a sheet. You’ll see the behavior after you type in a new blank row or cell as soon as you press Enter or Return; it will also occur in rows that are submitted from a form. This behavior will persist even if columns are hidden or locked. All sheets automatically include this functionality.
Automatic formatting or autofill for formulas is applied when specific conditions exist in the sheet. Note that it is possible to override these changes when they occur.
Conditions That Trigger Automatic Formatting
You’ll 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.
Conditions That Trigger Formula Autofill
You’ll 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.
NOTE: 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 which references an entire column, newly added rows 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.
Override Automatic Formatting or Formula Autofill
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.)
Try It on Your Own
To see autofill for formulas and automatic formatting in your own sheet:
- Apply a background color to the first two rows in a sheet.
- Insert a column named "Formula" and in the first two rows of that column, create a formula that multiplies values from the two previous columns (as in the example below).
- Begin typing into the empty row 3, or into a new row inserted above, below, or between the two existing rows.
The background color populates on the row and the formula is used in the "Formula" column automatically.