10 Airtable Formulas All No-coders Should Know (With Examples) (2024)

Contents

    As a highly customizable low-code platform, Airtable is one of those apps that you hear about long before you step into the nocode world. There are many reasons to believe this cloud-hosted, spreadsheet-database hybrid is one of the best for nocode automations, but top of the list is its native integrations with hundreds of apps (including ours!).

    Airtable’s modular design allows users to build systems catered specifically to their use case. By adding app extensions, setting up interfaces, and creating automations, you can access information in an easier and more meaningful way. However, not all the data you gather is in its most valuable form.

    Enter formulas.

    A spreadsheet mainstay, formulas open up a new world of possibility with your data. You can use them to reference other fields, specify conditions, and otherwise leverage the contents of your base in a more meaningful way.

    All Airtable formulas have their time in the sun. But if you aren’t planning to become the next database consultant, knowing just a few of the most commonly used items in nocode automations can help you get more value out of your base.

    How to Use Airtable Formulas

    Formulas are an Airtable field type, so to add one to your table, simply click "+" to add a new field and select Formula from the drop-down list.

    10 Airtable Formulas All No-coders Should Know (With Examples) (1)

    There will be an empty input field for you to insert your formula, and you have the option to manually type it out or paste a pre-existing one.

    If your output is a number or a date, you can also modify it to best suit your use case by choosing from the following formatting options:

    • Number : Decimal, integer, currency, percent, duration
    • Date : Date format (local, friendly, US, European, ISO), time format (12 hour, 24 hour), use the same time zone for all collaborators, display time zone

    10 Airtable Formulas All No-coders Should Know (With Examples) (2)

    10 Airtable Formulas All No-coders Should Know (With Examples) (3)

    A single formula isn’t always enough to return the value you need, which is why you may use an expression to combine values, fields, and formulas together.

    Bear Tip 🐻: When working with complicated formulas and expressions, you might want to test your logic before applying it to your main set of data. Airtable has a Formula Playground base that you can duplicate and play around with for this very purpose!

    Note : Formulas in Airtable may seem similar to those used in Excel, but keep in mind that they reference entire fields rather than individual cells. This is in line with the platform’s function as a spreadsheet-database hybrid.

    Types of Airtable Formulas

    There are dozens of formulas available for use, with each one falling under one of the following categories:

    1. Text operators and functions : To manipulate and return text values
    2. Logical operators and functions : To compare and return values based on logical expressions
    3. Numeric operators and functions : To calculate and return numeric values
    4. Date and time functions : To format, interpret, and return date and time values
    5. Array functions : To join, remove, or separate values in an array
    6. Record functions : To return record data
    7. Regex functions : To return values matching a regular expression

    Look through Airtable’s Formula Playground or Formula Field Reference page to see a comprehensive list of descriptions, syntax, and examples of the functions in order to decide what best suits your use case.

    10 Useful Formulas for Your Nocode Automations

    While there are almost a hundred different formulas and countless ways to join them into expressions, it’s unlikely that you’re going to be using them all any time soon. In fact, it’s more likely that you’ll learn a few of the most useful formulas and find that they get the job done—most of the time, that is.

    In this article, we’ll go over 10 of the (arguably) most useful Airtable formulas for your nocode automations, complete with technical functions and examples.

    1 - LEFT() and RIGHT()

    Technical Function: LEFT(string, howMany) and RIGHT(string, howMany)

    The LEFT() and RIGHT() formulas extract a certain number of characters from the beginning or end of the string. Because they extract the same number every time, these formulas are best used with consistent datasets, such as invoice numbers or area codes.

    Example 1 :

    Let’s use LEFT() to extract year of registration and RIGHT() to extract a summarized ID number from a dataset of student IDs.

    10 Airtable Formulas All No-coders Should Know (With Examples) (4)

    Using the formula LEFT({Student ID Number}, 4) and RIGHT({Student ID Number}, 6), we are left with the four-digit year of registration and six-digit number that doesn’t include year or placeholder zeroes.

    10 Airtable Formulas All No-coders Should Know (With Examples) (5)

    10 Airtable Formulas All No-coders Should Know (With Examples) (6)

    2 - LEN()

    Technical Function: LEN(string)

    The LEN() formula shows how many characters a string contains. This can be helpful when you have certain length-based output restrictions, such as when posting ad copy or printing on paper of a limited size.

    Example 2 :

    Let’s use LEN() to indicate which quotes can be published as Tweets.

    10 Airtable Formulas All No-coders Should Know (With Examples) (7)

    Using the formula LEN({Full Quote}), we can see how many characters each quote contains.

    10 Airtable Formulas All No-coders Should Know (With Examples) (8)

    10 Airtable Formulas All No-coders Should Know (With Examples) (9)

    3 - IF()

    Technical Function: IF(expression, ifTrue, ifFalse)

    The IF() formula is one of the most useful formulas for nocode automations. Acting based on a logical expression, it returns value1 if the argument is true and value2 if the argument is false. This helps you separate data based on a certain condition without having to manually filter through it.

    IF() formulas are often combined with others to create expressions, allowing you to trigger calculations based on whether or not the input meets a condition.

    Example 3 :

    Let’s use IF() to indicate whether or not our quotes from Example 2 meet can be published as Tweets.

    10 Airtable Formulas All No-coders Should Know (With Examples) (10)

    Using the formula IF({Quote Length}<280, "Tweet It!", "It's a No-Go"), we can easily see which of the quotes exceed Twitter’s 280-character limit.

    10 Airtable Formulas All No-coders Should Know (With Examples) (11)

    10 Airtable Formulas All No-coders Should Know (With Examples) (12)

    4 - SUBSTITUTE()

    Technical Function: SUBSTITUTE(string, old_text, new_text, [index])

    The SUBSTITUTE() formula replaces occurrences of old_text with new_text, and it’s incredibly helpful if you have large datasets that need to be cleared of certain keywords or phrases that need to be extracted in a creative way.

    Unlike REPLACE(), SUBSTITUTE() replaces all occurrences or a certain number of occurrences of old_text without beginning at a specified start point.

    Example 4 :

    Let’s use SUBSTITUTE() to extract the date from a string including name and date.

    10 Airtable Formulas All No-coders Should Know (With Examples) (13)

    Using the formula SUBSTITUTE(SUBSTITUTE({Reviewer Name + Date}, {Reviewer Name}, ' ')," on ",""), we are left with only the review date.

    10 Airtable Formulas All No-coders Should Know (With Examples) (14)

    10 Airtable Formulas All No-coders Should Know (With Examples) (15)

    This formula substitutes the Reviewer Name with a blank space, then does the same with “on”. Because the length of the input string varies, SUBSTITUTE() is a better formula for this use case compared to RIGHT() or something similar.

    5 - DATETIME_DIFF()

    Technical Function: DATETIME_DIFF([date1], [date2], 'units')

    The DATETIME_DIFF() formula calculates the difference between datetimes in specified units (milliseconds, seconds, minutes, hours, days, weeks, months, quarters, years). This can be useful in a wide range of situations, such as calculating subscription length, counting down to an event, or triggering an action when a milestone is reached.

    Example 5:

    Let’s use DATETIME_DIFF() to calculate membership length.

    10 Airtable Formulas All No-coders Should Know (With Examples) (16)

    Using the formula DATETIME_DIFF({Membership End}, {Membership Start}, 'days'), we can determine how long each customer has been a member.

    10 Airtable Formulas All No-coders Should Know (With Examples) (17)

    10 Airtable Formulas All No-coders Should Know (With Examples) (18)

    These numbers can be used as insight for strategic decision making or to trigger events, such as a special gift for each year of membership.

    6 - CONCATENATE()

    Technical Function: CONCATENATE(text1, [text2, ...])

    The CONCATENATE() formula joins text arguments together into a single value. Equivalent to the use of the & operator, this function can be used to combine names, create reference codes, and join mailing addresses.

    Example 6:

    Let’s use CONCATENATE() to create unique order reference codes.

    10 Airtable Formulas All No-coders Should Know (With Examples) (19)

    Using the formula CONCATENATE({Branch}, ": ", {Supplier}, " - ", {Order Number}), we are left with concatenated reference codes that summarize not only order number but also associated parties.

    10 Airtable Formulas All No-coders Should Know (With Examples) (20)

    10 Airtable Formulas All No-coders Should Know (With Examples) (21)

    7 - ARRAYUNIQUE()

    Technical Function: ARRAYUNIQUE([item1, item2, item3])

    The ARRAYUNIQUE() formula helps you remove any duplicate items in an array, making your data more digestible. This is helpful when you are aggregating data from long lists and only need unique values, such as the suppliers associated with one client or the team members who have submitted at least one report.

    Example 7:

    Let’s use ARRAYUNIQUE() to show a list of suppliers that have provided inventory items for one shop location.

    10 Airtable Formulas All No-coders Should Know (With Examples) (22)

    Using the formula ARRAYUNIQUE(values) in the aggregate formula section of a rollup field type, we are returned a short list of the suppliers who have sold stock to one shop location, without the duplicates from every single product order.

    10 Airtable Formulas All No-coders Should Know (With Examples) (23)

    10 Airtable Formulas All No-coders Should Know (With Examples) (24)

    Note : Array formulas can not be used on text strings, so they will only work when values are first aggregated using a rollup or lookup field.

    8 - ROUND()

    Technical Function: ROUND(value, precision)

    The ROUND() formula rounds a numeric value to the closest number of decimal places to an indicated precision. This helps you avoid long, seemingly endless decimals that make your data look messy. Rounding numeric values is especially helpful when it comes to currencies and actions that require a whole number.

    Example 8:

    Let’s use ROUND() to approximate project budgets per person to the closest $1.

    10 Airtable Formulas All No-coders Should Know (With Examples) (25)

    Using the formula ROUND({Full Budget}/{Team Size}, 1), we are returned a nice, rounded number that can be used for project proposals.

    10 Airtable Formulas All No-coders Should Know (With Examples) (26)

    10 Airtable Formulas All No-coders Should Know (With Examples) (27)

    9 - ENCODE_URL_COMPONENT()

    Technical Function: ENCODE_URL_COMPONENT(component_string)

    The ENCODE_URL_COMPONENT() formula turns a string of text into URL-friendly encoded equivalents. This saves valuable time when constructing browser- and server-readable addresses, especially if you aren’t familiar with URL encoding.

    Example 9:

    Let’s use ENCODE_URL_COMPONENT() to turn blog titles into URL-friendly snippets and use CONCATENATE() to join the domain and form a full URL.

    10 Airtable Formulas All No-coders Should Know (With Examples) (28)

    Using the formula CONCATENATE("https://bannerbear.com/blog/", (ENCODE_URL_COMPONENT({Blog Title})), "/"), we are left with a post URL that is ready to be used as the publishing address.

    10 Airtable Formulas All No-coders Should Know (With Examples) (29)

    10 Airtable Formulas All No-coders Should Know (With Examples) (30)

    10 - LAST_MODIFIED_TIME()

    Technical Function: LAST_MODIFIED_TIME({field1},{field2},...])

    The LAST_MODIFIED_TIME() formula returns the date and time of the most recent modification to a specified field (or fields). This can be helpful when you want to sort data by time but don’t want any later modifications to change the order of your records.

    Example 10:

    Let’s use LAST_MODIFIED_TIME() to return the time new Tweet data was added to a database.

    10 Airtable Formulas All No-coders Should Know (With Examples) (31)

    Using the formula LAST_MODIFIED_TIME({Username}, {Tweet}), we are returned the date and time that a last modification was made, but only to the Username and Tweet fields. Any changes or additions to other fields are not considered, even if they are in the same record.

    10 Airtable Formulas All No-coders Should Know (With Examples) (32)

    10 Airtable Formulas All No-coders Should Know (With Examples) (33)

    Nocode Doesn’t Mean No Control

    No- and low-code platforms like Airtable are created to minimize the need for technical coding, but they still allow a lot of control using functions and operators. While you do not need to know how to code to use them, understanding the syntax and having an idea of which formula to use in each situation can open up another world of possibilities in your automations.

    If you’re looking to learn more about Airtable and how to use it alongside apps like Zapier and Bannerbear, check out a few of our other articles:

    👉 All You Need to Know about Airtable’s Rollup Feature (in 2024)

    👉 A 5-Minute Quick Guide to Working with Airtable Views

    👉 5 Project Management Alternatives to Airtable (2024)

    10 Airtable Formulas All No-coders Should Know (With Examples) (2024)

    FAQs

    10 Airtable Formulas All No-coders Should Know (With Examples)? ›

    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.

    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 Len formula in Airtable? ›

    Using LEN() in Airtable

    You can reference a value / field / string within the function following this format: LEN(string). A popular use for this function is determining word and character counts in order to alert you if you've ran over a specified limit (word counts for blog articles, tweets).

    Does Airtable support formulas? ›

    Adding formula fields in Airtable

    Formulas allow you to output numbers, dates, strings, and more in each record based on static or dynamic information from other cells in that same record.

    What can Airtable do that Excel cannot? ›

    Difference Between Airtable and Excel

    Essentially, this means that Excel is designed for managing tabular data with calculations and formulas. On the other hand, Airtable is more geared toward managing complex data structures and relationships between different pieces of information.

    Why do people love Airtable? ›

    Airtable is a powerful cloud-based database platform that makes it easy to organize, store and collaborate on data. It combines the features of a traditional spreadsheet with the power of a database, allowing users to quickly create sophisticated databases without any coding knowledge.

    What is the \n formula in Airtable? ›

    Using the \n formula in Airtable

    You can add in-line breaks as needed by using "\n" in a formula. To see multiple lines show up in Airtable make sure the row height is set to medium or larger. For a more readable job description field, we can adjust the formula to use "\n" to insert line breaks in the right places.

    What is rollup in Airtable? ›

    A rollup field performs calculations, creates aggregates, and builds formulas on specific cells from records that are linked to another field in your table. Adding rollup fields in Airtable NOTE To use rollup fields, your base must have a ... Updated on : 02 Jul 2024.

    Can Airtable be used as a spreadsheet? ›

    Is Airtable a spreadsheet or database? Although the default view in Airtable makes it look a lot like a spreadsheet, Airtable is a relational database. This means it does more than just store data (the main function of a spreadsheet)—it stores relationships between that data.

    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.

    What problem does Airtable solve? ›

    Airtable can help you create views and workflows, keeping team members up-to-date and providing an overall view of the project timeline - a place to manage it all.

    Does Airtable require coding? ›

    Airtable is a low-code platform for application development. It lets teams build workflows that modernize their business processes and can be tailored to the technical ability of any end user. Its no-code features let nontechnical professionals harness the capabilities of relational databases to optimize workflows.

    Can Airtable do calculations? ›

    Adding, subtracting, multiplying, and dividing using one formula in Airtable. You can run any number of different functions within the same formula. In the example below, several different factors play into finding the final amount to charge a client on a payment plan: Multiply {Consult: Hours} * {Consult {Rate}

    Can you convert Airtable to Excel? ›

    Step 1: Click on the view name and select Download CSV. Step 2: Your view with the data from Airtable will be downloaded as a CSV file to your device. You can then open it in Excel, or upload it to Google Sheets.

    What is the difference between spreadsheets and Airtable? ›

    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 compatible with Excel? ›

    Read, Write, and Update Airtable Data from Excel

    Connect with live Airtable data directly from Microsoft Excel. The free Excel Add-In for CData Connect Cloud gives you real-time access to Airtable data, right from the Excel tool bar. Install the Add-In, configure your Airtable connection, and go!

    Top Articles
    Latest Posts
    Article information

    Author: Laurine Ryan

    Last Updated:

    Views: 6336

    Rating: 4.7 / 5 (57 voted)

    Reviews: 80% of readers found this page helpful

    Author information

    Name: Laurine Ryan

    Birthday: 1994-12-23

    Address: Suite 751 871 Lissette Throughway, West Kittie, NH 41603

    Phone: +2366831109631

    Job: Sales Producer

    Hobby: Creative writing, Motor sports, Do it yourself, Skateboarding, Coffee roasting, Calligraphy, Stand-up comedy

    Introduction: My name is Laurine Ryan, I am a adorable, fair, graceful, spotless, gorgeous, homely, cooperative person who loves writing and wants to share my knowledge and understanding with you.