How to Use Formulas in Airtable (2024)

Airtable is a versatile productivity tool that combines the power of a database with the flexibility of a spreadsheet to supercharge the way you store and interact with your data.

One of the most basic features of a spreadsheet is formulas, and you may be wondering how to incorporate them into your bases in Airtable. Here's how you can get started with Airtable's Formula field.

Adding the Formula Field to Your Table

To start working with formulas in Airtable, you'll first have to add the Formula field to your table.

In the Grid view of your table, click the plus-sign button ("+") located at the end of the row of field headings at the top of the table. If you have a lot of fields, you may have to scroll to the right until you see it. Select Formula from the list of field options. You'll then see the Edit field menu to enter your formula.

How to Use Formulas in Airtable (1)

Depending on what you put into your formula, Airtable will automatically detect certain kinds of values, such as numbers, percentages, and dates, and, if it does, you can change how they display in your field by clicking on the Formatting tab.

When you're done entering your formula, click the Create field button to add the field to your table. If you have experience with working with functions and formulas in Microsoft Excel, you'll likely have some idea of how the Formula field works in Airtable.

However, there are some differences: many of the more specialized functions, such as for finance or engineering, aren't available. And more importantly, the Formula field applies the same formula to every record in the table; you can't adjust the formula, or use a different formula entirely, for just specific cells.

How to Make Cell References in Airtable's Formula Field

In keeping with the comparison to Excel, you might be wondering how to make references in your formulas to particular values located elsewhere in your table. Airtable doesn't have cells like A1 or C5, so how do you refer to other field values?

In Airtable, you can make references to other fields just by typing their names. So, for example, if you had a field named "Price", you would just type:

Price 

If the name of the field you want to reference has multiple words, you'll need to enclose the field name in curly brackets (the { and } brackets) to refer to them. So if you had a field named "Order quantity", you would type:

{Order quantity} 

You can use curly brackets around single-word field names too, but they aren't required in the same way they are for fields with longer names.

In that way, cell references in Airtable are similar to using named ranges in Excel, though conveniently, the names are set for you, spaces are allowed, and you can refer to them right away, without having to set them up first.

Using Operators in the Formula Field

Airtable supports a number of symbols that allow you to quickly perform a number of logical, text, and numerical functions. Collectively, these are called "operators".

The operators you might easily recognize are the operators for common mathematical equations. You can use the plus key ( + ) for addition, the hyphen, dash, or minus key ( - ) for subtraction, the asterisk key ( * ) for multiplication, and the forward slash ( / ) for division. You can also use parentheses ( ( and ) ) around equations to break out more complex equations into smaller parts, as needed.

The ampersand key ( & ) is helpful for quickly combining text, similar to the CONCAT function in Excel—though Airtable has its own version of that function, CONCATENATE(), with the same functionality.

If you have any additional text you want to add to the field's output with either function, you'll need to put it in quotation marks ( " " ), including spaces or other special characters. If you want to add quotation marks themselves into the output display, you'll need to add a backslash, then the quotation mark character, within the quotes; it should read as "\"" within the formula.

Airtable also contains a set of logical operators. These will return "TRUE" if their conditions are met, and "FALSE" if they are not met. These are "greater than" ( > ), "less than" ( < ), "greater than or equal to" ( >= ), "less than or equal to" ( <= ), "equal to" ( = ), and "not equal to" ( != ).

Some Basic Functions You Can Use in Airtable

If you're looking for some easy ways to get started with formulas, here are some basic functions you can use in Airtable to get you started.

This is barely scratching the surface of the capabilities available; Airtable's documentation offers a complete list of functions. They've even built a base that contains example outputs that you can copy into your workspace and edit.

The IF Function

The IF function is one of the most basic functions in spreadsheets, and its implementation in Airtable is essentially the same as the way the IF function works in Excel.

IF(expression, value1, value2) 

The function tests the logic entered into the expression field, then it returns value1 if that logic is true, or value2 if it is false. Like in Excel, you can create nested IF statements and insert other functions into your IF formula.

Particularly complex IF statements may be easier to visualize by taking advantage of Airtable's "multi-line editing" functionality, which lets you type your formulas more similarly to writing code, with indents representing nested statements and multiple colors representing different parts of the syntax.

A notable difference is that there is no equivalent to Excel's ISBLANK function; instead, you need to ask if the value is equal to BLANK().

The SUM Function

Similarly, as common as IF, the SUM function works just as it does in any other spreadsheet, adding together all of the values contained within the function (be they field values or numbers defined directly within the formula).

SUM(number1, [number2, ...]) 

The DATEADD Function

For a function that's a little more unusual, but you could find impressively useful, you can use the DATEADD function to add a certain length of time to dates and times.

