Google Sheets Course: Formulas

Calculation Formulas

Always start your formula with a =, then add a number, then an operator (for example +), then a number, etc. Add ( ) when necessary.


=45-35 (the displayed result will be: 10)

=(45-35)^2/8 (the displayed result will be: 12.5)

Here are the useful operators for your calculations:


You now know how to write a formula by directly entering the numbers in the formula but in most cases, you will need to enter cell references instead of directly entering numbers.

To better understand the usefulness of entering cell references, observe this example:

google sheets formula formulas

In this case, the numbers have been entered directly in the formula =50*60.

The problem is that if you now change the values of cells A2 or B2, you will have to think to modify the formula with each change and this is really not practical:

google sheets formula cell formulas

That's why you should refer to the cells instead of directly entering the numbers in the formula =A2*B2:

google sheets formula references formulas

In this way, when you modify cells A2 or B2, the result of the formula will be automatically recalculated:

google sheets formula references cells formulas
To add a cell reference, simply click on the concerned cell (after entering the =) or directly enter its name in the formula.

Copying Formulas

As you have seen in the previous lesson, copying works very well with formulas:

google sheets formula copy formulas

The starting formula at line 2 is =A2*B2 and the copy ends at line 6 with the formula =A6*B6:

google sheets formula copy incremented formulas

But in some cases, you will need to reference a unique cell...

To better understand, a divisor is now added to the formula =A2*B2/B8 and the formula is copied:

google sheets copied formula formulas

This time, it won't work because the line of the divisor has been moved in the same way as values 1 and 2:

google sheets copied formula error formulas

To fix a reference during a copy, add $ to the reference.

The formula =A2*B2/B8 then becomes =A2*B2/$B$8:

google sheets formula copy dollar formulas

After copying, all the formulas now refer to cell B8:

google sheets copied formula dollar formulas
The first $ of $B$8 fixes the column during copying and the second $ fixes the line. In this example, it is therefore possible to fix only the line with B$8 since there is no column offset during copying.