Use our calculation builder - or write your own formulas - to automatically fill column values
So you created a column - now you have to fill it. You can always enter the values manually, row by row. But this can be very tedious, especially if you need to calculate the value first. No sweat! If you want values in your column to be automatically populated, Calculate is a great option.
The Calculate feature uses formulas to dynamically calculate values based on other data in your table or doc, and fills those values in accordingly. These calculations apply to all cells in the column, and the values in those cells cannot be edited manually.
Did you know that Calculate isn’t the only option for automatically filling columns? You can also choose Compose (for dynamic text) and AI.
Within this article, you’ll find...
Add a calculation to your column
Let’s say you have a table of Projects. You already have a Start date column, plus a Duration column (in days). Now you want to use that information to figure out the End date in your new column. No problem - you just need to add a calculation to your End date column. Follow these steps:
First choose and column and set the appropriate column type (in our example, that would be a Date column)
Then right click on the column header, and select Column options
Click into the Fill values tab
Here you’ll see 3 options for how to automatically fill the values of your column. Choose Calculations & formulas
Use the structured builder to quickly build a calculation, or click the f next to the Calculate from field to switch to the formula builder and write your own custom formula. To learn more about the options for the structured builder, jump to the section below.
Once you’re done, your column will fill with values according to your calculation. If you try and click on any of these cells, you’ll see a popup explaining that the values cannot be directly edited since they’re generated by the calculation.
💡 Tip: Want to save yourself a few clicks? You can simply right-click on the column header, then choose either Add formula (if you want to write a custom formula for the column) or Calculation builder (if you want to use the structured builder) to get started quickly.
Calculation options
When adding a calculation to your column, you have some options. If you want to write a custom formula - which gives you the most flexibility - click the f icon in the upper right of the builder or choose Custom formula from the dropdown. You can learn all about writing in Coda’s formula language here.
If you want to use the structured calculation builder, you’ll see two fields: Calculate from and Property. Continue reading to learn about each of these fields.
Calculate from
Calculate from tells Coda what other data in your table to use in the calculation. You can search for and choose any of the other columns in your table, or choose Row property. If you choose a specific column, you will calculate values based specifically on that column's data. If you choose Row property, you will calculate based on the properties of the entire row.
Property
Once you’ve selected your source data (via Calculate from), you decide which property of that data you care about. The options for this Property field will vary depending on what you’ve selected in your Calculate from section.
Standard properties include Last modified (when was the specific column or the row last modified) and Last modified by (who last modified the given column or the row).
Convert to editable
As a reminder, values in calculated columns cannot be directly edited. But maybe you no longer need your calculation and want to convert the column back to being editable - while still maintaining the values you’ve calculated thus far. You can do that by...
Right-click on the column header
Choose Convert to editable values from the list of options
The values in your column should remain the same, but the underlying calculation will no longer exist. This means values won’t be updated dynamically - they are now static and need to be manually edited.
💡Tip: If you want new rows to be given a default value and still be able to edit those values as needed, check out our Value for new rows feature.
FAQs
How can I edit an existing calculation?
Simply right-click on the column header, then select Edit calculation.
How is the calculate feature different from the default values feature?
The Calculate feature applies calculations to all cells in a given column. These values are therefore not able to be edited directly, since they’re calculated by the underlying formula and are updated dynamically.
The value for new rows feature - found within the column settings - is a way to assign a default value to newly created rows. When a row is created, this value is instantly assigned. But these values still remain editable, so you can manually change the value for each cell as needed.
Why can’t I directly edit a value in a calculated column?
Values in calculated columns are automatically calculated (and continuously updated) based on the underlying calculation. Individual cells cannot be manually edited. Instead, you may want to adjust the underlying calculation - or convert the column to editable.
What's the difference between column formulas and canvas formulas?
Column formulas are formulas in a table that perform calculations on the table data. Canvas formulas are formulas embedded in the canvas of the doc. They can be freestanding and independent or tied to data from anywhere else in the document to help summarize or aggregate key data or metrics.
In the example shown below, we have a table for your shopping list of fruit, with a column that calculates the cost of each fruit type. But we also want to know the total cost of all fruit in the table, and we want this value displayed on the canvas. That's where a canvas formula could come in handy. All we need to do is hit = while typing in the canvas to open up the formula builder.
Now, we have the table for a deeper look at data, but a handy shortcut to the most important question we have on the canvas as well.