PlaidXL Custom Functions

PlaidXL adds functions to Excel that read PlaidCloud data straight into cells. Type =PLAIDXL. in a cell and Excel’s autocomplete lists them, with a short description of each argument. The Formulas tab of the task pane can also write one for you; see Insert a Formula From the Task Pane.

Group	Functions	Use them to
Lists	LIST_PROJECTS, TABLE.TBL_LIST, TABLE.GET_COLUMNS, DIMENSION.DIM_LIST	See what’s available to build on
Tables	TABLE.GET_TABLE	Read a whole table into a range
Hierarchies	DIMENSION.GET_DIMENSION, DRILL	Lay out a dimension’s nodes as report rows
Values	CELL, CELLS	Calculate a rolled-up value for any combination of nodes
Building blocks	REPR.TABLE_REPR, REPR.MEASURE, REPR.MEASURES, REPR.MEASURE_TYPE, REPR.DIMENSION_REPR, REPR.DRILL_KEY	Describe the table, measure, aggregation, nodes and drill depth that CELL, CELLS and DRILL use

Build a Report With Custom Functions shows these working together.

How the Functions Work

• Sign in first. The functions read with your PlaidCloud sign-in, so sign in from the task pane before you use them.
• They read from the workbook’s project. Every function reads from the project linked to the workbook in the task pane. GET_TABLE, TBL_LIST and DIM_LIST also take an optional project ID, to read another project without changing the link. Each workbook has its own project, so two open workbooks can read from different projects.
• Results spill. A function that returns a list or a table fills the cells below it (or to its right). Leave room for the results; if anything is in the way, Excel shows a #SPILL! error.
• Recalculate to pick up changes. A PlaidXL formula fetches again when one of its arguments changes. To pick up new data in PlaidCloud, or a different project linked in the task pane, force a full recalculation — on Windows, press Ctrl+Alt+F9. Refresh All Tables refreshes retrieved tables, not formulas.
• Use exact names. Type table, column, dimension and node names exactly as they appear in PlaidCloud.

Arguments in square brackets are optional.

Lists

LIST_PROJECTS

=PLAIDXL.LIST_PROJECTS([horizontal])

Lists the names of the projects you can open, sorted by name.

• horizontal — TRUE to list across a row. Defaults to FALSE, a column.

TBL_LIST

=PLAIDXL.TABLE.TBL_LIST([project_id], [horizontal])

Lists the names of the tables in a project, sorted by name.

• project_id — the project to read. Defaults to the workbook’s project.
• horizontal — TRUE to list across a row. Defaults to FALSE, a column.

GET_COLUMNS

=PLAIDXL.TABLE.GET_COLUMNS(table_name, [column_filter], [orientation])

Lists the column names of a table in the selected project.

• table_name — the table’s name in PlaidCloud.
• column_filter — a data type, such as "text", to list only the columns of that type. Leave it out to list every column.
• orientation — "vertical" (the default) for a column, or "horizontal" for a row.

DIM_LIST

=PLAIDXL.DIMENSION.DIM_LIST([project_id], [horizontal])

Lists the names of the dimensions in a project.

• project_id — the project to read. Defaults to the workbook’s project.
• horizontal — TRUE to list across a row. Defaults to FALSE, a column.

Tables

GET_TABLE

=PLAIDXL.TABLE.GET_TABLE(table_name, [project_id], [limit])

Returns a table’s rows with a header row, spilled from the formula’s cell. Empty values come back as blank cells.

• table_name — the table’s name in PlaidCloud.
• project_id — the project to read. Defaults to the workbook’s project.
• limit — the most rows to return. Defaults to 1,048,575, the most a worksheet holds below a header row.

GET_TABLE returns every column. To choose columns or filter rows, retrieve the table from the task pane instead.

Hierarchies

GET_DIMENSION

=PLAIDXL.DIMENSION.GET_DIMENSION(dimension_name, start_node, [hierarchy], [orientation], [generation_limit])

Lays out a dimension’s nodes, starting at start_node and working down through its descendants. Each generation is indented four spaces more than its parent, and Excel outline groups let you collapse and expand each level.

