Admins

How to Create a Formula Field in Salesforce

By Andreea Doroftei

Wondering how to get started with Formula Fields in Salesforce? There are many types of custom fields you can create, but formula fields can have a positive impact on your org by helping you to display and leverage a great deal of new, untapped information from data that already exists on your records. Crazy, right? 

This combined guide and tutorial will help you get started. Once you have completed both sections, you should understand exactly how formula fields in Salesforce work, having walked through the process of creating your first one. Additionally, we’ll cover some of the considerations and alternatives. You’ll then be ready to create many more in the future to support your organization’s different use cases.

What Is a Formula Field in Salesforce?

Formula Fields are an out-of-the-box Salesforce feature that enables you to manipulate and get more out of your existing Salesforce data. While the feature itself is a standard, readily available one, you will have to create a new custom field in order to make use of it (more on that later). 

Since most of us are familiar with Excel, this field type actually allows us to create simple or even more complex formulas based on data from a record and from its related records if needed. 

Going back to the Excel comparison, the Salesforce concept is very similar in terms of how you get the result of a formula in a spreadsheet cell based on values from other cells, columns, spreadsheets, or documents. Keep this in mind with the example below, as we will apply the same logic once we return to Salesforce. 

Formula fields can have a number of applications, such as:

  • Perform calculations between multiple number fields (which is also possible through Salesforce Reports to a certain extent). 
  • Display data from related objects. 
  • Concatenate and manipulate field contents.
  • Calculate the difference in days or a certain date between two date fields.
  • Display different images if a certain criteria is true (e.g., traffic light based on the length of another field, and more). 

Formula fields have slightly different characteristics from other custom fields – they are not editable and their values are generated behind the scenes, tweaking the logic in the Object records using the formula editor. If you can handle basic functions in an Excel sheet, then you will master Salesforce formula fields in no time!

Get Started with Formula Fields

As with any other custom Salesforce fields, formula fields are created by following just a handful of steps: 

  • Access the Object Manager from Setup, then navigate to the object you wish to create the Formula Field on. Alternatively, you can always opt for the Edit Object option from the Setup wheel if you already are on one of those Object’s records. 
  • Click New in the Fields & Relationships section of the Object you decided on.
  • Select Formula as the field type. 

Formula Output Data Type

Once a formula field is selected, you should choose which data type is most suitable for your use case. Each available type also comes with an example to make your decision easier based on what your formula output should be. 

Layout and Features

After you have selected your data type, you will be presented with the formula creation page (I would always advise clicking on the Advanced Formula tab). There are various features you can take advantage of here for building both simple and complex formulas to display different sets of data on your object:

  • Insert Field: Insert fields from your object and related objects here. Standard field syntax looks like the Amount field, while custom fields will always have “__c” appended to the end. 
  • Operators: Operators allow you to test and evaluate data in different ways – for example, performing simple mathematical equations or concatenating text.
  • Functions: Functions allow you to transform data, perform calculations, or test data in a more comprehensive way than you can with operators. While you will find common functions such as IF(), there are Salesforce-specific ones as well, like ISPICKVAL() which is often used when working with picklist fields within a formula. 

How to Create a Salesforce Formula Field

Now that we have covered all the basics, it’s time to get hands-on with creating a formula field based on a real-life scenario. 

Lead scoring is a typical use case for many businesses, and provides a numerical score for Leads based on various criteria defined by your organization. However, simply displaying a numerical Lead score may not be particularly easy (at first glance) for sales and business development teams. 

So, let’s translate the number within the Lead Score field in a custom Lead Rating formula field. This will display if the Lead is a “Must Call” or “Normal Priority”, which all users can easily understand and use. 

This step-by-step guide will help you navigate the creation of both a simple and a more complex formula, using one of the most widely known and used functions: the IF() statement. Additionally, we’ll make the field available to users by replacing the Lead Score on the Compact Layout with our new field! 

As the business grows and more granularity is needed, the Lead Scoring threshold will most likely change, alongside many other processes that have to be supported in Salesforce. 

In this particular example, the initially simple IF() formula above will change to a nested IF() with three conditions to evaluate against. 

READ MORE: 50+ Salesforce Formula Examples

Faster Formula Editing  

When using your very first formula field, it’s best to experience the out-of-the-box editor. As you continue working with these, it may become a hassle to always have to click the Insert Field button or memorize and manually type the API Names of all the fields you are about to use.

There are quite a few browser extensions out there that can transform your experience in different areas. When it comes to formulas, the Better Salesforce Formula editor can’t go unmentioned. 

