Skip to content
Book a demo Try the demo MacWindows

Formulas, functions and named ranges

Write formulas in PlexiiSheets with cell references, functions and named ranges, refer to other tabs, insert a lookup, and understand the error values.

All plansUpdated

A formula works something out from other cells and keeps the answer up to date. Type = in a cell, then a calculation such as =B2*C2 or a function such as =SUM(B2:B10). PlexiiSheets shows the result in the cell and the formula in the formula bar.

Before you start

  • Plans. Formulas are on every plan.
  • Columns have letters and names. Formulas use the letters, such as B. A column's name, shown beside its letter, is a label for people.

Write a formula

  1. Click the cell where the answer should go.
  2. Type = and the formula, for example =B2+B3 or =SUM(B2:B10).
  3. Press Enter.

To change a formula, double-click the cell, press F2, or edit it in the formula bar.

Point at cells instead of typing them

While you type a formula, you can add a reference by pointing:

  • Click a cell, or drag across a range, to put its reference into the formula at the cursor.
  • Use the arrow keys straight after =, an operator such as +, an opening bracket or a comma. A dashed outline shows the cell you are pointing at, and its reference goes into the formula. Keep pressing arrows to move it, then type the next part of the formula.

Pick a function from the list

Type = and a few letters, such as =SU. A list of matching functions opens under the cell, each with a short description.

  • Press ↑ and ↓ to choose, and Enter or Tab to insert it with its opening bracket. Or click it.
  • Press Esc to close the list and keep typing.

Once you are inside a function's brackets, a hint under the cell shows what it expects, with the part you are typing in bold.

References

You type It means
B2 One cell.
B2:B10 A range of cells.
$B$2 A cell that stays the same when you copy or fill the formula. $B2 keeps only the column, B$2 only the row.
Sheet2!A1 A cell on another tab. Put quotes round a name with spaces: 'Q3 plan'!B4.
Revenue A named range (see below).

When you insert or delete rows and columns, formulas are updated so they still point at the same cells. A formula that pointed at a deleted row or column shows #REF!.

Named ranges

A name stands for a cell or range, so =SUM(Revenue) reads better than =SUM(C2:C40). Names belong to the whole spreadsheet and work on every tab.

  1. Select the cells you want to name.
  2. Click Names beside the formula bar, or choose Insert > Named range. The Named ranges panel opens, with your selection already filled in, such as A1:B10.
  3. Type the name in Name.
  4. Click Add, or press Enter in the range box.

The panel lists every name with what it refers to. Click the bin beside a name to delete it. A range can be on another tab, such as Sheet2!A1:C3.

Insert a lookup

To pull a value from a table, such as a price by product code:

  1. Click the cell where the result should go.
  2. Click the lookup button (the magnifying glass) in the toolbar. The Lookup a value window opens.
  3. Fill in:
    • Look up this key: the value to find, or the cell that holds it, such as A2.
    • In this table range: the table, such as A2:C50.
    • Match column (in table): which column of the table holds the keys, counting from 1.
    • Return column (in table): which column holds the answer.
    • If not found (optional): what to show when there is no match.
  4. Click Insert lookup.

PlexiiSheets writes an =XLOOKUP(…) formula into the cell, so the result stays up to date.

Functions you can use

Type = to see the full list with descriptions. They include:

Kind Functions
Maths SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, PRODUCT, ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, ABS, SIGN, SQRT, POWER, EXP, LN, LOG, LOG10, MOD
Statistics and counting AVERAGE, AVERAGEIF, AVERAGEIFS, MEDIAN, MIN, MAX, LARGE, SMALL, RANK, STDEV, VAR, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COUNTUNIQUE
Logic IF, IFS, IFERROR, AND, OR, NOT, SWITCH, CHOOSE
Text CONCAT, TEXTJOIN, LEN, LEFT, RIGHT, MID, UPPER, LOWER, PROPER, TRIM, CLEAN, FIND, SEARCH, SUBSTITUTE, REPLACE, TEXT, VALUE, CHAR, CODE, TEXTSPLIT, REGEXMATCH, REGEXEXTRACT, REGEXREPLACE
Lookup and reference XLOOKUP, XMATCH, VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT, ROW, COLUMN, ROWS, COLUMNS
Arrays SEQUENCE, UNIQUE, SORT, FILTER, TRANSPOSE, VSTACK, HSTACK, TOROW, TOCOL, TAKE, DROP, MAKEARRAY, MAP, REDUCE, SCAN, LET, LAMBDA
Dates and times DATE, TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, HOUR, MINUTE, SECOND, DAYS, DATEDIF, EDATE, EOMONTH
Information ISBLANK, ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISNA, ISEVEN, ISODD, NA
Finance PMT, FV, PV, NPV
Charts SPARKLINE

Formulas that fill several cells

Some functions, such as SEQUENCE, UNIQUE, SORT, FILTER and TRANSPOSE, return more than one value. Type the formula in one cell and the results spill into the cells below and to the right, shown in purple. If those cells are not empty, the formula shows #SPILL! instead; clear them and the results appear.

Error values

An error is shown in red in the cell, so a problem is never hidden behind a plausible number.

Error Usually means
#DIV/0! Dividing by zero or by an empty cell.
#N/A A lookup found no match.
#VALUE! A value of the wrong kind, such as text where a number is needed.
#NAME? A function or name that does not exist. Check the spelling.
#NUM! A calculation that has no answer, such as the square root of a negative number.
#REF! A reference to a row or column that was deleted.
#SPILL! A formula's results would cover cells that are not empty.
#ERR Anything else, such as a formula that cannot be read, or one that refers back to its own cell.

Common questions

Can PlexiiSheets write the formula for me? Yes. Click AI beside the formula bar and describe the calculation. See Describe a calculation, get a formula.

How do I copy a formula down a column? Drag the small square at the corner of the cell, or select the cells and press ⌘ D or Ctrl D. See Fill, copy and paste.

A formula refers to the wrong cells after I moved a column. When you drag a column or row to a new place, PlexiiSheets asks whether to fix the formulas that point at it. Choose Auto-fix & move to keep every result the same. See Rows, columns, frozen panes and tabs.

Still need help? Contact support at [email protected].