Column formula

The Column formula data type 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.

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 example, 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).

Column_Formula.png

Configuration Options

Strict

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.

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 "Formula expression is required!" 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 section on Functions.

In the example below, we have a simple expense tracker worksheet with two expense columns: "B – Budgeted Cost" and "C – Actual Cost". We want to create a third column – "D – Delta" – that tracks the difference between our actual and budgeted costs. To do this, we set the new column as a Column formula data type and use the formula "C-B".

ColumnFormula-Example.jpg

Now, anytime the values in Column B or Column C are updated, the corresponding value in Column D will update as well per our formula.

Formatting

Once you’ve applied the column formula, you can format the resulting data from the Formatting tab in the Update column dialog. By default, the column is set as Auto detect, which automatically formats the data based on the inputs. For example, as shown in our example above, 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. Click on the Formatting tab 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.

ColumnFormula-Example2.jpg

How to Use the Column Formula Data Type

Column formula data type columns update automatically and cannot be manually edited at the cell-level. When any of the values referenced in your column formula change, the Column formula column's corresponding value will update automatically.

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. Read on to learn more about the rest of Spreadsheet.com's Data Types.