Sheets and formulas

A table block is a sheet. Someone looking at rows wants to do four things: sort them, total them, format a column, and add a column of their own. Most people already do all four in Excel after exporting, which is where a report stops being sanda's numbers and becomes a copy in a downloads folder. The sheet lets you do them on the report, over live rows, and the export is there for the cases that genuinely belong elsewhere.

Two rules keep a report a report rather than a spreadsheet:

• Nothing is editable. A cell holds what the warehouse said. A column of your own is a formula, never a number typed over one.
• A sheet stores instructions, never rows. Your sort, formats, totals, hidden columns and formulas are saved on the block and applied to whatever the block returns next time. A margin column you define in June is still a margin column in July, over July's rows.

Sort, select and read

• Sort by clicking a column header. Click again to reverse, and a third time to clear. Empty cells sort last in either direction.
• Select a cell by clicking it. Drag, or shift-click, to select a range. The footer then shows how many cells are selected and, for the numeric ones, their sum and average, where Excel puts them.
• The footer also says how many rows and columns the table has. When the block's row limit cut the result short it reads, for example, 15 rows of more.

People who can only read a report, whether through a shared link, the portal or a read-only share, can sort and select too. Their changes last for the visit and are not saved, and they cannot format, total or add columns.

Format a column

Hover a column header and press the three dots to open its menu. format offers:

A value that starts with a zero and is five or more digits long, such as a code 00123, is never treated as a number under any format, so its zeros survive. The console does not currently let you choose the number of decimals or a currency code. currency shows a dollar sign and two decimals.

Total a column

The total menu on a column adds a totals row to the foot of the table. Choose sum, average, minimum, maximum or count (which counts the cells that are not empty), or none to remove it. The row is labelled total in the first column when that column has no total of its own.

Totals cover the rows the block returned, the same as everything else in the sheet. If the footer says of more, raise the block's row limit before trusting a total.

Hide a column

The three-dot menu has hide, and show all once anything is hidden. A hidden column is not drawn, and it is left out of the csv download.

A formula written before you hid a column keeps reading it on the report. The formula editor only accepts columns that are showing, though. In the Excel download a hidden column is still written, hidden the way Excel hides one, so a formula that reads it keeps calculating there too. Unhide it in Excel to see it.

Add a column of your own

A column in brackets means this row's value. Inside SUM, AVERAGE, MIN, MAX, COUNT or COUNTA the same reference means the whole column, so [net revenue] / SUM([net revenue]) is each row's share of the total in one line.

A formula can read the columns before it, including formula columns you added earlier. You can add up to 12 formula columns to one table, and hidden formula columns count toward that. When a table has 12, the formula button turns off and its tooltip says why. Remove one to add another.

To change a formula, open its column's menu and press edit formula. The editor's bin icon removes the column.

When a formula cannot be worked out for a row, the cell shows an error word such as #DIV/0! and the rest of the table carries on. A formula that cannot be read at all fills its column with #NAME?, marks the header with a red ! and says why under the table. The full list is in the formula reference.

The language is Excel's, on purpose: the same operators, the same function names, the same meaning. Everything you can write is in the formula reference.

Take it to Excel

The footer has two download buttons. Both export the table as you see it: the sort applied and formula results included. The csv leaves hidden columns out. The workbook keeps them as hidden columns, so every formula still has the cells it reads.

The workbook has a bold header row that stays put as you scroll, a filter on each column, and your formats carried across as Excel number formats. A totals row, if you set one, is written as Excel formulas over the column. The sheet is named after the block's title, cut to Excel's 31-character limit. Numbers the warehouse sent as text, such as 2,000, arrive as numbers.

The file is named after the block's title. The compact format arrives in Excel as a whole number with thousands separators.

A CSV holds the raw values rather than the formatted ones, with a total row at the end if you set totals. Any text cell that begins with =, +, - or @ is written with a leading apostrophe, so a spreadsheet reads it as text and never runs it as a formula.

Anyone who can see a table can download it, including people holding a shared link and portal readers, who get the rows their own data rules allow. Bear that in mind before you share a report whose tables hold detail.

What the sheet does not do

There are no cell references such as A1, no lookups such as VLOOKUP, no conditional sums such as SUMIF, no date functions and no running totals. A formula sees the rows of its own table and nothing else. If you need a shaped number more than once, define it as a metric in the semantic fluid, so every report gets it.