Tables, not spreadsheets

Real applications need real tables.

Matt Hudson

Head of Strategy & Insights at Coda

Tables, not spreadsheets

By Matt Hudson

Share this article

Share on TwitterShare on LinkedInShare on Facebook
Blog > Productivity · 7 min read
From the very first napkin sketches of Coda (and yes, there were actual napkin sketches passed around at coffee shops, bars, or whatever office we happened to be squatting in), two things were abundantly clear: (1) we were obsessed with the idea of building a doc as powerful as an app and (2) at the center of that design had to be a table. And when I say “table”, I don’t mean a collection of adjacent cells in a Google Sheet or Excel file, nor the “tables” you might find in Microsoft Word or Google Docs that allow you to align columns of text vertically or horizontally. We knew that real applications needed real tables ー tables where rows and columns are distinctly different. We needed the relational database table ー one that has the ability to reliably join against other tables at scale through primary keys, foreign keys, and indices. Benedict Evans wrote a piece about Machine Learning that articulated some of the relevant history around relational databases well:
Why relational databases?
They were a new fundamental enabling layer that changed what computing could do. Before relational databases appeared in the late 1970s, if you wanted your database to show you, say, 'all customers who bought this product and live in this city', that would generally need a custom engineering project. Databases were not built with structure such that any arbitrary cross-referenced query was an easy, routine thing to do. If you wanted to ask a question, someone would have to build it. Databases were record-keeping systems; relational databases turned them into business intelligence systems. This changed what databases could be used for in important ways, and so created new use cases and new billion dollar companies. Relational databases gave us Oracle, but they also gave us SAP, and SAP and its peers gave us global just-in-time supply chains - they gave us Apple and Starbucks. By the 1990s, pretty much all enterprise software was a relational database - PeopleSoft and CRM and SuccessFactors and dozens more all ran on relational databases. No-one looked at SuccessFactors or Salesforce and said "that will never work because Oracle has all the databases" - rather, this technology became an enabling layer that was part of everything.
As Benedict identified, relational databases became the center of nearly all applications. So if we were going to build docs that could grow into apps, a solid foundation of relational data was a must. The primary question then was how were we going to do that. Did we have to abandon the spreadsheet model entirely, or could we keep aspects of it while introducing table-like concepts? Even subtle differences in these options would end up having pretty big implications on the formula language, on our ability to create a compelling mobile experience, and on the familiarity of the surface itself and the backwards compatibility with other tools.

A primer on spreadsheets

To really put the choice in context, you have to understand how spreadsheets actually work. At Coda, we’ve always been humbled by the power and flexibility of a spreadsheet ー regularly making the case that spreadsheet formulas are the most popular programming language in the world. However, most spreadsheet users don’t realize that there’s an alternate to the common A1:B14 style of referencing ー known as R1C1. You can read a bit more about the details of it on excel champs, but R1C1 is kind of like the metric system for spreadsheet formulas. By using R1C1 notation, it becomes easier to see how spreadsheets actually work ー based on the relative position of a cell, not just its absolute position (e.g. in B2). Said differently, when you write a spreadsheet formula of =B2 in cell C3, the underlying formula is actually an offset: =R[-1]C[-1] → “give me the value one row and one column before me”. This is why when you copy and paste the C3 cell to D3, the formula remaps to =C2 ー you’re actually pasting the same underlying value of the formula (offset by one column and one row), it just has a different display name.

Some simple spreadsheet formulas using R1C1 notation

To build docs as powerful as apps, we knew we’d need a powerful, yet flexible formula language to work over your data — like in a spreadsheet. But could we do that if we deviated too far from the spreadsheet grid? Ultimately, the options boiled down to two primary questions related to how we’d make that formula language work:
  1. Where do formulas live? Do formulas live in cells like they do in a spreadsheet, or are they defined at the column or row level? Would we support formulas on the canvas?
  2. What is the reference model? Do formulas use an object name (e.g. the name of a table or column) or refer to a location (e.g. the position of a cell ー is it geometric)? Is there a workable hybrid where you point to a location, but pretty print with the names of the object in that spot (a la Apple’s Numbers)?

The original sketch of the options for formulas in tables vs spreadsheets