• dimension_name — the dimension’s name, or its path in the project.
• start_node — the node to start from, such as the dimension’s root.
• hierarchy — the alternate hierarchy to follow. Defaults to "main".
• orientation — "vertical" (the default) to list nodes down a column, or "horizontal" to list them across a row.
• generation_limit — how many generations to return, counting start_node as the first: 2 returns the start node and its children. Leave it out to return every generation.

DRILL

=PLAIDXL.DRILL(dimension_name, start_node, drill_key, [hierarchy])

Returns start_node and the nodes below it that drill_key asks for, down a column, indented by generation and grouped in an Excel outline.

• dimension_name — the dimension’s name, or its path in the project.
• start_node — the node to drill from.
• drill_key — how far to drill, from DRILL_KEY: every descendant, only the leaves, a number of levels, or a range of generations.
• hierarchy — the alternate hierarchy to follow. Defaults to "main".

For example, =PLAIDXL.DRILL("Region", "Total", PLAIDXL.REPR.DRILL_KEY("leaves")) lists Total followed by every leaf region beneath it.

The expand commands on the ribbon do the same thing for a dimension and node you’ve typed in two cells, without writing a formula.

Values

CELL and CELLS calculate a measure — a numeric column of a table — rolled up to the nodes you choose in one or more dimensions. Think of each call as one intersection in a report: Revenue for EMEA in FY2026.

You describe the intersection with the building-block functions below, usually in their own cells, then pass those cells to CELL or CELLS in any order:

Argument	Built with	Required
The table to read	TABLE_REPR	Exactly one
The column to aggregate	MEASURE for CELL, MEASURES or several MEASURE cells for CELLS	Yes
How to aggregate it	MEASURE_TYPE	No — defaults to sum
A filter on a column	DIMENSION_REPR	At least one, one per column

Each filter names a column of the table and a value. When that column is linked to a dimension in PlaidCloud, the value is a node, and the filter includes everything that rolls up to that node in the dimension’s main hierarchy — on a Region column linked to a geography dimension, the node EMEA covers every country beneath it. On a column that isn’t linked to a dimension, the filter matches the value exactly.

The examples on this page use a table whose Region and Period columns are linked to dimensions. Use your own table’s column names.

CELL

=PLAIDXL.CELL(table, measure, [measure_type], node, [node], ...)

Returns one number: the measure, aggregated across the rows that fall under every node you pass.

A1  =PLAIDXL.REPR.TABLE_REPR("Sales")
A2  =PLAIDXL.REPR.MEASURE("revenue")
A3  =PLAIDXL.REPR.MEASURE_TYPE("sum")
A4  =PLAIDXL.REPR.DIMENSION_REPR("Region", "EMEA")
A5  =PLAIDXL.REPR.DIMENSION_REPR("Period", "FY2026")
B1  =PLAIDXL.CELL(A1, A2, A3, A4, A5)

B1 shows total revenue for EMEA in FY2026.

A sheet full of CELL formulas needs fewer lookups per cell, because PlaidXL reuses the table’s column details for a short time instead of fetching them for every cell. If a value can’t be calculated, the cell shows #VALUE! and hovering over it shows the reason as PlaidCloud reported it, such as a filter column that isn’t in the table or a node with no members. CELLS shows the first reason for the whole result.

CELLS

=PLAIDXL.CELLS(table, measures, [measure_type], node, [node], ...)

Like CELL, but for several measures at once. Returns one number per measure, down a column, in the order you listed them.

