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 | |||
| Workbook | An Excel spreadsheet file | Base | A base is like a file, containing any number of tables | |||
| Worksheet | Separate spreadsheets within the workbook | Table | Tables containing separate sets of information (although they can be linked) | |||
| N/A |
No similar feature to Airtable views |
|
View | One 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. | ||
| Column | The lettered (e.g. column B) vertical collection of cells | Field |
Various field types are used to display different data that apply to every record in the table
|
|||
| Row | The numbered (e.g. row 3) horizontal collection of cells | Record | A single record of information containing information that is stored within fields | |||
| Cell | An 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')
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')
NOTE
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.
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.
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.