In working through the options, one of the biggest problems that comes up with geometric references is that you end up with some mucky looking formulas. Paraphrasing Rob Collie, an Excel muse we talked with after starting Coda, imagine you told a software developer that they weren’t allowed to name their variables ー they had to use system-generated variable names (e.g. A4). That would seem crazy. But in spreadsheets, we oddly accept it. So if you were trying to calculate the total cost for a list of inventory, rather than write something legible like = Price * Quantity, we’re okay with = B2 * C2. There have been a few attempts to circumvent that awkwardness (e.g. named ranges, or the behavior found in Apple’s Numbers), but because the underlying substrate of a spreadsheet is a geometric grid, most users still rely on their system-generated variables like B2 or A14. But by leaning into tables, rather than the spreadsheet grid, we’re able to unlock the real magic of relational tables that is not possible in geometric-based systems.

Layouts and views

One of the primary benefits of a relational database powering your app is writable views ー the ability to see the same data in difference places, across multiple layouts, and still be able to edit the values. With tables, Coda brings that ability into your doc surface ー like having a table of names and dates and being able to edit the date in a table, or by dragging and dropping in a calendar view and having all the values stay up to date.

A simple table with a chart view and a calendar view

Humane formulas

Another example is what we’re able to do with formulas now that we’re not bound to a grid. We can put formulas anywhere ー in the table, in the writing surface itself, in configuration panels ー all using the same namespace. That means from anywhere, you can refer back to your tables and data in more legible ways. In Coda, the formula is easy to read aloud from left to right ー ‘Take the tasks table, and count if the done column is equal to true: =Tasks.CountIf(Done = True). In spreadsheets, there are multiple levels of indirection ー you have to read from inside out and know that A:A represents a data set with some meaning. =CountIf(A:A, ‘TRUE’).

A canvas formula that counts the number of open tasks in the table

Further, it also means our formula builder auto-complete can be much smarter since we know the type of data being used and which formulas can go with that data.

Column types enable our formula autocomplete to make type specific recommendations

Relationships and the performance of a database

Finally, one of the most visceral benefits of tables over spreadsheets goes back to the point made by Benedict Evans in the article I referenced earlier ー it unlocks the abilities of relational database tables in your document. Gone are the days of VLOOKUP(…) or INDEX(MATCH(…)) and the embarrassment that ensues when you realize you didn’t specify an exact match in your formula. In Coda, a lookup column defines a relationship with another table ー and then you’re able to simply project the fields of the underlying row (rather than count the number of columns to the right of your VLOOKUP range).

VLOOKUP in a spreadsheet

Say you want to pull in the person responsible for a given team into a task list. In a spreadsheet, you’d have two “tables” at arbitrary locations, and would need to create the connection between the two with a VLOOKUP. If you add a new task, be sure to copy the formula down. If someone reorders the columns, you’re toast.

The way a projection is done in a spreadsheet using a VLookup

Relationships in Coda

In Coda, the same thing is achieved with a Lookup column ー where the selection of the team (e.g. Product Management) is actually a rich reference to the entire row of the Teams table. In technical terms, references are like pointers in that they don’t require us to rescan the entire Teams table every time a new row is added.

The way a projection is done in Coda, using a Lookup column

And perhaps most important of all (but hopefully completely unnoticed by users ー unless it’s delight), by choosing tables over grids, we’ll be able to leverage all the best performance optimizations developed over the past 40 years in relational databases ー like generating hash maps for a column to do hash joins instead of loop joins ー which will allow Coda to meaningfully scale.

Revisiting spreadsheets

This choice didn’t come without downsides. First and foremost, we’re clearly doing something different than what most people expect out of a document or spreadsheet ー which is always a challenge. Concretely, something simple like direct cell referencing ー the ability to point at a very specific cell and get its value ー requires you to think a bit differently about what to ask for in Coda. So instead of getting the value for A4, with Coda you achieve that by getting the status field off the row reference of the task you want:

Getting a cell value in Coda through the @ref

There were a variety of other tradeoffs we made, but nonetheless, over 5 years later, it’s easy to look back and feel pretty confident in our choice. As a product manager, my primary tests for a decision like this are whether or not it holds up in real world scenarios (aka does it stand up with users) and can we innovate on top of it. And for the most part, it’s worked. Relational tables are still a pretty difficult concept for most people to understand ー learnability itself will forever be a challenge. And we’re also still in the early days of our performance and scale work. But every new building block we’ve introduced since then (buttons, packs, etc.) all seem to fit even better with tables than with a spreadsheet counterpart ー including the pieces we haven’t yet released. If this is something you find yourself interested in or passionate about ー reach out! We’re constantly testing out new ideas with folks in the community and we'd love to hear from you.

Related posts

Explore more stories from Coda
3 reasons to run your product team on Coda

Product teams are different — Coda fits them all.

The dark side of spreadsheet scripts

Scripts are powerful—but they have hidden costs.

Why you should think about AI as an intern

With the right guidance, AI can serve as a game-changing resource for your team.