Not verified hands-on. This article was written from primary documentation rather than reproduced in an org, so it carries no tested-release badge. If you run it and something differs, say so in the comments and it gets tested and corrected.

About a dozen formula functions cover nearly everything you will ever write, and the ones that get you into trouble are not the complicated ones. CASE, BLANKVALUE, TEXT and DATEVALUE handle the common work cleanly. The functions that quietly eat your compile budget are the ones that reference picklists, cross-object fields and other formulas repeatedly, and there is no regular expression function available in a formula field at all.

CASE instead of nested IF

Nested IF statements are the reflex and usually the wrong one. When you are testing a single expression against a list of literal values, CASE says the same thing in less text and compiles smaller.

CASE(TEXT(Stage__c),
  "Prospecting", 0.1,
  "Qualification", 0.25,
  "Proposal", 0.6,
  0)

The last argument is the else. Note what CASE cannot do: every comparison is equality against the same expression. The moment you need ranges or a different field per branch, you are back to IF, and nesting it four deep is a sign the logic belongs somewhere else.

BLANKVALUE, NULLVALUE and the empty text trap

BLANKVALUE(field, substitute) returns the substitute when the field is blank or null. It works on text and on numbers, which is why it has largely replaced NULLVALUE, which only handles numbers and dates.

The trap is arithmetic. A null number in a formula makes the whole expression null, so Amount__c + Fee__c returns nothing at all if either field is empty. Wrap both:

BLANKVALUE(Amount__c, 0) + BLANKVALUE(Fee__c, 0)

There is a field-level setting that treats blank numbers as zero, and it applies to the whole formula. Use it when that behaviour is what you want everywhere in the expression.

TEXT and the picklist question

TEXT() on a picklist returns the API value, not the translated label. That is almost always what you want in a formula and almost never what a user expects when they compare the output to the screen.

For a single comparison, ISPICKVAL(Stage__c, "Closed Won") is cheaper and clearer than TEXT(Stage__c) = "Closed Won". For multi-select picklists you need INCLUDES(), and nothing else will do.

Dates: DATEVALUE and friends

DATEVALUE() converts a date/time to a date, and this is where time zones bite. The conversion uses GMT, not the running user’s zone, so a record created at 6pm in a zone eight hours behind GMT reports as the following day. If a report grouped by a date formula looks off by one for some users, that is the reason.

DATE(year, month, day) builds a date from parts. TODAY() and NOW() are evaluated at read time, so a formula field using them is never stale. Date subtraction returns a number of days directly, so age calculations need no function at all.

There is no REGEX in a formula field

REGEX() exists in validation rules only. You cannot use it in a formula field, a workflow field update formula, or a Flow formula resource. This surprises people regularly.

The alternatives inside a formula field are blunt but effective. CONTAINS(text, substring) for a substring anywhere. BEGINS(text, prefix) for a prefix. FIND(search, text) returns a position, which lets you test for presence and slice in one pass. For pattern checks that genuinely need a regex, put the rule in a validation rule where REGEX() is available, or move the work to Apex.

What costs compile size

A formula field is limited on three separate axes: the characters you type, the characters after the platform expands merge fields, and the compiled size. The compiled size is the one that fails unexpectedly, because it grows from things that look small.

  • Every reference to a cross-object field expands into a join. Referencing Account.Owner.Manager.Email three times costs three times as much as storing it once.
  • Referencing another formula field inlines that formula’s entire compiled body at every use.
  • TEXT() on a picklist expands into a comparison against every value in the list, so a picklist with 80 values is expensive to convert and cheap to test with ISPICKVAL.
  • CASE is smaller than the equivalent nested IF for the same branches.

The practical fix when you hit the wall is to split the formula into two fields, or to stop referencing other formula fields and reference the source data directly.

Frequently asked

Why does my formula return blank instead of an error? A null operand in arithmetic or text concatenation propagates null through the whole expression. Wrap the nullable inputs in BLANKVALUE.

Can I use PRIORVALUE in a formula field? No. PRIORVALUE and ISCHANGED only work where there is a before and after state, which means validation rules, workflow rules and record-triggered automation.

Is IMAGE() still usable? Yes, and it is still the tidiest way to render a status icon in a report or list view. Host the image as a static resource, not an external URL you do not control.