Airtable is often compared with Excel for its multiple similarities — one of them being formulas.

Formulas allow you to manipulate and process data to either make it easier to understand or to calculate a metric. Different from traditional spreadsheets, Airtable functions are applied to the entire column instead of a single cell. Plus — since Airtable works as a database as well — it allows you to include data from different tables and improve your database.

This post will show you how Airtable formulas work, how to learn them, and what you can do to improve it with automation.

How to Write Formulas on Airtable?

Writing formulas is like writing code but over-simplified. You need to know the functions and get used to the syntax. Before that, it is important you have a fair understanding of the difference in writing formulas on Excel and on Airtable.

In order to leverage formulas on Airtable, get started with the following steps:

Step 1: Head to your Airtable base

First, log into your Airtable account and open one of your bases. Go to your Airtable database and take a look at your current table. Take this customer database as an example.

Customer Database in Airtable
Customer Database in Airtable

Ask yourself: What data can I process here? What kind of information is missing?

In this case, the table needs a key characteristic: a Customer ID field.

Step 2: Create a formula type field

To get started, click on the “add field” icon and create a new field.

Here, browse through the different field types and find the Formula option — you can also use Rollup if you need to include data from other tables in your formula. Once clicked, give your field a name and insert your formula.

airtable formulas
Airtable Formulas

Step 3: Writing your formula

Next, write the right formula.

The most used functions are for calculating averages, sum values, round numbers up, etc.

Conditional functions such as IF, OR, XOR, can be used as well.

You can include as many functions inside your formula as you need — as long as you use the syntax right. If you are beginner, follow this comprehensive guide on how to write formulas on Airtable.

You require some math knowledge and creativity to write the right formulas. In the shown example, in order to generate a unique ID for each customer, the RECORD_ID function works well enough.

concatenate formula
Using the Concatenate Formula

The concatenate function allows you to merge different strings of text, so using it to merge “C-” with the record ID works pretty well to identify it as a customer ID.

On another hand, if you need to — for example — calculate the average purchase value of each customer, you have to choose the Rollup option.

roll up function airtable
Roll up Formula in Airtable

This option allows you to bring data from linked tables and aggregate a formula. 

To use it, Choose the table (Purchases), the specific field (Purchase value), and the formula you need to apply (Average values). Click save to finish.

Common Airtable Formulas to Start Using:

  • =VLOOKUP() to search vertically in a database
  • Nested If to test multiple criteria and increase the possibility of outcomes. This function lets you place an IF function inside another IF function, like this - IF(logical, value 1, value 2). It will return value 1 if logical argument is true, else value 2.  Learn how to make nested IF statements.
  • =CONCATENATE() lets you join multiple text strings into a single text string. 
  • =SUM() to add all data in a column or entire table. Here’s how you can start using this function
  • =SUBSTITUTE() lets you replace old text with new. This function is written as SUBSTITUTE(string, old_text, new_text, [index]) where you can specify the index. Learn how to use this formula in detail.

Additionally, you must also know about the following functions: 

Basic calculations like MULTIPLY, SUBTRACT and DIVISION: Formulas in Airtable are applied across the entire field (column) since it is a relational database. 

  • Multiply: {value1} * {value 2}
  • Subtract: {value 1} - {value 2}
  • Division: {value 1}/{value 2}

You can also use all these functions in one formula as well. Follow this guide to learn how. To get you acquainted with the formulas available on Airtable, follow the guides below as your starting point:

Step 4: Format the result of your formula

The last detail is to select the format of the results.

After writing your formula, hit the formatting tab, and select how you want to show the result.

This will depend on whether the result is Numerical, Date, or String.

Numerical: The numerical format is applied to formulas that involve math operations or anything related to numbers. Includes decimal numbers, integers, currencies, percentages, and time durations.

numerical format airtable
Numerical Formatting in Airtable

Date: This format applies when you’re working with specific dates, like merging calendars, adding days to a deadline, and any time you need to show the field in date format.

date formatting airtable
Date formatting in Airtable

String: A string is a group of characters containing a mix of numbers, symbols, and plain text. There’s no way to format this information, so you won’t see any option on Airtable.

Basic Formula Terminologies

Now, in order to fully understand formulas and communicate effectively with other co-workers when collaborating, you must learn the basic terminologies. Here’s a table showing different words used to refer to different elements of your database.

airtable formulas terminologies
Airtable Formulas Terminologies

This is mostly so you can learn the different elements of formulas and learn quickly. The goal is to learn how to write more complex formulas whenever you need them.

Airtable has a great guide for writing formulas. Read it and put it into practice, the best way to learn the syntax is by using it.

Use Automation to make your formulas more actionable

If you reduce your time updating your database, you’d get more time to process the information and write better formulas.

And the best way to save your time is with automation.

When using automation software, you can integrate Airtable with more than 200 different apps to automatically fill records. Big part of the flexibility that Airtable offers come from the formulas.

Writing formulas isn’t hard, but you need to pay attention to how you’re doing it — or else you’ll find yourself very frustrated while using the app.

In the end, the best way to learn it is by doing it. So if your work is demanding you to improve the database, take it as an opportunity to practice!

Don't forget to share this...