Aside from the autocomplete functionality applicable to field names and functions, the fact that it simply appears as an additional tab next to the out-of-the-box “Advanced Formula” tab and the real-time syntax check bar at the bottom of the editor makes this extension the best help you can get to significantly optimize the way you create or edit formula fields. 

Salesforce Formula Field Testing

The “Check Syntax” button exemplified above while creating the field is just one of the ways you can check if your formula is correct. While the syntax may be spot-on, mistakes like referencing the wrong field or not capturing the entire requirement can still happen. Discovering and fixing such situations before the new field reaches your end users will ensure the success of the new feature. 

Make sure to: 

  • Test, test, then test again: Create enough test records in your developer sandbox to cover the different scenarios, ideally all possible variations or as many of them as you can.
  • Engage business stakeholders for user acceptance testing (UAT): It’s important for the impacted users to confirm the formula works as expected, especially if it is a complex one that will be widely used. Additionally, since they were the ones sharing a business requirement with you prior to the formula field becoming a reality, they should know what they are expecting to see in different scenarios. 
  • Once in production, smoke test*: This step is recommended for anything that is released to the production environment, just for you to know that the new field, along with the updated layout and user permissions, were all deployed correctly.

*Smoke test: verify through test or actual records that the formula you deployed works as intended in the production environment.

Frequently Asked Salesforce Formula Questions

How Many Formula Fields Can We Create in Salesforce?

Answer: There is no specified limit of how many formula fields can be created in your org or by Object. The limit is the total number of available custom fields you have left on an Object depending on your instance.

Are Formula Fields Searchable in Salesforce?

Answer: At this time, searching formula fields remains a limitation. Feel free to vote for this idea, although an update has been added that it will not be considered at least for the next few releases. 

What Happens with the Formula Field on Historical Records?

Answer: Luckily for us, once a formula field is created, it is automatically calculated for all records of the Object the formula is created on, and populated accordingly without you having to do any further steps. Make sure to mention this to your business stakeholders as well, as they will surely be thrilled that they can immediately report on historical data too.

 

Is a New Formula Field Really Needed? 

Although we already went through the creation of the formula field for the Lead Rating example, it’s imperative to always consider if a new formula field makes sense for the requirement at hand. 

There are times when having a calculated field on record may seem like the best way to go as it cannot be obtained in any other way or is needed on page layouts, list views, or some reports. However, you should always consider the available alternatives and functionalities before ultimately deciding. This means that staying up to date with Salesforce enhancements is a must either way. Did you know that as of Summer ‘24, Einstein for Formulas can also help you fix syntax errors?

For example, previously if you wanted the sellers to have the Account Revenue available on their Opportunity so that they wouldn’t have to go back and forth between the Account and Opportunity records for this key information, you could have created a formula field and called it a day, right? 

Nowadays, if you have already migrated to Dynamic Forms, it becomes as simple as dragging the Account field of your choice directly on the Opportunity Record Page by leveraging the relationships between the two objects. 

READ MORE: Converting Page Layouts to Dynamic Forms: A Quick Guide

Considerations for Using Formula Fields

There are some important considerations to be aware of when it comes to Salesforce Formula Fields, including:

  • 3900 character limit (including spaces, return characters, and comments).
  • 15000 bytes compile size.
  • You cannot use long text areas, encrypted, or description fields in formulas.
  • You cannot delete fields if they are referenced by a formula field. You will first have to amend the formula to remove the field or delete it for good, and then delete the other field.

These are just a few examples. Before including formula fields as part of your solution design, make sure you check all considerations in the official documentation

Summary

Learning formulas and logic in Salesforce is a very good skill to have as it can be used in various other places in the system, including Reports, Validation Rules, and Flows. 

Make sure you check out the recommended best practices and get some hands-on experience with this Trailhead Trail.

READ MORE: Best Practices for Formatting & Fixing Formulas in Salesforce + VIDEO

The Author

Andreea Doroftei

Andreea is a Salesforce Technical Instructor at Salesforce Ben. She is an 18x certified Salesforce Professional with a passion for User Experience and Automation. 

Comments:

    Arnold Brown
    June 17, 2024 2:57 pm
    regarding formula fields, two other things might be good to add. 1 treat blank fields as zeros vs. blanks. getting that wrong will cause pain and suffering 2. when creating standard number fields, set default value to zero. That helps a lot subsequently writing formulas that need to test for Isnull vs. Isblank. Testing not equal to zero is less error prone.

Leave a Reply