Set Formulas for All Rows with Column Formulas

This Help Article Appears in the Following Learning Tracks

Capabilities

Who can use this capability?

Permission requirements icon The Owner and Admins can set, edit, and remove column formulas. A license is required.

Plan availability icon Individual, Business, Enterprise, and Premier

Currently, Column Formulas does not support semicolons. To work around this, create a formula without a semicolon, convert to a column formula, and then edit column formula to put the semicolon back in. We are aware of this issue and working to fix it as soon as possible.

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:

  1. 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.
     
  2. 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