• 8 heures
  • Facile

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 09/12/2022

Make calculations in your spreadsheet with functions and formulas

Grasp the potential of functions and formulas

Functions and formulas are very useful because:

  • They can quickly give you more information than you previously had, like a sum or an average.

  • They are more efficient, in that you don’t have to make the calculation yourself.

  • If the value of one cell changes later on, using a function or formula means your result will automatically get updated. The spreadsheet fetches the right value, and the resulting value will be updated automatically.

Write formulas

To enter a formula, you need to start by checking the references of the cells you want to include      ( A6,  A7,  A8, for example), then choose your result cell (A9, for example), and finally type an equal sign in there, followed by your cell references.

It could look like this  =A6*A7+A8 .

Write functions

Functions follow the same rules (using the equals sign and no spaces), but always start with a word. You can either:

  • Type this word in yourself, and the program will recognize which calculation you aim to use, or

  • Select a function by navigating to Insert > Functions. From there, you’ll see the most common functions.

Screenshot, quick action button.

Once you've written out the name of the simple function, you need to:

  • Open a parenthesis.

  • Either select individual cells or a range of cells (OR write out their reference).

  • Close the parenthesis.

  • Release or click enter for the result to appear.

The spreadsheet helps you every step of the way with a cheat sheet of what you have to enter:

Details of the function are given, with a clear indication of what needs to be filled in
Here I have a complete cheat sheet to figure out what I need to enter.

If you are in a result cell and you want a reminder of the formula or function behind it, select the cell and check out the formula bar.

The formula show in the formula bar
At the top of your spreadsheet, "fx" marks the formula bar.

This gives you a visual display of the function or formula, and cells that are used.

Use common functions

Common functions include  =SUM(),  =AVERAGE(),  =COUNT(),  =MIN(), and  =MAX(), which we’ll cover now.

SUM

The sum is the total of selected cells. The most efficient way to identify cells, if they are right next to each other, is to drag the cursor over the range of cells you want to total.

Here is a quick video of the SUM function in action:

AVERAGE

Averaging takes the total of the values divided by the number of cells. You don't need to specify the number of cells because the spreadsheet does this leg work on its own!

COUNT

COUNT()  provides the count of the number of cells with numeric values in them in the selected range. It's close sister function is  COUNTA()  which counts the number of non-empty cells, whether they are filled with numeric values or text.

A few extra functions for the road

MAX

= MAX (start cell:end cell )

Finds largest number in specific range

MIN

= MIN (start cell:end cell )

Find smallest number in specific range

TODAY

= TODAY()

Returns the current date

UPPER

= UPPER (cell with text)

Converts text to uppercase

LOWER

= LOWER (cell with text)

Converts text into lowercase

Let’s recap!

  • To enter a formula or a function, start by typing an equal sign into the cell where you want the calculated value to be.

  • Make sure not to include any spaces inside your formula or function as this will create error messages!

  • A formula is an equation (like math) which can be designed by a user. 

  • A function is a predefined calculation in the spreadsheet and relies on words to instruct the spreadsheet on which calculations to make.

  • Once you've written out the name of the simple function, you need to:

    • Open a parenthesis.

    • Either select individual cells or a range of cells (OR write out their reference).

    • Close the parenthesis.

    • Release or click enter for the result to appear.

Now that we’ve covered the basics of formula and functions, we’re going to go deeper with IF: a pretty powerful spreadsheet function. 

Exemple de certificat de réussite
Exemple de certificat de réussite