Column formula

  • Updated

The Column formula data type mceclip0.png is for showing the result of a formula expression that computes a value based on other cells in the same row. The Column formula is a Dynamic data type and applies a formula to every cell in that column. Column formula data types are always set to strict, meaning that cell data is set at the data type level and can't be changed inside individual cells.

ColumnFormulaPanel.png

You can use the Column formula data type to automatically apply a formula that only references other cells in that row or fixed cells. For instance, if you have a sheet for your cap table, you can use a column formula to show what percent of the company each employee and investor owns (this row's shares divided by the total) or use Column formulas in your budget sheets to show a column with the current balance (Previous balance minus this row's addition or subtraction).

How to input a column formula

You can add formulas directly in the open field in the update column dialog box. If you leave it blank, Spreadsheet.com will return the error: mceclip1.png. But there are a few intricacies to note around applying a formula at the column level, as detailed below

  • = prefix: Unlike cell formulas, column formulas do not require an "=" prefix.
  • Not in Table Header: Like all other column-wide data types, the Column formula data type does not apply to cells above the table header.
  • References: Column formulas can only contain references to columns or to fixed cells, not relative cells or ranges. To reference a column, simply type in the letter for the column. For instance, if you wanted to add Columns B and C, you would type "B+C". To reference a fixed cell, type the cell location with USD currency symbols before the column and cell reference (to refer to A1, you would type $A$1). For instance, if you wanted to add columns B and C divided by the value in A1, you would enter (B+C)/$A$1.
  • Formula: You can use any formula available in Spreadsheet.com. For more information, see our articles on formulas.

ColumnFormula.gif

Column Formula Formatting

Once you’ve applied the column formula, you can format the resulting data within the Update column dialog as well. By default, the column is set as Auto detect, which automatically formats the data based on the inputs. For example, if your column formula takes two columns with currencies as inputs, the resulting data will be formatted as currency as well.

If the auto detection returns an incorrect format or if you’d like your column formula data to return a different format, you can manually select how the data appears. Within the Update column dialog, click on the Formatting tab next to Formula and select your desired data format. In addition to manually selecting the data type, the Formatting tab also allows you to apply specific styles to the resulting data.

FormatColumnFormula.gif

If you need to reference other cells outside of a row or a range of cells you can always apply a formula to a cell and paste it to the other cells in that column. Or check out our other column-only data types, as detailed in our Introduction to Data Types article.