Spreadsheets and whiteboards
Spreadsheets and whiteboards
Who can do this: Edit on the project, or on the folder you create in, to add a spreadsheet or a whiteboard. Read to open one. Edit on the item to change it. Turning on a whiteboard's public link needs more: you must be the item's creator or a project Owner. See the .
What it's for
A spreadsheet is a free-form grid for calculations and quick models, in an Excel-compatible workbook that several people edit at once. A whiteboard is an open canvas for diagrams, flows and sticky notes. People work on it together, and it can show your spreadsheets and documents inside it. Both sit in the navigator next to your tables and documents.
Spreadsheet or data table?
You need | Use |
|---|---|
Many records of one kind, with a type per field, checks on entry, forms, the portal, automations or access to some rows only | |
Calculations laid out cell by cell, a one-off model, figures you format by hand | A spreadsheet |
Figures on a dashboard that customers or anyone with a link will see | A data table. Spreadsheet panels are refused on those pages |
A spreadsheet cell holds anything. There are no column types, no required fields and no rows to protect: rights apply to the whole spreadsheet. A spreadsheet can read from your tables with IMPORTRANGE, DBLOOKUP and QUERY (below), so you can keep the records in a table and build the calculation beside it.
Before you start
No administrator setup is needed for a spreadsheet.
A whiteboard that holds pictures uses the installation's file storage.
Needs your administrator: if a picture dropped on a board fails to upload, ask your administrator to check file storage. See .
Steps: create and use a spreadsheet
In the navigator open the arrow beside New Document and choose New spreadsheet. On a folder's menu the entry is New Spreadsheet.
Type a name and choose Create. The spreadsheet opens.
Look at the bar above the grid. It shows Live when your changes are being shared, and Reconnecting… when the connection dropped. The tabs Start, Insert, Formulas, Data and View hold the tools. The buttons Refresh references and Export to XLSX sit at the right.
Pivot tables: Data tab, Pivot table. The panel Pivot tables has New pivot from selection.
Sparklines: Data tab, Sparkline. The panel Sparklines has New sparkline from selection.
Row and column groups: right-click a row or column header and choose Group rows or Group columns.
To find out what a function does, start typing it in a cell. The formula helper shows its description and parameters. The Formulas tab lists the functions by group, with a search box. This page does not list them.
Several people can edit at the same time. Changes appear on the other screens as they happen.
Bring in data from your tables
On the Formulas tab open Data tables and choose one of three wizards. Click a cell first.
Entry | Wizard | What it does |
|---|---|---|
IMPORTRANGE — import columns | Insert IMPORTRANGE | Fills the sheet with the columns you pick from a data table in the same project |
DBLOOKUP — look up a value | Insert DBLOOKUP | Finds one row by a key and returns one column from it |
QUERY — query a range | Insert QUERY | Runs a |
Pick the table under Data table, then the columns (Columns, or Column to return and Column to match on with Value to look up). The wizard shows the formula under Formula; press Insert.
The sheet reads each table with the rights of the person who has the spreadsheet open. A cell can show #REF! (the table is missing, is not a data table, or is in another project), #NAME? (the column was not found), #PERM! (you have no right to read it) or #ERROR! (any other failure). IMPORTRANGE returns at most 10,000 rows. The sheet checks its sources about every 30 seconds. Refresh references checks them now.
Export to Excel
Choose Export to XLSX. The file takes the spreadsheet's name. It keeps values, formulas, formatting, merged cells, widths and heights, frozen panes, tables and sparklines. IMPORTRANGE and QUERY cells are written as their last values. A DBLOOKUP cell stays a formula, which Excel cannot calculate.
There is no XLSX import into a spreadsheet. To bring an XLSX file in as records, import it into a data table ().
Use a spreadsheet in a dashboard
A chart or metric panel can read a range, and a Spreadsheet panel can show whole sheets. In the panel's configuration choose the spreadsheet under Data source. For a chart or metric pick Named range or Custom (A1) under Range (for example Sheet1!A1:C20) and set First row is a header. The range of a chart or metric can hold at most 50,000 cells. The Spreadsheet panel shows up to 300 by 60 cells per sheet, read-only. The dashboard reads the last saved copy of the sheet, which the editor refreshes about every 15 seconds and when you leave it. Viewers need Read on the spreadsheet as well as the dashboard.
On the portal and on public pages every panel that reads a spreadsheet shows Not available. See .
Share a spreadsheet
Open the item's menu in the navigator and choose Manage access… for your team, or External users access… for portal users. The menu has no Publish entry for a spreadsheet, and it has no public page. See .
Steps: create and use a whiteboard
Open the arrow beside New Document and choose New whiteboard. On a folder's menu the entry is New Whiteboard. Type a name and choose Create.
Work on the canvas. The toolbar offers selection and hand tools, a pen, arrows (straight, bent, Bezier or curved), shapes, text, comments, sticky notes, a timer, a poll, images, SVG, frames and links. A menu of charts and diagrams adds org trees, bar, line and donut charts, heatmaps, funnels, tables, to-do lists and more. The Import / Export menu brings in Mermaid, Visio and SVG, copies the canvas as PNG, and downloads it as a PNG or a Visio drawing.
Use frames to split a board into screens. In the Frame tool choose Add Root Frame for a new top-level screen, or Add Embedded Frame to draw a frame on the screen you are on. Once a board has more than one frame, a Screens list lets you jump between them, rename, duplicate or delete.
To show your own content, open the charts and diagrams menu and choose Spreadsheet or Document. The dialog Insert spreadsheet (or Insert document) lets you pick one from the project, or press Create new spreadsheet (or Create new document) to make a new one under the whiteboard. The board shows a read-only preview with its title and an Open link that opens the item in a new tab.
Other people on the board appear as cursors and in a list of who is present. Changes are saved as you go.
The item's menu also has Open full-screen (a page with only the board and the assistant) and Open in a new window.
The assistant
With a whiteboard open, the project's assistant chat can read the board and change it for you: add sticky notes, shapes, text, arrows, frames, org trees and charts, rearrange the layout, and create a spreadsheet or document under the board. The changes land on the board you have open, through your own session, and follow your rights on it. Using the assistant needs the Chat right. See .
Share a whiteboard and publish a view
In the navigator open the whiteboard's menu and choose Share board…. The dialog is also in the account menu on the board's toolbar.
Under Share link switch on Anyone with the link can view. Use Copy link or Open.
Under People with access, Manage access and External users open the usual access dialogs for your team and your portal users.
Anyone with the link sees a read-only copy with the board's name and a Read-only badge, and needs no account. They see the board as of its last save, not live. Embedded spreadsheets and documents are not included. The link is secret and not listed in your public navigation.
Careful: turning the link on also switches on the project's own Published switch if it was off. The dialog does not tell you. Turning the link off does not switch the project back. Pictures on a board are stored like pictures pasted into documents: their addresses open for anyone who has them. See .
How to check it worked
Spreadsheet: open it in two browsers. A change in one shows in the other, and both bars say Live. Export to XLSX gives a file that opens in Excel.
Table formulas: after inserting IMPORTRANGE, the columns you picked fill in below the cell.
Whiteboard: open the link in a private window. You see the board with the Read-only badge. If you see This board is not available, the link was switched off or the board was archived.
Undo / what can go wrong
The grid is not visibly locked for people with only Read. They can type, but their changes are not shared or saved. Give them Edit if they should change it.
Keep a spreadsheet under 250,000 filled cells. Above that the full save is refused and the editor shows no message.
A dashboard reads the last saved copy of a spreadsheet, so its figures can trail the sheet by about 15 seconds.
Archiving a whiteboard also takes its public link offline. Switching the link off in Share board… does the same without archiving.
Spreadsheet tools are translated into English, Russian and Spanish. Portuguese shows English. The product's own spreadsheet menus (data tables, pivots, sparklines, groups) are English and Russian only.