Use formulas in a table
Add up, multiply and compare values in a Table content block with spreadsheet formulas, format the results, and link to other tables, spreadsheets and pricing tables.
A cell in a Table content block can hold a formula, just like a spreadsheet. The table uses the same formulas as the Spreadsheet block, with more than 500 functions such as SUM, ROUND and IF.
Add a formula
Total a column
3 steps
Select the cell
Click the cell where the total should go.
Type the formula
Type
=and then the formula, such as=SUM(D2:D6). Function suggestions appear as you type, with help for each argument. Press F1 to show or hide the help.Save it
Press Enter. The cell shows the result.
While you edit a formula:
Letters appear above the columns and numbers beside the rows, so you can see each cell's name.
Each reference in the formula has its own colour, and the cells it points to are outlined in that colour.
Click a cell, or drag over several, to put its reference into the formula. You can also use the arrow keys.
Press F4 with your caret on a reference to switch between
B2,$B$2,B$2and$B2.
Row 1 is the first row of the table, including heading rows.
To change a formula, select the cell and press Enter or F2. To type text that starts with =, start it with an apostrophe, like '=not a formula.
Formulas keep pointing at the right cells
Insert, delete, move or sort rows and columns, and your formulas still point at the same cells. If you delete a cell a formula uses, the formula shows #REF!.
Format numbers
Select the cells, then choose a format from the Number format button on the toolbar:
Number, Currency or Percent. Currency uses your document's currency symbol.
Date, in your document's date format.
More decimal places and Fewer decimal places.
Custom… for any Excel format, such as
#,##0.00or0%.
When you type a number into a formatted cell, it's shown in that format and stays a number that formulas can use.
Use a value from another table
Every table has a name. New tables are called Table 1, Table 2 and so on.
To give a table a clearer name, open the Table menu, choose Table Settings, and type it in Table name. A name can't look like a cell, such as B2, or be just a number.
In another table, type the name, an exclamation mark and the cell, such as =Summary!B2. Put quotes around a name with spaces: ='Table 2'!B2. As you type, the table suggests the names of other tables and spreadsheets in the document.
Use a value from a spreadsheet
Put the spreadsheet block's title in square brackets, then the sheet and the cell, such as =[Costs]Sheet1!B4.
Show a table value somewhere else
Select one cell in the table.
Right-click and choose Copy cell reference.
Paste it into a text block or another table. In a pricing table, right-click a cell such as the price or quantity and choose Paste cell reference.
The pasted value follows the table cell when it changes.
Renaming and loops
Renaming a table or a spreadsheet doesn't break formulas that use it. They show the new name.
If a formula ends up using its own result, directly or through other cells, the cell shows #CIRCULAR!.
What your customer sees
Your customer sees the results, not the formulas, in the viewer and the PDF.
Related guides
- Format table text and use keyboard shortcutsFormat part of a cell's text, fill cells by dragging, hide rows and columns, and move around a Table content block with Excel-style keys.Smart Tables
- Sort, filter and highlight table rowsSort the rows of a Table content block, filter out rows your customer doesn't need to see, and colour cells by their values with conditional formatting.Smart Tables
- Using Formulas for Calculations in Spreadsheet BlocksLearn how to use formulas like sum, average, multiplication, and more in QuoteCloud Spreadsheet Blocks to automate calculations.