DATEADD([date or date and time], [#], 'units') 

In the first segment of the syntax, you specify either a date alone or both a date and a time, either inline or from a "Date and Time" field. (You can't add a time to this formula by itself, without a date; it will display an error, and you'll need to re-edit your formula).

Then, specify a numeric value and a unit of time from the following: 'milliseconds', 'seconds', 'minutes', 'hours', 'days', 'weeks', 'months', 'quarters' (of a year), or 'years'.

If necessary, functions such as TODAY() (which displays the current date) or NOW() (which returns the current date and time, updated every 15 minutes and any time the base is recalculated) also work with DATEADD in lieu of a field or specific value.

A filled-in DATEADD function might look similar to this:

DATEADD("07/18/23 14:55:29", 760, "minutes") 

Accelerate Your Workflow in Airtable

The Formula field adds a powerful way to analyze and interact with your data in Airtable. From simple statements to complex creations with multiple functions, the versatility of formulas brings some of the most useful functionality of a spreadsheet tool into your base.

If you're already familiar with spreadsheets, you can quickly adapt your skills to unlock new insights, all while still taking advantage of the conveniences of Airtable's database model. This is just one of the many unique features that make Airtable such a compellingly popular tool for boosting your productivity.

How to Use Formulas in Airtable (2024)

FAQs

How to Use Formulas in Airtable? ›

To use an IF function in Airtable, you'll need to create a new field. You can do so by scrolling to the right of your base in any grid view and clicking the "+" icon. Give it a descriptive name, then choose the field type “Formula.” This calls up a box where you can write your formula.

How to use if formulas in Airtable? ›

To use an IF function in Airtable, you'll need to create a new field. You can do so by scrolling to the right of your base in any grid view and clicking the "+" icon. Give it a descriptive name, then choose the field type “Formula.” This calls up a box where you can write your formula.

What are the limitations of an Airtable? ›

Record limits: 50000 records per base - Bases in a workspace on the Team plan can support a maximum of 50000 records (rows) each. Note that this number is cumulative over different tables.

Are Airtable formulas the same as Excel? ›

Formulas. Airtable lets you use the same formulas as in Excel, but formulas get very limited functionality in this app. With Excel, every cell can contain a formula that references any other cell or field in the spreadsheet. In Airtable, formulas apply to the whole field.

What is the formula if not empty in Airtable? ›

The BLANK() formula in Airtable checks if a field is empty or not. It's often used with the IF() function to take actions when a field is not empty.

Can I put a formula in an if statement? ›

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")

What are the pros and cons of Airtable? ›

Airtable review at a glance
Pros ✅Cons ❌
User-friendly interfaceExpensive per-user pricing
Real-time collaborationRecord limitations
A range of customizable viewsAttachment space restrictions
100+ pre-built templatesClosed source, proprietary platform
6 more rows
Oct 3, 2023

Is Airtable better than Google Sheets? ›

Airtable has a lot more features, and while it can present data in spreadsheet form, it's a much more dynamic database solution. Both options are code-free and update in real time. Google Sheets is free and offers a ton more records than the free version of Airtable.

Is Airtable hard to use? ›

Airtable might look frustrating at first, but it's actually a lot easier than you think, if you put in a little time!

What can Airtable do that Excel can't? ›

Advantages of Airtable over Excel

However, there are many reasons why Airtable is better than Excel when it comes to data management. From its ease of use and intuitive interface to its powerful collaboration features and customizable templates, Airtable offers a level of flexibility that Excel simply can't match.

Should I use pivot tables or formulas? ›

Formulas are great for numbers, because we'll need to calculate descriptive statistics like mean, median, mode, standard deviation, variation, quartiles, percentiles, skewness, and kurtosis, among many others. Pivot tables are great for categories, because we'll need to calculate frequencies (like how many people).

Does Airtable have a Vlookup function? ›

Airtable doesn't natively support VLOOKUP functionality, but the answer is pretty easy: In Table 1, just convert your 49 answer fields into linked record fields. Then convert your 49 response fields into lookup fields… each one would be based on its associated answer field.

How do you check if a cell is empty in Airtable? ›

Identifying blank values in Airtable

The BLANK() function can identify an empty value in a field or output an empty value in a formula. Typically, the BLANK() function is used within IF statements.

How do you ignore blanks in a formula? ›

To ignore blank cells in a formula without producing blank cells, you can use the IF function together with the ISBLANK function. The IF function allows you to perform different calculations based on a logical condition. The ISBLANK function returns TRUE if a cell is blank, and FALSE otherwise.

Does Airtable have an API? ›

Airtable has many APIs that developers use to build customized software.

How do you use if in a formula field? ›

Use. IF( logical_test , value_if_true , value_if_false ) and replace logical_test with the expression you want evaluated; replace value_if_true with the value you want returned if the expression is true; replace value_if_false with the value you want returned if the expression is false.

How do you use or function in if formula? ›

OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False) NOT – =IF(NOT(Something is True), Value if True, Value if False)

How do you use between in an IF formula? ›

Between function for text
  1. Select the cell where you want the result to appear.
  2. Enter the formula "=IF(AND(value>=text 1,value<=text 2),"Yes","No")." In the sample table, this formula for cell D2 would be "=IF(AND(C2>=A2,C2<=B2),"Yes","No")."
  3. Press "Enter" on your keyboard to produce the result.
Jun 24, 2022

Top Articles
Latest Posts
Article information

Author: Margart Wisoky

Last Updated:

Views: 6338

Rating: 4.8 / 5 (58 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Margart Wisoky

Birthday: 1993-05-13

Address: 2113 Abernathy Knoll, New Tamerafurt, CT 66893-2169

Phone: +25815234346805

Job: Central Developer

Hobby: Machining, Pottery, Rafting, Cosplaying, Jogging, Taekwondo, Scouting

Introduction: My name is Margart Wisoky, I am a gorgeous, shiny, successful, beautiful, adventurous, excited, pleasant person who loves writing and wants to share my knowledge and understanding with you.