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

The Project section lists 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.

Click a project to select it. Its tables appear on the Tables tab and its dimensions on the Dimensions tab.

The selected project is also the project PlaidXL custom functions and drill commands read from. The selection applies to every workbook you have open, not just this one.

Set a Default Project

To have a workbook select the same project every time you open its task pane, select the project and click Set Default. The default is saved in the workbook, so it travels with the file to anyone who opens it. Clear Default removes it.

Retrieve a Table

1. 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.
2. Under Column, clear the check box of any column you don’t need. All columns start selected.
3. To retrieve only some rows, select Advanced and build a filter. See Filter Rows.
4. Under Select where to place this table, choose:
   • Matching Worksheet — a worksheet named after the table. PlaidXL creates the sheet if it doesn’t exist, and writes the table from cell A1 if it does.
   • Current Selection — starting at the cell you’ve selected on the active sheet.
5. Click Retrieve Table.

The data arrives with a header row, ready to work with. The header row is bold and shaded, columns are sized to fit, and filters are turned on. A table retrieved to Matching Worksheet also gets:

• A frozen header row, so it stays visible as you scroll.
• A named range over the data, so formulas that use it follow the data as it grows on refresh. Its name is the worksheet name with an underscore in front: a table named Sample data lands on the sheet Sample_data, with the named range _Sample_data.

To name the worksheet, PlaidXL cuts the table name to Excel’s limit of 31 characters, keeps only plain letters (A–Z), numbers and spaces, and turns spaces into underscores. A table named Q3 Revenue (EMEA) retrieves to the sheet Q3_Revenue_EMEA. Underscores and accented letters are dropped too, so sales_2024 retrieves to the sheet sales2024.

Tip

Wide tables are slow to load into Excel. Clearing the columns you don’t need is the quickest way to make a large retrieve faster.

Tables Shown in Red

A table shows in red when it’s too big for a worksheet — more than 1,048,576 rows or 16,384 columns — or when PlaidCloud can’t report its size, as for a table that has never been loaded. Filter the rows with Advanced and clear columns until it fits. PlaidXL retrieves at most 1,048,575 rows plus the header row, and stops there without a warning.

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.

Refresh All Tables

Refresh All Tables — in the task pane, or on the ribbon under PlaidCloud → PlaidXL Controls — pulls current data from PlaidCloud for every table you’ve retrieved into the workbook, with the columns and filter you chose for each.

While it runs, each table’s worksheet tab turns yellow. It turns green when that table is up to date. Formulas that use the data recalculate as usual; refresh pivot tables built on it with Excel’s Data → Refresh All.

PlaidXL saves the list of retrieved tables in the workbook, so Refresh All Tables works for anyone who opens the file and signs in with access to the same projects.

Each Retrieve Table adds the table to the refresh list, and there’s no way to remove one. Retrieve each table once, then use Refresh All Tables to update it — retrieving the same table again makes every refresh pull it twice.

Caution

A table retrieved to Current Selection refreshes into the same cells on whichever sheet is active when you click Refresh All Tables, overwriting what’s there. Retrieve tables you plan to refresh to Matching Worksheet; they always refresh onto their own sheet.

A refresh writes over the old data without clearing it first. If a table now has fewer rows or columns than before, the extra rows or columns from the earlier pull stay on the sheet. Clear the sheet’s contents before you refresh a table that may have shrunk.

Retrieve a Dimension

The Dimensions tab lists the project’s dimensions.

1. Click a dimension. Use Filter dimensions by name to find it.
2. Under Select where to place this dimension, choose Matching Worksheet or Current Selection.
3. Click Retrieve Dimension.

PlaidXL writes the dimension’s nodes as one column, in hierarchy order. Dimensions have no column or row filters, and Refresh All Tables doesn’t refresh them — retrieve the dimension again to pick up changes.

To lay out a hierarchy with indentation and Excel outline grouping, or to start from a particular node or alternate hierarchy, use PLAIDXL.DIMENSION.GET_DIMENSION or the drill commands instead.

Changing Data

PlaidXL reads from PlaidCloud and never writes back. You can edit, sort and add formulas to a retrieved sheet freely, and nothing in the project changes. A refresh writes the current PlaidCloud data over the table’s range, so keep your own calculations on a separate sheet.

To change the data itself, update it in PlaidCloud — for example, with a workflow or the Data Editor.

Related

• Custom Functions — read tables and dimensions with formulas instead of the task pane
• The PlaidCloud Ribbon — refresh and drill from the ribbon
• Tables and Views — where the tables you retrieve come from
• Table Explorer — browse a table’s columns and rows in PlaidCloud