Drill, Pick Members and Explain Numbers
The Drill commands work on a single cell. They turn a node into a live PLAIDXL.DRILL formula and then edit that formula for you, so the layout stays connected to the dimension and updates when it changes. Each command is on the PlaidCloud ribbon tab, the PlaidXL right-click menu, the PlaidXL Cell tab and a keyboard shortcut.
Drill Down From a Cell
Section titled “Drill Down From a Cell”- Type a node of a dimension in a cell — an ID such as
4000or an alias such asRevenue— and select it. - Click Drill down, then One level, To lowest level or All levels.
The cell becomes a PLAIDXL.DRILL formula that lists the node and the nodes beneath it down the column, indented by generation and grouped in an Excel outline so you can collapse each level.
PlaidXL works out the dimension from the nearest dimension name above the cell. Without one, it uses the dimension you last drilled on the sheet. You can also select two cells side by side — a dimension name and a node — and the drill goes in the node’s cell.
To start from the top of a dimension, select a cell holding the dimension’s name, or the empty cell under it: the drill goes below the name and starts at the dimension’s top nodes.
Drill Within a Drill
Section titled “Drill Within a Drill”Use the same commands on a row of an existing drill:
- One level opens that row’s children in place, leaving the rest of the list as it is.
- To lowest level and All levels start the drill again from that row’s node.
- Drill up closes the nearest open node above the row. When there’s none to close, it moves the drill’s start node up to its parent.
On a cell that holds a node but no drill, Drill up replaces the node with its parent.
Drill From a Report Value
Section titled “Drill From a Report Value”Select a value in a report — a CELL or CELLS formula — and Drill down one level opens the row header on that value’s row in place, so the report gains the next level of detail beneath it. Drill up closes it again. When the row heading isn’t part of a drill, or a drill in place would lose rows of the report, as All levels would, PlaidXL opens Custom drill filled in for you instead.
Overwrites and Undo
Section titled “Overwrites and Undo”A drill never asks before it writes. If the new rows need cells that hold something, PlaidXL clears them and says how many in its message. Where your version of Excel can group the edit, a drill is one undo step and Ctrl+Z puts everything back; in older versions it can take more than one. When the task pane is open, the message also offers Undo, which restores only the cells still holding what the drill wrote and keeps anything you’ve edited since.
Drill commands edit drills that run down a column. A DRILL that runs across a row — such as the column headings of a report — is changed in its formula.
Keep Only and Remove Only
Section titled “Keep Only and Remove Only”Select one or more rows of a drill, across several drills if you like, then:
- Remove only takes the selected nodes, and everything under them, out of the drill. Removing the drill’s first row clears the drill.
- Keep only keeps just the selected nodes. One node becomes a drill of that node alone; several keep their place under their nearest shared parent.
Remove only adds the nodes to the drill’s exclude argument. Keep only rewrites the drill’s start node, key and exclude. Either way the result stays live. PlaidXL reads up to 5,000 selected cells. A very long list of removed nodes moves to a hidden sheet named PlaidXL Lists, which the formula refers to.
Custom Drill
Section titled “Custom Drill”Custom drill… opens the Formulas tab on A list of dimension nodes, filled in from the selected cell:
- Start from node — leave it blank to start at the top of the dimension.
- How far to drill — All levels, Lowest level only, Down a number of levels, From level one level to another, All but the last number of levels, or Advanced (type the drill key), such as
[1:3]. - Alternate hierarchy (optional).
- Exclude nodes (optional, one id per line) and Open nodes (optional, one id per line) — nodes to leave out, and nodes whose children to show as well.
Use selected cell beside a field points it at the cell or range you’ve selected, so the drill follows that cell. Rows previews the first 10 nodes and the total; click Show all to see more. Click Insert in active cell to change the drill you started from, or put a new one in the active cell, and Copy to paste the formula yourself.
Pick a Member
Section titled “Pick a Member”Pick member… opens the member picker on the selected cell’s dimension, already searching for the cell’s text. You can also open it from the Dimensions tab.
- Search id or alias searches every hierarchy of the dimension, and shows each match’s path.
- Choose a hierarchy from the list when the dimension has more than one.
- Browse the tree with the arrow keys, check nodes with Space, and press Enter to insert.
Insert writes the node’s ID into the active cell and moves down a row; with nodes checked, it fills them down from the active cell. Fill down and Fill right write the checked nodes down or across. Double-click a node to insert it. If the active cell holds a drill, the node you insert becomes the drill’s start node.
Inserting writes over what’s there and says how many filled cells it replaced. The message offers Undo, and where your version of Excel can group the edit, Ctrl+Z undoes it in one step.
Add a Dropdown of Members
Section titled “Add a Dropdown of Members”Select the cells that should offer a choice — a period column, say — then in the member picker point at a node and click Add dropdown. Each selected cell gets a dropdown of that node’s children. The list is a drill on the hidden PlaidXL Lists sheet, so it updates when the dimension changes. Cells keep their values, and a value that isn’t in the list is still allowed.
Dropdowns that follow a live list need Excel for Microsoft 365 or Excel 2024. Where your version of Excel can group the edit, Ctrl+Z removes the dropdown in one step.
Explain a Number
Section titled “Explain a Number”Explain this number opens the Explain tab with a card for each selected PlaidXL cell. It explains CELL, CELLS, DRILL, TABLE.GET_TABLE, METRIC and METRIC_BREAKDOWN, including one inside another formula such as IFERROR. Select a cell that isn’t a PlaidXL formula and PlaidXL explains the PlaidXL cells it’s calculated from.
Each card shows:
- What the cell calculates — for example, the sum of a column in a table, filtered to each node, with the number of leaf members under each.
- The value calculated now, and whether it matches the cell. If it doesn’t, Recalculate brings the cell up to date.
- Where this comes from — when the table’s data was last updated, how many rows it has, and the workflow step that built it, each linking to PlaidCloud.
- Open table in PlaidCloud, Drill through to rows and Copy explanation.
A filter node links to the node in PlaidCloud, and its Drill button adds that node’s children as rows. When a cell shows an error, the card says why and, where it can, offers the fix — Choose a project, or Pick a member with the closest matches.
When you explain many cells at once, the cards load 20 at a time, with a button at the end to show the rest.
Show Detail Rows
Section titled “Show Detail Rows”Show detail rows puts the table rows behind a CELL or CELLS value on a new sheet named after the nodes and the table. Select a cell that’s calculated from a PlaidXL value and PlaidXL uses that value.
- Row 1 describes the value and links back to it.
- Row 2 checks that the rows add up to the value: it shows ties or by how much they differ.
- The rows start in row 3, as an Excel Table.
While the rows load, the task pane shows progress with Cancel. Running Show detail rows on the same value again refreshes that sheet, and Refresh all tables keeps it current. A detail sheet can be saved back to PlaidCloud; the save replaces only the rows behind that value.
Related
Section titled “Related”- PlaidXL Custom Functions — the
DRILLarguments the commands edit - Build a Report — reports whose rows and columns are drills
- The PlaidCloud Ribbon — the commands and their shortcuts