Work With Tables and Dimensions
Once you’re signed in, the task pane lists your projects. You pick a project, then retrieve its tables and dimensions into the workbook.
Choose a Project
Section titled “Choose a Project”The header of the task pane shows your workspace and the project this workbook is linked to. Click the project name to open the project list, which shows every project you can open, in the same folders you see in PlaidCloud. Folders start collapsed; click one to open it, or type in Filter projects by name to narrow the list. If your workspace has only one project you can open, PlaidXL links the workbook to it for you, and the header says Linked to the project, with change beside it.
Click a project to select it. Its tables appear on the Tables tab and its dimensions on the Dimensions tab.
The Project Belongs to the Workbook
Section titled “The Project Belongs to the Workbook”The selected project is saved in the workbook, so each workbook has its own project. It’s the project PlaidXL custom functions and drill commands read from in that workbook. Open two workbooks and each reads from its own project, and when you reopen a workbook the task pane returns to its project. The project travels with the file to anyone who opens it and signs in with access to it.
A project belongs to one workspace. If you switch workspace, a workbook linked to a project in the old workspace has no project until you pick one. Tables and dimensions you retrieved from the old workspace stay in the workbook but aren’t refreshed or saved: Refresh All Tables says that tables or dimensions from another workspace were not refreshed. Retrieve them again in the new workspace to link them.
Retrieve a Table
Section titled “Retrieve a Table”- On the Tables tab, click a table. Use Filter tables by name to find it in a long list. The Rows and Columns next to each table tell you how big it is.
- Under Column, clear the check box of any column you don’t need. All columns start selected.
- To retrieve only some rows, select Advanced and build a filter. See Filter Rows.
- Under Select where to place this table, choose:
- Matching Worksheet — a worksheet named after the table, from cell A1. PlaidXL creates the sheet if it doesn’t exist.
- Current Selection — starting at the cell you’ve selected on the active sheet.
- Click Retrieve Table.
The data arrives as an Excel Table, named after the PlaidCloud table, so formulas, pivot tables and charts that refer to it follow the data as it grows or shrinks. Numbers, dates and times get a format that suits their data type, or the number format set on the column in PlaidCloud. Text columns are formatted as text, so a code such as 0012 keeps its leading zeros. The first time you retrieve a table, its header row is frozen and its columns are sized to fit.
If the cells the table would go into already hold something that isn’t a PlaidXL table, PlaidXL puts the table on a new sheet instead and tells you where. If the table would land on part of an Excel Table that PlaidXL doesn’t manage, nothing changes and the message says Move that Table or convert it to a range, then try again.
Retrieving a table that’s already on the sheet refreshes it in place: your own columns stay, and a source column you clear keeps its header with the cells below it emptied.
Whole-number IDs too large for Excel to hold exactly (over 9,007,199,254,740,991) and dates before 1 March 1900 arrive as text, so they show and save back exactly.
To name the worksheet, PlaidXL keeps only plain letters (A–Z), numbers, underscores and spaces, turns spaces into underscores, and cuts the name to Excel’s limit of 31 characters. A table named Q3 Revenue (EMEA) retrieves to the sheet Q3_Revenue_EMEA, and sales_2024 to sales_2024. Accented letters are dropped.
Large tables retrieve much faster in Excel on the web and in current desktop Excel. A table of 70,000 rows and 17 columns takes about 15 to 20 seconds on the web, and one of 143,000 rows and 77 columns takes about a minute where it once took over ten. Older versions of Excel still work, just without the speed-up. Columns are sized to fit the header and the first rows.
Text Longer Than an Excel Cell
Section titled “Text Longer Than an Excel Cell”An Excel cell holds at most 32,767 characters, so longer text shows cut. When a table has such text, the task pane says which columns of which table hold text longer than an Excel cell’s 32,767 characters, and that Excel shows it cut, so Save is off for this table. Saving would cut the text in PlaidCloud too, so Save to PlaidCloud refuses that table.
Tables Shown in Red
Section titled “Tables Shown in Red”A table shows in red, with the tooltip Too large for an Excel worksheet, when it has more than 1,048,575 rows or 16,384 columns, or when PlaidCloud can’t report its size, as for a table that has never been loaded. You can still retrieve it: rows that don’t fit on one worksheet continue on the next, named after the first with (2), (3) and so on. A table split across sheets can’t be saved back to PlaidCloud, so filter the rows with Advanced and clear columns until it fits if you plan to edit it.
Filter Rows
Section titled “Filter Rows”Select Advanced to open the filter builder. Each rule picks a column, a comparison such as equal, in, contains, begins with or is empty, and a value. Use AND groups, OR groups, or both to combine rules.
The filter compares values as text, so it matches exact values and text patterns. Ranges of numbers or dates aren’t available; to retrieve a range, filter on the values you need with in, or retrieve the table and filter it in Excel.
- Type values without quotes:
EMEA, not"EMEA". - For a comparison that takes several values, such as in, separate the values with a comma and a space:
EMEA, APAC, NA.
The filter applies only while Advanced is selected. PlaidXL remembers the filter and the columns you chose with each retrieved table, and uses them again when you refresh. A filter built only from equal and in rules joined by AND also decides which rows a save replaces.
Refresh All Tables
Section titled “Refresh All Tables”PlaidXL keeps a list of the tables and dimensions you’ve retrieved into the workbook, and refreshes them with the columns and filter you chose for each. The list is saved in the workbook, so refresh works for anyone who opens the file and signs in with access to the same projects.
- Refresh This Sheet, in the task pane, refreshes the tables on the active sheet.
- Refresh All Tables, in the task pane, refreshes every table and dimension in the workbook.
- The Refresh menu on the ribbon also refreshes a sheet, the selection or every table, and recalculates PlaidXL formulas. See Refresh.
While a refresh runs, the task pane shows its progress for each table — querying PlaidCloud, then the share of rows written — with a Cancel button. Three tables refresh at a time, and starting another refresh adds to the one already running. When it finishes, the pane lists each table with its row count, or the reason it failed. Click a table in the list to select it. A failed table doesn’t stop the others.
During Refresh All Tables, whether from the pane, the ribbon or Weave’s refresh of all tables, each sheet’s tab is yellow while that sheet refreshes. It returns to its previous colour, none or your own, when the sheet finishes, fails or is cancelled. If you save and close the workbook part-way, PlaidXL puts the tabs back the next time it starts in that workbook. Refreshing one sheet or a selection doesn’t colour tabs.
Cancel leaves the workbook as it was before the refresh: a table’s data is replaced only once all of its rows have arrived. It stops the query in PlaidCloud on StarRocks tables; on other engines, such as Databend and Snowflake, PlaidXL stops waiting but the query isn’t stopped in PlaidCloud. Excel’s calculation is paused while the data is written, and runs once at the end.
What a Refresh Keeps
Section titled “What a Refresh Keeps”A table refreshes into the same Excel Table, wherever it is now — a table retrieved to Current Selection refreshes in its own cells on its own sheet, even after you move or rename the Excel Table. The Table grows or shrinks to fit the new data, and rows and columns left over from a bigger earlier pull are cleared.
- Your own columns — columns you’ve added beside the data inside the Excel Table stay. A formula that’s the same in every row fills down to the new rows. Other values, such as notes, follow their row when the table has key columns in PlaidCloud; notes for rows that are gone are kept below the table. Without key columns, notes stay in their place by position.
- Your formatting — a number format you’ve set on a column is kept.
A refresh that would grow over other cells, or over an Excel Table that isn’t the table’s own, changes nothing and says Move what is there, then try again. If the Excel Table changed while the rows were arriving, the message says to try again.
If a table’s worksheet has been deleted, the table is skipped and PlaidXL offers Recreate sheet or Stop refreshing.
If Excel refuses part of the final write, the pane says the table was only partly written, with the reason, and to refresh it before you save it. Save to PlaidCloud refuses that table until a refresh succeeds.
Remove a Table From the Refresh List
Section titled “Remove a Table From the Refresh List”Under On this sheet, the task pane lists each table and dimension retrieved into the active sheet, with its row count and when it was last refreshed. Click Stop refreshing to take one off the refresh list. The data stays on the sheet, and the message offers Undo.
Retrieving a table over one already on the list replaces it in the list, so a table is refreshed once however often you retrieve it. Beside each table, Copy copies its Excel Table name for use in formulas.
Retrieve a Dimension
Section titled “Retrieve a Dimension”The Dimensions tab lists the project’s dimensions.
- Click a dimension. Use Filter dimensions by name to find it.
- Under Select where to place this dimension, choose Matching Worksheet or Current Selection.
- Click Retrieve Dimension.
PlaidXL writes the dimension’s nodes as one column, in hierarchy order. Dimensions have no column or row filters. Refresh All Tables refreshes them along with your tables.
To lay out a hierarchy with indentation and Excel outline grouping, or to start from a particular node or alternate hierarchy, use the drill commands or PLAIDXL.DRILL instead. To put a single member in a cell, click Pick member… on the Dimensions tab; see Pick a Member.
Save a Table to PlaidCloud
Section titled “Save a Table to PlaidCloud”A table you retrieved into the workbook can be saved back, replacing the PlaidCloud table’s rows with what is on the sheet.
- Go to the worksheet that holds the retrieved table. Under On this sheet, the task pane lists each table retrieved into it. If there’s none, the pane says to retrieve a table there first.
- Click Save to PlaidCloud next to the table, or click Save sheet to PlaidCloud… in the Write group of the ribbon (Ctrl+Alt+Shift+W, or ⌘⌥⇧W on Mac) to save every table on the sheet.
The save happens straight away, without a confirmation. The message says how many rows were saved, notes anything you should know, and offers Undo.
PlaidXL reads the table’s header row and the rows below it. The header must still match the columns you retrieved, and values are converted to each column’s data type. If a value can’t be converted, nothing is written and the rows that failed are listed. The column definitions in PlaidCloud stay as they are.
What Gets Saved
Section titled “What Gets Saved”- Empty cells are saved as empty.
- A number written with a comma, such as
1,5, isn’t read as 15. A whole number too large for the column is reported against its row, and the date 29 February 1900, which Excel invents, is refused. - Empty rows inside the retrieved rows are skipped. Below them, the table ends at the first empty row; rows further down aren’t saved, and the message names them.
- Columns you didn’t retrieve are emptied in PlaidCloud, and the message names them.
- If the table changed in PlaidCloud after you retrieved it, your save replaces it and the message says so. The restore point keeps what was there.
- If you saved a drill-through sheet and the members under its nodes have changed since you retrieved it, nothing is written: the message says Retrieve again before saving. Retrieving or refreshing replaces the sheet, so copy any edits you haven’t saved first.
Saves That Replace Only Some Rows
Section titled “Saves That Replace Only Some Rows”A save replaces only its own rows, and keeps the rest of the table, when:
- The table was retrieved with a filter built from equal and in rules joined by AND. Only the rows the filter covers are replaced; rows you’ve added that fall outside the filter are still saved, and the message says how many. A table retrieved with any other filter can’t be saved; retrieve it again with such a filter, or none.
- The sheet shows detail rows from Show detail rows. Only the rows behind that value are replaced.
- Your access to the table is limited to some rows. Only the rows you can see are replaced, so other people’s rows are kept. If the sheet holds rows outside your access, nothing is saved and the message lists them; remove those rows and save again.
Undo a Save
Section titled “Undo a Save”PlaidXL takes a restore point before it writes. Each save keeps its own restore point for 7 days, so Undo works for up to 7 days after each save, even if you saved again since. This applies to Lakehouse tables. If the write fails, the table is put back as it was and your changes stay in the sheet, unless someone else changed the table after the write began; then it is left alone and the message says so.
To undo a save, click Undo in the message, or Undo last write in the Write group of the ribbon. The table goes back to how it was before your save — for a save that replaced only some rows, just those rows. Refresh the sheet to see it. See Undo a Write. If a save can neither finish nor be put back, the message names the restore point so the table can be restored; Undo then also overwrites any changes made to the table since. A table that can’t take a restore point is still saved, and the message says the save can’t be undone.
Only the person and workspace that made a write can undo it.
Related
Section titled “Related”- Custom Functions — read tables and dimensions with formulas instead of the task pane, or build one on the Formulas tab
- The PlaidCloud Ribbon — refresh, drill and save from the ribbon
- Type Values Into PlaidCloud — write single values from a report
- Tables and Views — where the tables you retrieve come from
- Table Explorer — browse a table’s columns and rows in PlaidCloud