Formulas let you calculate new values from your existing data. If you've used Excel, the concept will feel familiar—you write expressions that combine column values, operators, and functions to produce results. The key difference is that formulas in Analyse data apply to entire columns rather than individual cells, so a single formula calculates values for every row in your table automatically.
Where to write formulas
The formula bar is located below the toolbar at the top of your workbook. When you select a column, the formula bar displays that column's formula (if it has one). When you create a new column, the formula bar is where you'll type your calculation.
As you type in the formula bar, autocomplete suggestions appear showing function names and column names that match what you've entered. This helps you discover available functions and ensures you reference columns correctly.
Creating a calculated column
To add a calculated column to a table:
Select the table in your workbook.
In the Editor panel, click + next to Columns.
Select Add new column. A new column appears and the formula bar becomes active.
Type your formula in the formula bar. As you type, suggestions appear for functions and column names.
Press
Enteror click the tick (✓) to the right of the formula bar to save your formula.Double-click the column header to rename your column to something meaningful.
The column automatically populates with calculated values for every row in your table.
Basic syntax
Formulas use a straightforward syntax that combines column references, operators, and functions.
Referencing columns
To use a column's values in your formula, enclose the column name in square brackets:
[Column Name]
For example, to reference a column called "CO2 Emissions", you would write [CO2 Emissions].
Arithmetic operators
Use these operators to perform calculations:
Operator | Description | Example |
| Adds two values |
|
| Subtracts one value from another |
|
| Multiplies two values |
|
| Divides one value by another |
|
| Raises a value to a power |
|
| Returns the remainder after division |
|
Comparison operators
Use these operators to compare values (typically within logical functions like If):
Operator | Description | Example |
| Equal to |
|
| Not equal to |
|
| Less than |
|
| Less than or equal to |
|
| Greater than |
|
| Greater than or equal to |
|
Logical operators
Combine conditions using these operators:
Operator | Description | Example |
| Both conditions must be true |
|
| Either condition can be true |
|
| Reverses a condition |
|
Joining text
Use the ampersand (&) to combine text values:
[Site Name] & " - " & [Location]
This produces results like "Factory A - Birmingham".
Column reference types
Different situations require different reference syntax.
Reference type | Syntax | Example | Use case |
Same table |
|
| Referencing a column in the current table |
Different table |
|
| Referencing a column from another table in the workbook (used with the Lookup function—see Function reference by category) |
Control value |
|
| Referencing the selected value from a control element |
Range control minimum |
|
| Getting the minimum value from a range slider or number range control |
Range control maximum |
|
| Getting the maximum value from a range slider or number range control |
Date range start |
|
| Getting the start date from a date range control |
Date range end |
|
| Getting the end date from a date range control |
Understanding autocomplete
As you type in the formula bar, Analyse data suggests matching items to help you write formulas accurately:
Function suggestions: Type the beginning of a function name and matching functions appear. Select one to insert it with placeholder text showing the required inputs.
Column suggestions: Type an opening bracket
[and available column names appear. Columns from your current table display first.Control suggestions: Control IDs also appear in the autocomplete list, making it easy to reference control values in your formulas.
Select a suggestion by clicking it or pressing Enter when it's highlighted. This helps prevent typos in column names and shows you which functions are available.
Common errors
When something's wrong with your formula, the formula bar displays an orange border and a warning icon. Here are the most common issues:
Misspelled column names: Column references must match the exact name shown in the column header, including spaces and capitalisation. Use autocomplete to avoid typos.
Missing closing brackets: Every opening bracket needs a matching closing bracket. Check that your square brackets
[ ]for column references and parentheses( )for functions are properly paired.Type mismatches: Some operations only work with specific data types. For example, you can't add a number to text, or use a text function on a number column. Check that your formula uses compatible data types.
Invalid function syntax: Functions require specific parameters in a specific order. If a function isn't working, check the function reference to confirm you're using the correct syntax.
Working with empty values
Empty cells (called Null values) require special attention in formulas. Any arithmetic operation involving a Null value returns Null as the result.
For example, if [Scope 2 Emissions] is empty for a particular row:
[Scope 1 Emissions] + [Scope 2 Emissions]
This formula returns Null for that row, even if Scope 1 has a value.
Use these approaches to handle empty values:
IsNull function: Tests whether a value is empty:
If(IsNull([Date]), "Missing", "Present")Zn function: Converts empty values to zero:
Zn([Value1]) + Zn([Value2])Coalesce function: Returns the first non-empty value:
Coalesce([Primary], [Backup], 0)
Related articles
Function reference by category
Common formulas for Excel users