Tabulys guide

Formulas & functions

Use the formula bar, autocomplete, AutoSum and named ranges.

Writing formulas

  1. Select a cell and type = to begin a formula.
  2. Click a cell or type its reference, such as A1, to use its value.
  3. Combine references with operators like + - * / to calculate.
  4. Reference a range with a colon, for example A1:A10.
  5. Press Enter to confirm; the cell shows the result while the formula bar shows the formula.

Tip Use $ to fix a reference, such as $A$1, so it does not shift when the formula is copied or filled.

Point to cells and ranges in a formula

While you write a formula, you can build a reference by pointing at it on the sheet instead of typing its address, as in Excel.

  1. Type = to start a formula, then an operator, a comma or an opening parenthesis: that is where a reference can go.
  2. Click a cell to write its reference, or drag across cells to write a range; clicking again replaces the reference you just pointed to.
  3. Hold Shift and click to extend the range, or hold Ctrl and click to add another reference after a comma; click a column or row heading to point to whole columns or rows.
  4. With the keyboard, press the arrow keys after an operator to point to the neighbouring cell and Shift+arrow to extend; press F2 to switch between moving the cursor in the text and pointing to cells.
  5. To use a range on another sheet, click its tab and point to the cells: the reference carries the sheet name, and Enter brings you back to the formula's cell.

Tip Each reference gets its own colour, and the same colour outlines its range on the sheet. Press F4 to cycle a reference between relative and absolute ($A$1). The status bar reads Point while you are pointing.

References to sheets, whole columns and other workbooks

A formula can refer to cells on other sheets and in other workbooks, to whole columns and rows and to lists of values written in braces, using the same notation as Excel. If a formula cannot be read, Tabulys does not accept it and tells you where to fix it.

  1. For another sheet, type its name and an exclamation mark: =Sales!B2. If the name contains spaces or symbols it goes in single quotes, ='Sales 2024'!B2; Tabulys adds or removes the quotes itself when you confirm.
  2. For a whole column or row, type only the letters or only the numbers: =SUM(B:B), =SUM(2:2), =SUM(B:D). Tabulys reads only the part of the sheet that holds data, so they stay fast.
  3. For the same cell on several consecutive sheets, type the first and the last sheet separated by a colon: =SUM(January:March!B2). Every sheet between the two counts, including sheets moved there later.
  4. For a list of values, type them in braces, with a comma between columns and a semicolon between rows: =SUM({1,2;3,4}).
  5. A reference to another workbook, as in an Excel file, shows the file's path and is worth what the file has stored: Tabulys does not update it from the network.

Tip If you press Enter on a formula that cannot be read, for example with an unclosed parenthesis, the editor stays open and a message says what is missing and at which character; the part to fix is already selected.

Formula suggestions

While editing a formula, Tabulys shows Excel-style autocomplete of function names and a ScreenTip of the current function's signature.

  1. Type = followed by the start of a function name to open the autocomplete list.
  2. Press the up and down arrows to move through the matching names.
  3. Press Enter, Tab or click to insert the function with an open parenthesis and the caret inside.
  4. Continue typing the arguments; the ScreenTip shows the signature with the current argument in bold.
  5. Press Esc to close the suggestion list without accepting it.

AutoSum

  1. Select the cell below a column of numbers, or to the right of a row.
  2. Press Alt+= or click AutoSum.
  3. Check the range Tabulys guessed in the editable =SUM(range) formula.
  4. Press Enter to confirm the total.
  5. Use the AutoSum arrow to choose Average, Count Numbers, Max or Min instead.

Tip Select a range first and AutoSum fills the totals below or to the right in a single step.

Defined names

Give a cell or range a name and use it in formulas, for example =SUM(Taxes).

  1. Select the cells to name.
  2. Type the name into the Name Box and press Enter to define it on the selection.
  3. Use the name in a formula in place of its references.
  4. Open the Name Manager with Ctrl+F3 to create, edit or delete names.
  5. Press Ctrl+Shift+F3 to create names from the labels in the first row of a selection.

Tip Names resolve with sheet-local scope first, then global, just like Excel. If a name is refused, a message says why; a reference written without a sheet takes the active sheet.

