--- 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", "Security Permissions", "Modeling Formulas", "Pigment formulas", "Excel comparison", "formula syntax"] status: "update" updated: 2026-09-21T11:00:58Z published: 2026-09-21T11:01:13Z 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. 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?sv=2026-02-06&spr=https&st=2026-11-22T15%3A02%3A46Z&se=2026-11-22T16%3A02%3A46Z&sr=b&sp=r&sig=KSYM7HAPooZo51sIAPBVatuXp6t4suB2hV7Nk%2FjTVZg%3D) ## 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.` > - You can include apostrophes in your Blocks’ names, but they have to be of the following type: Alt Gr + ' (UK-Windows keyboard) ## 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?sv=2026-02-06&spr=https&st=2026-11-22T15%3A02%3A46Z&se=2026-11-22T16%3A02%3A46Z&sr=b&sp=r&sig=jlJ3SOJ6moGgheM%2BotzUO80keANTddrmnYEP3p%2FQN1s%3D) ## 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?sv=2026-02-06&spr=https&st=2026-11-22T15%3A02%3A46Z&se=2026-11-22T16%3A02%3A46Z&sr=b&sp=r&sig=kooDneZZDljo7Gt51P9RnRvHy8brR%2Bj7IjP3JfBJWEc%3D) > [!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)