---
title: "Introduction to Formulas"
slug: "introduction-formulas"
description: "Kickstart your formula writing in Pigment with this guide, comparing syntax and operations to Excel for seamless data management."
tags: ["blocks", "Excel comparison", "formula syntax", "Modeling Formulas", "Pigment formulas", "Security Permissions"]
updated: 2026-07-28T14:44:39Z
published: 2026-07-28T14:44:39Z
canonical: "kb.pigment.com/introduction-formulas"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Introduction to Formulas

When you get started in Pigment, many operators and plenty of syntax will be [familiar from Excel](/v1/docs/pigment-vs-excel-comparison). However some syntax and basic operations are different. This guide helps you to kickstart your formula writing.

For more information on how Pigment compares to Excel, see the article [Pigment Compared to Excel.](/v1/docs/pigment-vs-excel-comparison)

> [!WARNING]
> **⚠️** **Important**
> 
> You must have the Configure Blocks permission to write formulas. The formula bar appears grayed out for those who do not have this permission.

> [!TIP]
> 🎓 **Pigment Academy**
> 
> Check out the following Pigment Academy courses before reading this article:
> 
> - [Introduction to Formulas](https://academy.gopigment.com/introduction-to-pigment-formulas)
> - [Basics of Formula Writing](https://academy.gopigment.com/basics-of-formula-writing)

## The basics

In Excel, you typically click in a cell where you’d like to add a formula, start with an “=” and then write your formula.

In Pigment, one formula applies to all data in the Block. At the top of the main pane, look for the lower case “f” by a blank rectangle—this is where to input your formula:

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/144cab31-099c-4aa2-bf6a-acf438bfc13d.png)

## How to refer to Items in Pigment

While Excel calls on data using cell references, Pigment holds data in [Blocks](/v1/docs/pigment-blocks-block-explorer), including Metrics, Lists and Properties. You directly reference the Blocks’ names when writing formulas.

To reference a **Metric**, single quotes are required unless the Metric name starts with a letter and contains no space in the name of the **Metric**:

- `'Quantity'` can be written `Quantity`
- `'Quantity 42'` needs single quotes because of the space
- `'42'` needs single quotes because it leads with a number
- `'%Margin'` needs single quotes because it leads with a non-alphanumeric characters
- `'Margin%'` can be written `Margin%` because there is no space and it leads with a letter
- `'Margin %'` needs single quotes because there is a space

To reference a **List Property**, write the List name, dot `.` , the Property name. For example, in a Metric containing lists of cities and the months, you can look up any Property of **City** or **Month**:

| **Reference** | **Returns...** |
| --- | --- |
| `City.Country` | the country of each city |
| `City.Inhabitants` | the number of inhabitants of each city |
| `Month.Quarter` | the quarter of each month |
| `Month.Year` | the year of each month |

You can also reference Properties of Properties by "chaining" them in the formula:

| **Reference** | **Returns...** |
| --- | --- |
| `City.Country.Region` | the Region of every City |
| `Orders.Country.Currency` | the Currency of every Order |

> [!NOTE]
> ℹ️ **Note**
> 
> After typing List names followed by dot, such as `City.` from the above example, Pigment provides all available Properties for that List in a dropdown. Select from the suggestions, such as `City.Country` or `City.Inhabitants`, reducing keystrokes. For Properties of Properties, you can also bring these up as suggestions by using a second dot: `City.Country.`

## Parentheses versus square brackets

- Parentheses enclose arguments, commas separate them
- Square brackets introduce formula modifiers: BY, ADD, REMOVE, FILTER, SELECT and EXCLUDE
- Reference the Functions and Modifiers app for the correct structure and syntax for all functions and modifiers

| `IF(City.Country = Country."France", 1, 0)` | Creates a Boolean of “**1**” against all cities that are in France, and “**0**” against all cities outside France |
| --- | --- |
| `Orders.Amount[BY SUM: Orders.Country]` | Sums the Property **Amount** of the **Orders** list by the Property **Country** |
| `Country.VAT[BY CONSTANT: Orders.Country]` | Looks up the **VAT** Property of each Orders' **Country** in the **Country** List |

> [!NOTE]
> ℹ️ **Note**
> 
> As your formulas grow in length, try using [**Prettify formula**](/v1/docs/use-the-formula-bar-menu#prettify-formula) from the formula bar three-dot menu, or its shortcut (**cmd**/**ctrl** + . (dot)). It autocapitalizes functions, indents, and formats formulas over multiple lines at logical breakpoints.
> 
> ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(248).png)

## Reference a specific Item

In some cases, you want to reference a specific Item of a List (a specific country, a specific month, a specific product category and so on).

The complete syntax here is 'List'.'Property'."Item". Notice how **Item** is in double quotes. The **Property** is actually optional to make it easier to point to an Item. If omitted, Pigment will use the [Default Property](/v1/docs/block-settings-transactions-dimension-lists#default-display-name).

Below are some examples.

| `Country."France"` | Points to **France** in the **Country** List |
| --- | --- |
| `Employee."Ben"` | Points to **Ben** in the **Employee** List |
| `Month."Jan 21"` | Points to **Jan 21** in the **Month** List |
| `Revenue[Month."Jan 21"]` | In a Metric **Revenue** defined by **Month**, keeps only the data of **Jan 21** (see more about filtering [here](/v1/docs/)) |

> [!NOTE]
> ℹ️ **Note**
> 
> After typing List names followed by dot and double quotes, such as `Country."` from the above example, Pigment provides all available specific items for that List in a dropdown, reducing keystrokes.

## Exclude an Item or value

There will be instances when you want to set a condition to exclude an Item or value. For example, you might want to say that a value is not equal to 0. You can choose between using `<>` or `!=`. The following works for numeric and Item values.

| `Revenue[FILTER: Revenue <> 0]` | In a Metric **Revenue**, brings in revenue data that is not equal to 0 |
| --- | --- |
| `Revenue[EXCLUDE: Month."Feb 19"]` | In a Metric **Revenue**, brings in month data apart from for February 2019 |
| `IF(City.Country <> Country."France", 1, 0)` | Creates a Boolean of “**1**” against all cities that are outside France, and “**0**” against all cities in France |

## Basic operations in Pigment

### Operations on numbers

Basic operations between numerical objects and numbers can easily be made using those four operators: additions `+`, subtractions `-`, multiplications `*`, divisions `/`. Here are some examples:

| `100` | Number 100 will be filled in every cell |
| --- | --- |
| `Quantity + 1` | 1 will be added to all non-blank cells |
| `Quantity * Seasonality` | Quantity will be multiplied by Seasonality |
| `Quantity * 'Unit Price'` | Quantity will be multiplied by Unit Price |
| `Revenue - 'Cost of Sales'` | Cost of Sales will be subtracted from Revenue |

### Operations on text

Concatenation of multiple Properties and/or strings can be done using the `&` operator:

| `"Text displayed everywhere"` | The string between double quotes will be filled in every cell |
| --- | --- |
| `Employee.'First Name' & " " & Employee.'Last Name'` | Concatenate all employee's First and Last names adding a space in between |

### Operations on dates

Adding or removing a certain number of days is also possible using `+` and `-`, exactly like in Excel:

| **Reference** | **Returns...** |
| --- | --- |
| `DATE(2021,1,1)` | 1st of January 2021 in every cell |
| `DATE(2021,1,1) + 30` | 31st of January 2021 in every cell |
| `DATE(2021,1,1) + 'Offset Metric'` | a calculation based on the value set in the Metric. If empty or 0, returns the same date. If positive, returns a later date. If negative, returns an earlier date |
| `Month.'End Date' - Employee.'Start Date'` | the number of days between the last day of the current month and the day the employee joined |

### Operations on Dimension Items

You can offset Dimension Items using integers. For example, you could set up a Time Dimension to show months alongside prior months to allow month-over-month comparisons—`Month-1`:

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/23c04f9d-4dbc-4513-b983-003eb71fba84.png)

> [!NOTE]
> ℹ️ **Note**
> 
> - This works on any Dimension, not just time Dimensions.
> - The integer doesn't have to be hardcoded. It can be a Metric with the Integer data type.

## Learn more

- [New to Pigment?](/v1/docs/get-started-pigment)
- [Understand Dimension and Transaction Lists](/v1/docs/understand-dimension-transaction-lists)