# 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.

Source: https://plexiidesk.com/help/sheets-formulas-and-functions
Section: Office > PlexiiSheets
Plans: All plans
Updated: 2026-10-15

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. |

> **Note:**
> In arithmetic such as `=A1+A2`, a cell holding text counts as 0. Functions such as SUM, AVERAGE and COUNT skip text, so an average is over the numbers only.

## 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](https://plexiidesk.com/help/sheets-ai-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](https://plexiidesk.com/help/sheets-fill-copy-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](https://plexiidesk.com/help/sheets-rows-columns-and-tabs).

## Related tasks

- [Describe a calculation, get a formula](https://plexiidesk.com/help/sheets-ai-formula)
- [Fill, copy and paste](https://plexiidesk.com/help/sheets-fill-copy-paste)
- [Rows, columns, frozen panes and tabs](https://plexiidesk.com/help/sheets-rows-columns-and-tabs)
- [PlexiiSheets overview](https://plexiidesk.com/help/sheets-overview)
- [Charts and sparklines in a sheet](https://plexiidesk.com/help/sheets-charts-and-sparklines)