D1:F1  revenue | cost | units
D2     =PLAIDXL.REPR.MEASURES(D1:F1)
B2     =PLAIDXL.CELLS(A1, D2#, A3, A4, A5)

D2# passes the whole spilled result of MEASURES. B2:B4 show revenue, cost and units for EMEA in FY2026.

Building Blocks

These functions don’t read data themselves. Each returns a short piece of text that describes one part of a calculation, ready to pass to CELL, CELLS or DRILL. Put them in cells you reference, so one change — a different period, say — updates every formula that uses them.

TABLE_REPR

=PLAIDXL.REPR.TABLE_REPR(table_name)

The table CELL and CELLS read from, by its name in the selected project.

MEASURE

=PLAIDXL.REPR.MEASURE(column_name)

The numeric column CELL aggregates.

MEASURES

=PLAIDXL.REPR.MEASURES(column_names)

Several measure columns for CELLS, from a range of cells that hold column names. The result spills one cell per column; pass it to CELLS with the spill operator, such as D2#.

MEASURE_TYPE

=PLAIDXL.REPR.MEASURE_TYPE(agg_function)

How to aggregate the measure. agg_function is one of these, in lowercase:

Value	Returns
sum	The total (the default when you don’t pass a measure type)
mean	The average
median	The middle value
min	The smallest value
max	The largest value
std	The standard deviation
var	The variance

DIMENSION_REPR

=PLAIDXL.REPR.DIMENSION_REPR(dimension_name, start_node)

A filter for CELL and CELLS. Pass one for each column you want to filter by.

• dimension_name — the name of a column in the table — the column that holds the dimension’s values, such as "Region". It must match the column name exactly; a column that isn’t in the table returns an error.
• start_node — the node to roll up to, such as "EMEA". On a column that isn’t linked to a dimension, the value to match.

Reference a cell for the node to build a grid: =PLAIDXL.REPR.DIMENSION_REPR("Region", $A7). Leading and trailing spaces are ignored, so you can point it at the indented node names DRILL lays out.

DRILL_KEY

=PLAIDXL.REPR.DRILL_KEY(d_key)

How far DRILL goes below its start node. d_key is plain text, either a word form or a drill key written out:

d_key	Drills to
"all"	Every descendant
"leaves"	Only the leaves, which are the nodes with nothing below them
"1 level", "2 levels", …	That many generations down
">[...]"	The drill key written out, as in the next table

Write the number as digits: "2 levels", not "two levels". A leading drill is ignored, so "drill 2 levels" also works.

A written-out key counts generations down from the start node, which is generation 0 and is always included. Negative numbers count up from the leaves of each branch, so a ragged hierarchy works the way you’d expect.

Key	Includes
>[*] or >[:]	Every node
>[] or >[-1]	Only the leaves
>[n]	The start node and n generations below it. >[0] is the start node alone.
>[a:]	The start node, then generation a and everything below it
>[a:b]	The start node, then generations a up to b - 1
>[0:-k]	The start node and every node that has at least k generations below it, so the last k generations of each branch are left off. >[0:-1] is every node except the leaves.
>[a:-k]	The same, starting at generation a

For the hierarchy A, with children B, C, and D, where B has B1 and B2 and C has C1:

Key	Result
>[1]	A, B, C, D
>[2]	A, B, B1, B2, C, C1, D
>[]	A, B1, B2, C1, D
>[0:-1]	A, B, C
>[2:]	A, B1, B2, C1

Nodes are listed in hierarchy order, each one under its own parent, and the Excel outline groups follow the same order. Spaces inside a key are ignored. A key that doesn’t match one of these forms returns an error that lists the valid forms.

Caution

>[-1] and >[0:-1] mean something different from earlier versions of PlaidXL, where they were backwards. >[-1] now returns only the leaves, and >[0:-1] everything except the leaves. Review any workbook that wrote a drill key out by hand. The word forms "all", "leaves" and "2 levels" are unchanged.

Note

CELL, CELLS and DRILL expect the output of these building-block functions. Build each argument with its function rather than typing the text yourself.

Insert a Formula From the Task Pane

The Formulas tab of the task pane writes a formula for you. Under What do you want in the cell?, choose one:

• A whole table (GET_TABLE) — pick or type the table, and optionally a maximum number of rows.
• One number (CELL) — pick or type the table, choose the Column to total and How to combine the values.
• A list of dimension nodes (DRILL) — pick or type the dimension and the start node, choose All levels, Lowest level only or Down a number of levels, and optionally an Alternate hierarchy.

The Formula preview shows the result as you fill in the fields. Click Insert in active cell to put it in the selected cell, or Copy to paste it yourself. Choosing a table on the Tables tab or a dimension on the Dimensions tab fills in the matching field.

Related

• Build a Report With Custom Functions — a hierarchy down the side, values across the top
• Work With Tables and Dimensions — choose the project the functions read from
• Using Dimensions — main and alternate hierarchies, and how nodes roll up
• Design a Dimension Hierarchy — build the hierarchy your report rolls up through