Formulas and functions

What it is

Calculations right in the spreadsheet cells: formulas with functions, as in Excel and Google Sheets, hints while typing, function help, the f(x) list of functions, named ranges and the workbook’s own functions. Formulas recalculate by themselves when the data they depend on changes.

How to use it

Start typing in a cell, or in the formula bar below the toolbar, with the “=” sign: =B2*C2, =SUM(D2:D4). Enter saves and moves to the cell below, Tab moves to the right, and Escape cancels the input. In the formula bar, while you type, there are the buttons Cancel input and Apply on the right.
References: A1, $A$1, ranges A1:B10, whole columns A:A and rows 1:1, another sheet Лист2!A1 or 'Продажи Q1'!B2. While you type a formula, a click on a cell or a drag across the sheet inserts the address at the cursor; a merged cell is inserted whole. Up to four references are highlighted in the formula in different colours, and with the same frames on the sheet.
How numbers are written depends on the interface language. In Russian, the fractional part is written with a comma, and the arguments are separated by a semicolon: =ROUND(1,5;0). In English, a dot is used in numbers, and the arguments are separated by a comma or a semicolon. A formula is stored in the same way, so a co-author with another language sees it in their own notation.
Function names are in English, but Russian names are understood too: =СУММ(A1:A3) is saved as =SUM(A1:A3). ИСТИНА and ЛОЖЬ work as TRUE and FALSE: you can type either, and in the cell and the formula bar a logical value is shown with the word of your interface language (ИСТИНА or TRUE). The ТЕКСТ (TEXT) function writes the names of months and weekdays in Russian for everyone: its result is table data, and it does not depend on the viewer’s language.
Typing hints: after “=”, a bracket, a separator or an operator sign, a list of up to 8 functions and range names matching what you typed appears. The ↑↓ arrows select, Home and End go to the first and the last, Tab or Enter inserts, and Escape closes the list. A function is inserted with an opening bracket; a range name is inserted without it. A workbook’s own function is labelled with its parameters, for example “МАРЖА(цена; себестоимость)”, and is also inserted with a bracket. The hints include names from all sheets of the workbook.
While the cursor is inside a function’s brackets, its signature is shown under the field, and the current argument is highlighted. Show help opens a card: What the function does, For example, and the list of arguments, with those that are optional marked optional. Hide help collapses it; the choice is remembered in this browser.
The function list is the f(x) (Functions) button to the left of the formula bar. At the top is the Search for a function field, which searches by name, Russian name and description. Below are the group Your functions (if the workbook has any), five common ones (SUM, AVERAGE, COUNT, MAX, MIN), and the sections All, Math, Statistical, Text, Date and time, Logical, Info, Lookup, Financial, Engineering, Database, Array, Operator and Web. If you point at a function and wait half a second, a short description appears; from the keyboard it is visible at once. The chosen function is placed at the cursor, and in a cell without a formula it starts a new formula.
You can stretch the formula bar by dragging its bottom edge, or expand it with the Expand formula bar button. Collapse formula bar, or a double-click on the edge, returns it to one line. A line break inside a formula is Shift+Enter or Alt+Enter; in the cell itself, also Ctrl/⌘+Enter.
Named ranges: select the area and click Named ranges on the toolbar (or in the More tools menu). A panel opens to the right of the sheet, and the sheet stays beside it. At the top of the panel are the list Names and the button + Add. Below are the switch Range / Function and the fields Name and Range. By default they hold the current selection and the name “Диапазон1”, “Диапазон2”…; in an empty field the sample is “Цены” and “A1:B10”. The cursor goes straight into Name. To take another area, click the Range field and outline the cells on the sheet: the address is entered by itself. The line In a cell under the fields shows at once how to write the name in a formula: =SUM(Цены). Save records the name, and the panel stays open. Cancel discards the changes, and the bin Remove name deletes the name. A click on a name in the list selects its area on the sheet. The panel is closed with the × button Close, with Escape, or by a repeated click on Named ranges. The Names list also shows the names of other sheets, with the sheet name after “·”; they cannot be selected here, and they are edited on their own sheet. For details, see the document “Workbook functions”.
The address field to the left of the formula bar shows a name when the selection exactly matches the area of a name on this sheet (for example, if you select A2:B2 with the name “Итоги”, the field shows “Итоги”). Otherwise it shows the address. Type a name in the field (letter case does not matter) and press Enter: its area is selected. The field does not accept an unknown name, and it shows the previous one. The arrow next to the field is the Named ranges list: the range names of the sheet, with the address shown lighter to the right of each name. Choosing one selects its area. The last item, Named ranges…, opens the names panel. An open panel stays open.
Workbook functions are a formula under a short name, for example =МАРЖА(D2;E2). How to create them, and what they are useful for, is described in the “Workbook functions” document.
A formula that returns several values (for example, =SEQUENCE(3;2;10)) spills into the neighbouring cells. Spilled cells cannot be edited: edit the source cell instead.
Value hints: when you type ordinary text in a cell, up to 6 values already entered in this column that start the same way appear below (the frequent ones are higher). The arrows select, and Tab inserts.

