Writing formulas in Excel vs Airtable
  • 04 Jul 2022
  • 3 Minutes to read
  • Dark
    Light

Writing formulas in Excel vs Airtable

  • Dark
    Light

If you're an Excel user who is familiar with writing formulas, and are just getting started with Airtable, this article is for you! While the principles of formula functions are similar in Airtable, there are some key differences to learn in the beginning that will pay off as you become an Airtable formula expert.

Terminology

Excel
Excel Use
Airtable
Airtable Use
WorkbookAn Excel spreadsheet fileBaseA base is like a file, containing any number of tables
WorksheetSeparate spreadsheets within the workbookTableTables containing separate sets of information (although they can be linked)
N/A

No similar feature to Airtable views

ViewOne of the most powerful Airtable features, views are different ways of viewing data within a table, and include these view types: grid, calendar, kanban, form, and gallery.
ColumnThe lettered (e.g. column B) vertical collection of cellsField

Various field types are used to display different data that apply to every record in the table

 

RowThe numbered (e.g. row 3) horizontal collection of cellsRecordA single record of information containing information that is stored within fields
CellAn individual cell referenced by column + row (e.g. cell B4)Cell 

An individual cell that's part of a record.

Airtable is like a spreadsheet and a database

While programs like Excel function only as traditional spreadsheets, Airtable works similarly to both spreadsheets and databases. As a spreadsheet, you can structure information in a grid view with the expected format of columns, rows, and cells. As a database, you can link records to one another to create powerful data links that open up more capabilities for data entry, formula calculations, and more.

Airtable also allows you to view the same data that appears in a grid view in other ways as a calendar, kanban board, form builder, and gallery view.

Airtable formulas reference entire fields, not specific cells

In a spreadsheet, you can put a formula in any cell, and have it reference any other cell in the sheet. Airtable is a relational database so formulas are applied for the entire field (what Airtable calls a column) so that the same formula applies to every record in that field.

Excel

Any specific cell can be referenced in another cell. For example, to identify the source of each purchase in a table, you would write a formula that referenced each cell - like A3 - to check for the type of source it is.
This is the formula used below is: =IF(A3='Online','Web','Store')

360057199273excel-references-cells.jpg
References specific cells, like A3.

Airtable

A formula is always applied to an entire field, rather than specific cells. For example, to identify the source of each purchase, you would write one formula to reference the {Purchase Type} field, and the formula will check that field for every record in the table.

This is the formula used below is: IF({Purchase Type}='Online','Web','Store')

360057199153excel-airtable-compare.jpg

your title goes here

In Airtable, you don't start a formula off by writing =. You can instead just start writing the formula.

Airtable formulas work within tables, not across a base

Similar to the above, Airtable formulas by default only reference fields within the same table. However, there are ways to bring over a value from a different table in order to reference it in a formula field (this approach uses linked records and lookup fields, which are covered more in-depth here ).

Excel

To reference a record in another table, you have to write specific references to the other table using a format like the following: =IF(Tasks!B1='Home improvements',Tasks!A1,0). You then need to apply that formula to every cell (ensuring you change the cell references for each) where you want to run the formula.

360053031594ScreenShot2019-12-13at102314AM.jpg

Airtable

To reference a record in another table, you can first link them by using the linked record field. Once a record in one table, like projects, is linked to a record in another table, like tasks, you can reference either field from either table by using a lookup, rollup, or count field .

In the example below, a rollup field is used to easily reference the cost of each task related to a project, and sum the expenses to find the total project cost.

360053031374Airtablereferenceothertable.gif

This creates a number of opportunities to write both simple and complex formulas to reference data between tables.

TIP

This article is part of a guided course called Formula Foundations. You can view the entire course here.

Was this article helpful?