Coda guide for spreadsheet users

Explore

Coda guide for spreadsheet users
Coda guide for spreadsheet users

5 ways Coda formulas are better than Excel formulas

 5 ways Coda formulas are better than Excel formulas

VLOOKUP

Index(Match)

Summary Formulas

1️⃣ Smarter, more readable formulas

Coda supports 99% of the same formulas as Excel. In fact, most of the time in Coda you don’t even have to use a formula to accomplish the same result in a spreadsheet.

⭐ Coda benefit: Dot notation. With Excel’s complex formulas, you’re required to wrap methods around “inner formulas” ー leaving you with this long block of )))))’s at the end of your formulas which can be really hard to understand. If you like that approach, Coda will support you, but you can also use our dot notation, chaining approach to help with readability & unlock auto-completing functionality.

✅ Learn more about how to do basic Excel formulas in Coda here:

Summary Formulas⁠

⁠

2️⃣ Simplify with column not cell formulas

In your traditional spreadsheet program, you perform formulas based on cell coordinates. As your spreadsheet grows, the margin of error in maintaining cell formulas gets a lot bigger.

⭐ Coda benefit: Named column formulas. To combat this problem, Coda's formula language abandons cell coordinates and uses column names and column level formulas instead. This is similar to naming cells within Excel, but the name applies to the entire column. The benefit is that it dramatically increases readability of your formulas and cuts the formulas needed by 100x.

❓I want to still use a cell formula

Today we have a few options you can use to accomplish a cell level formula including:

Add your unique formula directly on the canvas. Start typing = anywhere in the canvas to bring up the formula builder.

Create an override column. If there are exceptions for a column formula, you can create a hidden column and use an if() statement to override the column formula result.

Add a canvas column to your table to have a unique canvas per cell in that column.

Future state

Future state: we hope to offer override-able column formulas in the future

3️⃣ Goodbye dollar signs

In Excel, you rely on $ for references to other cells and : to specify a range. This system is brittle in that new columns or re-ordering columns can break formulas.

⭐ Coda benefit: Named references. Coda referencs the names of the objects in your docs (e.g. the Orders table or the Tasks table) rather than a set of location-based references (e.g. A1:D15 or F1:I5). This has a major benefit when writing formulas: they sounds more human: “Take the Orders table, filter it to Helmets, and sum the Quantity column.”

References in Excel vs. Coda

Summary

In excel

In Coda today

Coda in the future

Absolute refs

$A$1 RyCx

@row.column

N/A

Same row refs

$A1 R[0]Cx

thisRow.Column

N/A

Different row refs

$A5 R[5]Cx

thisRow.PreviousRow().runningTotal

Variable column refs

A$1 RyC[-1]

Not supported today

@ref.GetColumnValue(...)

Variable column and row refs

C5 R[-2]C[-3]

Not supported today

combo of above

There are no rows in this table

⁠

4️⃣ A more flexible way to lookup data

In Excel, you can use vlookup() or index(match) to lookup values in one sheet based on a matching value in another sheet. However, in Excel, vlookup() breaks when you re-order columns because columns have to be in a particular order. Additionally, the matching criteria in vlookup() is hard to discover and is unable to return sets.

⭐ Coda benefit: a flexible & resilient way to lookup data.

In Coda, since the formula looks at named references, as your table changes, the formula adapts with you. Confidently re-order your table, add new columns, and know that your formula will remain intact.

In Coda, you can return sets of data, not just one value, unlocking more use cases.

Lastly, you might not even need a Coda formula to accomplish a vlookup and instead can use a lookup column.

⁠

Detailed comparison of common excel vs. coda formulas

In excel

In Coda today

Coda in the future

Vlookup(), Index(match)

Filter(); lookup(); lookup column

N/A

Offset()

rowOffset()

Indirect()

Not supported

There are no rows in this table

⁠

✅ Read more about how to use the Coda equivalent formulas here:

VLOOKUP⁠

Index(Match)⁠

⁠

5️⃣ Simple, more powerful summary tables

Excel allows you to summarize data with a pivot table.

⭐ Coda benefit: Easily change your data with grouping. Unlike pivot tables, you can see all of the underlying data and use the Summarize menu for quick calculations in each of these features as well. Additionally, with one click you can easily toggle your table to other view types to visualize your data in different ways (e.g., chart, timeline).

⁠