Limits

The catalogue has 543 functions in 13 sections, and 203 Russian names for them.
A formula can be up to 8 192 characters long.
One formula reads no more than 100 000 cells across all its ranges together, and any array inside it no more than 10 000 cells. Beyond that, the cell shows #NUM!. On a sheet of 50 000 rows, this means: SUMIF over two columns, up to 49 000 rows; SUMIFS over three columns, up to 33 000 rows; two SUMIFs in one formula share the limit (2 × 20 000 rows is allowed, 2 × 30 000 is not); XLOOKUP and FILTER, up to 10 000 rows. COUNTIF, VLOOKUP(…;FALSE) and INDEX/MATCH over a whole column work on the whole sheet.
Nested calls of workbook functions: up to 128 levels.
A name applies to the whole workbook: identical names cannot exist on different sheets. A sheet can have up to 500 names. A name can be up to 255 characters, starts with a letter, “” or “\”, and continues with letters, digits, “”, “.” or “\”. A name cannot look like a cell address (A1), a R1C1 reference, ИСТИНА, ЛОЖЬ, TRUE or FALSE. A workbook function cannot have the name of a built-in function. A function can have up to 253 parameters.
Renaming a range or a workbook function changes the old name in the formulas across the whole workbook.
The search in the f(x) list shows up to 60 functions. The short description on hover appears after 0.5 s.
Value hints do not appear for formulas, for text with a leading apostrophe, for cells with a date format, or for values longer than 200 characters or spanning several lines.
In a spreadsheet opened for viewing only, the f(x) list is absent: instead of the button, the label f(x) is shown.
The address field and the Named ranges list know only the range names of the open sheet. A name from another sheet is not there, and the workbook’s own functions are not included either. In a spreadsheet opened for viewing only, the list has no Named ranges…, and without names on the sheet there is no arrow at all.

If something goes wrong

What you see on the screen	Why	What to do
#NAME? in a cell	An unknown function or name, or the formula cannot be parsed (for example, in a Russian interface the arguments are separated by a comma)	Check the name. In a Russian interface, put “;” between the arguments
#NUM! on a large range	The limit is exceeded: 100 000 cells per formula, or 10 000 in an array	Count in parts by rows, or use SUMIF, COUNTIF, INDEX/MATCH or VLOOKUP with FALSE
#SPILL!	The cells where the formula spills its result are occupied	Free the cells to the right and below
Cell occupied by spill from D1	An attempt to edit a cell inside a spill	Edit the formula in the cell indicated
#CYCLE!	The formula refers to itself, including through other cells	Remove the circular reference
#REF!	The reference goes beyond the sheet, or to a deleted sheet	Fix the reference
Could not apply the value	The edit could not be applied to the spreadsheet	Enter the value again. If that does not help, reload the page
Check the name	The name does not fit the rules (it looks like an address, R1C1, ИСТИНА/ЛОЖЬ)	Choose another name
This name already exists	A name like this already exists in the workbook, on this or another sheet	Choose another name
Check the range	The address in the Range field was not understood	Outline the cells on the sheet, or write the form A1:B10
Check the formula	The formula of a workbook function cannot be parsed	Check the brackets and quotes
Check the name and parameters	The parameters repeat, or are unsuitable as names	Give the parameters different, valid names
A built-in function has this name	A workbook function is named like a built-in one (SUM, СУММ)	Choose another name
Check the name and range	The sheet already has 500 names	Delete extra names
Nothing found in the f(x) list	No functions match the query	Check the spelling, or open the All section