Using Formula Columns
Formula columns leverage Excel-compatible formulas to perform powerful, flexible transformations on your data. This approach is perfect for row-level calculations, complex logic, and custom transformations.
What are Formula Columns?
Formula columns are new columns you add to your model that use Excel-style formulas to transform data. Each formula is evaluated for every row, creating calculated values based on existing columns.
Why Use Formula Columns?
Familiar Syntax
- Excel-compatible formulas that many users already know
- No need to learn SQL or programming languages
- Intuitive function names and syntax
Powerful Transformations
- Complex calculations and logic
- String manipulation and text processing
- Date and time operations
- Conditional transformations
Row-Level Control
- Apply different logic to each row
- Use conditional statements
- Combine multiple operations
Available Operations
Text Operations
Joining One or More Columns - Use CONCAT and CONCATENATE to combine text from multiple columns.
Extracting Portions of Text - Extract specific parts of text strings using LEFT, RIGHT, and MID functions.
Getting Text Length - Calculate the length of text strings with the LEN function.
Removing Extra Spaces - Clean up text by removing leading, trailing, and extra spaces with TRIM.
Text Case Conversions - Convert text to UPPER, LOWER, or PROPER case for standardization.
Replacing Text - Find and replace text within strings using SUBSTITUTE.
Logic and Conditions
Conditional Transforms (IF) - Apply different transformations based on conditions using the IF function.
Combining Conditions (AND/OR/NOT) - Build complex logical conditions with AND, OR, and NOT.
Handle NULL (COALESCE) - Provide default values for NULL data using COALESCE.
Calculations
Common Math (SUM, AVERAGE, etc) - Perform mathematical calculations with SUM, AVERAGE, MIN, MAX, and more.
Working with Date and Time - Extract and manipulate date components with TODAY, NOW, YEAR, MONTH, DAY, and more.
Validating Data - Check data types and values with ISBLANK, ISNUMBER, ISTEXT, and more.
Formula Basics
Formula Syntax
=FUNCTION_NAME(argument1, argument2, ...)
Referencing Columns
=UPPER([customer_name])
=IF([amount] > 100, "High", "Low")
Nesting Functions
=TRIM(UPPER([company_name]))
=IF(ISBLANK([email]), "No Email", LOWER([email]))
Best Practices
- Start Simple - Begin with basic formulas and add complexity as needed
- Test Incrementally - Verify results at each step when building complex formulas
- Use Meaningful Names - Give formula columns clear, descriptive names
- Handle NULLs - Always consider how your formula handles NULL values
- Document Logic - Add comments explaining complex formulas
Formula Column vs. No-Code UI
| Formula Columns | No-Code UI |
|---|---|
| Row-level transformations | Table-level operations |
| Custom calculations | Standard operations |
| Excel-like syntax | Point-and-click |
| Flexible logic | Predefined workflows |
Getting Started
Choose an operation from the list above to learn how to use specific formulas:
- New to formulas? Start with Text Case Conversions for simple transformations
- Need to combine data? See Joining Columns
- Working with conditions? Check out Conditional Transforms
- Handling missing data? Learn about Handle NULL
Complete Formula Reference
For a complete list of all available Excel formulas, see the Excel Formulas Reference.
Detailed examples and step-by-step instructions are provided in each operation guide.