Show formulas

  1. Click Show Formulas, or press Ctrl+` (the grave accent key).
  2. Read each formula's text in its cell instead of the calculated result: it applies to the sheet you are on, and the button stays pressed while that sheet shows formulas.
  3. Toggle it off the same way to return to showing results.

Tip Show Formulas is a view setting only; it never changes your data or the PDF export. Each sheet keeps its own and the file saves it, as in Excel: that is why turning it on or off marks the workbook as modified.

Pick a range on the grid (RefEdit)

Reference fields in dialogs let you select a range directly on the sheet instead of typing the address.

  1. Open a dialog with a reference field, such as Goal Seek, Consolidate, Data Validation or Name Manager.
  2. Click the small RefEdit button next to the reference box; the dialog collapses to a thin bar.
  3. Select a range on the grid — switch sheets if needed, or hold Ctrl to add more areas.
  4. The absolute address is written into the field as you select, qualified with the sheet name when on another sheet.
  5. Click the restore button on the bar, or press Enter or Esc, to expand the dialog with the captured reference.

Tip You can still type the reference by hand; the RefEdit button is just a faster alternative.

Calculation model: 1904 dates, iterative calculation and precision

Tabulys honors the workbook calculation options saved in the file: the 1904 date system, iterative calculation for circular references, recalculate-on-open and precision as displayed.

  1. A file saved with the 1904 date system (a legacy Mac option) shows its dates correctly, not shifted by 1462 days: every date function and every date-formatted cell uses the workbook epoch.
  2. To let a circular reference resolve, open Calculation Options and turn on Enable Iterative Calculation; Tabulys repeats the calculation until the values converge or the iteration limit is reached.
  3. A file that requests a full recalculation on open is fully recalculated when you open it, ignoring the cached values.
  4. When a workbook uses precision as displayed, a formula result is rounded to the number of decimals its number format shows before other cells use it.

Tip Iterative calculation and the date system round-trip with the file; keep iterative calculation off unless you really need circular references.

Array formulas (Ctrl+Shift+Enter)

An array formula is one formula that fills a whole range: each cell of the range shows one part of the result, exactly as in Excel.

  1. Select the range the result should fill, for example B1:B3, and type the formula, for example =A1:A3*$D$1.
  2. Press Ctrl+Shift+Enter instead of Enter: the formula is entered in every cell of the selection as one array formula. With a single cell selected you get a one-cell array.
  3. Select any cell of the array: the formula bar shows the formula between braces, {=A1:A3*$D$1}. Don't type the braces yourself: Ctrl+Shift+Enter adds them.
  4. To change the formula, select a cell of the array, press F2, edit it and press Ctrl+Shift+Enter again: the whole array is entered again with the new formula.
  5. To remove the array, select all of its cells and press Delete.

Tip You can't change part of an array: typing, deleting, pasting or filling over only some of its cells is refused with the message “You can't change part of an array.”, because the cells share one formula and one result. Copying part of an array elsewhere gives a one-cell array formula, as in Excel.

Spilled results (dynamic arrays)

Functions such as SORT, UNIQUE, FILTER and SEQUENCE return several values: the formula lives in one cell and its results spill into the cells below and to the right.

  1. Open a workbook from Excel that uses these functions, or type one, for example =SORT(A1:A6): the values fill as many cells as the result needs.
  2. Select a cell of the spilled range: the formula bar shows the formula of the first cell in grey, because that cell only shows a value of the result; F2 opens it empty.
  3. Change the source data: the result is recalculated and the spilled range grows or shrinks by itself.
  4. To use the whole result elsewhere, write # after its first cell, for example =SUM(C1#).

Tip If the spilled range would cover cells that already contain something, the formula shows #SPILL!; empty those cells and the values come back.

The @ in formulas from Excel files

In a formula written for older versions of Excel, a range given where a single value is expected means the value on the formula's own row or column. Tabulys shows this with @, exactly as Excel 365 does.

  1. Open a workbook written in Excel and select a formula such as =@A1:A6*10: the @ means that only the cell of A1:A6 on the same row as the formula is used.
  2. Leave the @ where it is to keep the result the file had in Excel; if you remove it, the formula returns the whole range as a spilled result.
  3. Save the workbook: the formula is written back in the form Excel expects, and Excel reopens it with the same @ and the same result.

Tip Formulas you type yourself never get an @ added: Tabulys shows it only where Excel would show it.

Excel files keep their formulas

Formulas in workbooks made with Excel are shown, recalculated and saved the way Excel writes them: formulas filled with the fill handle, newer functions such as XLOOKUP, IFS, LET and LAMBDA, array formulas and spilled results.

  1. Open an .xlsx file made with Excel: every cell shows its own formula in the formula bar, including the cells that were filled with the fill handle.
  2. Change a value the formulas depend on: every formula that uses it is recalculated, with the same result Excel would give.
  3. Newer Excel functions (XLOOKUP, IFS, XMATCH, TEXTJOIN, LET, LAMBDA…) are calculated normally, also in defined names, conditional formatting, data validation and table columns.
  4. Save the workbook: the formulas are written back in Excel's own form, and Excel opens the file without asking to repair it.

Tip In the file Excel writes some functions with a prefix, such as _xlfn.XLOOKUP; Tabulys shows the plain name, XLOOKUP, and puts the prefix back when you save.

Back to the guide

Productivity, made in Italy.

Bring the whole suite to your team — simple, secure and entirely yours.