How formulas work in Google Sheets

A formula is an instruction that works something out and shows the answer in a cell. Change a number it depends on and the answer changes by itself. This guide covers the basics, with the points where Google Sheets has its own way of doing things.

Start with an equals sign

Click a cell, type =, then the calculation, and press Enter.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=C2+D2+E2
Result: 254
Adds three cells. Refer to cells, not to typed numbers, so that a corrected mark corrects the total.
=(C2+D2+E2)/3
Result: 84.66666667
Brackets first, then division. Without them only E2 would be divided.
=SUM(C2:E2)
Result: 254
A function, with a range: C2 to E2.
=ROUND(AVERAGE(C2:E2),1)
Result: 84.7
One function inside another. The inner one is worked out first.
=A2&" of class "&B2
Result: Asha of class 8A
The & sign joins text. Typed text goes in double quotation marks.
=C2>=33
Result: TRUE
A comparison gives TRUE or FALSE.

The operators are +, -, *, /, ^ for powers, & to join text, and the comparisons =, <>, >, <, >=, <=.

As you type a function name, Sheets shows a list of suggestions and then a small help box with the arguments. Press Tab to accept a suggestion.

Ranges

A2:A10 is a block of cells.
A:A is the whole of column A.
A2:A is from A2 to the bottom of the sheet. This open-ended form is particular to Sheets. It is the usual way to write formulas that keep working as rows are added.
2:2 is the whole of row 2.

Other tabs and other files

A cell on another tab is written with the tab's name and an exclamation mark: =Prices!B2. If the name has spaces, put it in single quotation marks: ='Price List'!B2.

A cell in another spreadsheet file cannot be referred to directly. Use IMPORTRANGE.

Copying a formula: relative and fixed references

Drag the small square at the corner of a cell to copy its formula down. The references move with it: =C2*2 becomes =C3*2 on the next row.

To stop a reference moving, lock it with dollar signs: $F$1 always points to F1. Press F4 while editing to add them. $F1 locks only the column and F$1 only the row.

A cell or range can also be given a name, under Data, Named ranges. =B2*TaxRate then reads clearly and never moves.

One formula for a whole column

Sheets offers an alternative to copying down. Wrap the formula in ARRAYFORMULA and give it ranges:

=ARRAYFORMULA(IF(A2:A="", "", C2:C+D2:D+E2:E))

One formula in row 2 fills the entire column and covers rows added later. Functions such as FILTER, SORT, UNIQUE and QUERY return whole blocks of results in the same way. The cells they fill must be empty, or the formula shows #REF!.

Commas or semicolons?

The character between arguments depends on the spreadsheet's locale, under File, Settings. With the locale set to India, the United Kingdom or the United States, it is a comma. In locales where the comma is the decimal mark, it is a semicolon: =IF(A1>5;"Yes";"No"). If a formula copied from this site is rejected, check the locale. The locale also decides how typed dates are read and how currency is shown.

Seeing and checking formulas

The formula bar above the grid shows the formula of the selected cell.
Ctrl and the grave accent key switches the sheet between formulas and results. The same choice is under View, Show, Formulae.
Double-click a cell, or press F2, to edit. The ranges it uses are outlined in colour.
File, Version history lets you go back if a change breaks something.

When the cell shows an error

A result beginning with a hash sign means the formula could not be worked out. The guide to errors in Google Sheets explains each one.

Good habits

Keep figures that may change, such as a rate or a pass mark, in their own labelled cells.
One kind of data per column. A column that mixes text and numbers trips up QUERY, sorting and totals.
Do not type in cells that a spilling formula needs.
Check a new formula against one row worked out by hand.

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.