The Column Formula Data Type

  • Updated

The Column formula data type is one of Spreadsheet.com's unique data types, used for assigning a formula to every cell in a column. Particularly, the Column formula data type mceclip37.png shows the result of a formula that uses values from other cells in the same row. By default, cells are set to strict, meaning they can't be edited or changed by any user.

mceclip0.png

Use the Column formula data type, for instance, in your startup's cap table worksheet 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

When setting a column to the column formula data type, the update column menu provides a field for adding in the column's formula.

blobid1.png

If you leave this field blank, Spreadsheet.com will return the error: mceclip1.png.

Entering a Column Formula

There are a few important details to note around applying a formula at the column level, as detailed below:

  • = prefix: Unlike cell formulas, column formulas do not require an "=" prefix to start the formula.
  • 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 fixed cells, not relative cells or ranges. To reference a column, simply type in the letter name for the column. For instance, if you wanted to add the values in Columns B and C for every row, 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, type $A$1). For instance, if you wanted to add columns B and C divided by the fixed value in A1, you would enter (B+C)/$A$1.
  • Functions: You can use any function available in Spreadsheet.com. For more information, see our article on creating formulas, or our list of functions.

Column_Formula.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.