Skip to main content

Handle NULL (COALESCE)

Provide default values for NULL data using the COALESCE formula to ensure data completeness.

Relevant Formula​

COALESCE​

Returns the first non-null value from a list of arguments.

View COALESCE Formula Reference →

Basic Usage​

=COALESCE([value], "default")

// Provide default for NULL
=COALESCE([middle_name], "")
Input: NULL
Result: ""

// First non-NULL value
=COALESCE([phone_mobile], [phone_home], [phone_work], "No Phone")

Common Use Cases​

Default Values​

// Default name
=COALESCE([nickname], [first_name], "Guest")

// Default address
=COALESCE([shipping_address], [billing_address], "Address Unknown")

Fallback Chains​

// Try multiple price sources
=COALESCE([sale_price], [regular_price], [list_price], 0)

// Multiple contact methods
=COALESCE([email], [phone], [address], "No Contact Info")

Data Completeness​

// Ensure non-NULL for calculations
=COALESCE([quantity], 0) * [price]

// Prevent NULL in concatenation
=CONCAT(
    [first_name],
    " ",
    COALESCE([middle_name], ""),
    " ",
    [last_name]
)

Advanced Techniques​

Multiple Fallbacks​

// Try several columns in order
=COALESCE(
    [primary_email],
    [secondary_email],
    [work_email],
    [contact_email],
    "no-email@domain.com"
)

Conditional Defaults​

// Different defaults based on type
=IF([type] = "customer",
    COALESCE([customer_rate], [default_rate]),
    COALESCE([wholesale_rate], [default_rate]))

With Calculations​

// Safe division with NULL handling
=COALESCE([numerator], 0) / COALESCE([denominator], 1)

COALESCE vs. IF with ISBLANK​

COALESCEIF with ISBLANK
Cleaner syntaxMore verbose
Multiple fallbacks easyNested IFs needed
NULL-specificCan check other conditions
Modern approachTraditional approach

Comparison:​

// Using COALESCE
=COALESCE([value], "default")

// Using IF
=IF(ISBLANK([value]), "default", [value])

Best Practices​

  1. Order Fallbacks - Put most preferred values first
  2. Always Include Final Default - Ensure non-NULL result
  3. Consider Empty Strings - NULL vs. empty string handling
  4. Use for Calculations - Prevent NULL in math operations
  5. Document Fallback Logic - Explain priority order

Common Patterns​

Contact Information​

=COALESCE([mobile], [home], [work], "No Phone")

Pricing Hierarchy​

=COALESCE([custom_price], [tier_price], [base_price], 0)

Name Display​

=COALESCE([preferred_name], [first_name], [username], "Anonymous")

Date Fallbacks​

=COALESCE([completed_date], [shipped_date], [ordered_date])

NULL Handling Strategies​

For Text​

// Empty string for NULL
=COALESCE([text_field], "")

// Placeholder text
=COALESCE([description], "No description provided")

For Numbers​

// Zero for NULL
=COALESCE([quantity], 0)

// Sentinel value
=COALESCE([age], -1)

For Dates​

// Far future date
=COALESCE([expiry_date], DATE(9999, 12, 31))

// Current date
=COALESCE([start_date], TODAY())

Additional Formula References​

Additional examples will be added soon.