Excel - Using functions Tutorial

In this video, you will learn about using functions in Microsoft 365. The video covers the basics of functions and demonstrates how to use them effectively in various Microsoft 365 applications.
This will help you enhance your productivity and make the most out of the Microsoft 365 suite.

5:12
4304 views

Available in Microsoft365

Objectifs :

This video aims to teach users how to effectively use the AutoSum, SUM, PRODUCT, and basic arithmetic functions in Excel to perform calculations efficiently. It covers the syntax of these functions, how to handle ranges of cells, and the use of keyboard shortcuts to streamline the process.

Chapitres :

Introduction to AutoSum
AutoSum is a powerful feature in Excel that simplifies the process of adding adjacent cells in rows and columns. Instead of manually typing cell references like A3, A4, A5, and A6, users can simply click the cell to the right of a row or below a column. By navigating to the Home tab and selecting AutoSum, users can quickly verify the formula and execute the addition.
Understanding the SUM Function
The SUM function is a predefined formula that saves time when adding numbers. The syntax for the SUM function is as follows: SUM(number1, number2, ...). For example, the formula SUM(A3:A6) adds all cells from A3 through A6. It is important to note that when using AutoSum, if there are empty cells in the range, it may not recognize the entire column.
Adding Non-Adjacent Cells
To add cells that are not adjacent, users can hold down the Ctrl key, click on the desired cells, and press Enter. This method allows the SUM function to include only the selected cells containing numbers while ignoring any empty cells.
Subtraction in Excel
While there is no dedicated subtraction function in Excel, users can perform subtraction using a basic formula. This involves taking a number or cell and subtracting the next number or cell. For example, to subtract values, the formula would look like: =A1 - A2.
Using Keyboard Shortcuts
The keyboard shortcut for AutoSum is Alt + =, followed by pressing Enter. This shortcut allows for quick calculations without navigating through menus.
Multiplication with the PRODUCT Function
To multiply numbers or cells, users can utilize the PRODUCT function. The syntax is similar to the SUM function: PRODUCT(number1, number2, ...). To multiply a range of cells, hold down the Ctrl key, select the desired cells, and press Enter. This function also ignores empty cells.
Division in Excel
Division can be performed using a basic formula where a number or cell is divided by the next number or cell. Users can also divide the result of one function, such as PRODUCT, by another function, such as SUM. For example, the formula =PRODUCT(B3:B6)/SUM(C3:C6) divides the product of the cells in B3 through B6 by the sum of the cells in C3 through C6.

FAQ :

What is AutoSum and how do I use it?

AutoSum is a feature in spreadsheet software that allows you to quickly add adjacent cells. To use it, click the cell to the right of a row or below a column, then click on the AutoSum button in the Home tab. Verify the formula and click AutoSum again to add the cells automatically.

What is the syntax of the SUM function?

The syntax of the SUM function is SUM(number1, number2, ...), where you can input numbers or cell references separated by commas. For example, SUM(A3:A6) adds all the values from cells A3 to A6.

How can I add non-adjacent cells using the SUM function?

To add non-adjacent cells, hold down the Ctrl key while clicking on the desired cells and ranges, then press Enter. This will use the SUM function to add the selected cells while ignoring any empty cells.

Is there a subtraction function in spreadsheets?

No, there isn't a specific subtraction function. To subtract, you can use a basic formula like 'number1 - number2'.

How do I multiply numbers in a spreadsheet?

You can multiply numbers using the PRODUCT function. The syntax is PRODUCT(number1, number2, ...). To multiply a range of cells, hold down the Ctrl key, select the desired cells, and press Enter.

Can I divide the results of one function by another?

Yes, you can divide the results of one function by another. For example, you can divide the result of the PRODUCT function by the result of the SUM function using a formula like 'PRODUCT(B3:B6) / SUM(C3:C6)'.

Quelques cas d'usages :

Budget Calculation

Using the SUM function to calculate the total expenses in a budget spreadsheet by adding up all the costs listed in adjacent cells.

Sales Data Analysis

Applying AutoSum to quickly find the total sales figures for a month by selecting the relevant cells in a sales report.

Inventory Management

Utilizing the PRODUCT function to calculate the total value of inventory by multiplying the quantity of items by their unit price.

Financial Reporting

Creating a financial report that uses both SUM and PRODUCT functions to analyze revenue and expenses, providing insights into profitability.

Performance Metrics

Employing the SUM function to aggregate performance metrics across different departments, allowing for a comprehensive overview of organizational performance.

Glossaire :

AutoSum

A feature in spreadsheet software that automatically adds adjacent cells in rows and columns.

SUM function

A predefined formula used to add a range of numbers or cell references. The syntax is SUM(number1, number2, ...).

Syntax

The set of rules that defines the combinations of symbols that are considered to be correctly structured formulas in a programming language.

Range

A selection of two or more cells in a spreadsheet, typically defined by the first and last cell in the selection (e.g., A3:A6).

Ctrl key

A modifier key on a keyboard that, when pressed in combination with other keys, performs special functions.

Product function

A predefined formula used to multiply a range of numbers or cell references. The syntax is PRODUCT(number1, number2, ...).

Division

The mathematical operation of dividing one number by another, typically represented by the forward slash (/) symbol.

Basic formula

A simple mathematical expression that performs calculations using numbers or cell references.

00:00:07

you could type out = A3 + A4 + A5 + A6

00:00:17

but that's tedious.

00:00:19

Instead, click the cell to the right of a row or below a column.

00:00:25

Then on the Home tab click AutoSum,

00:00:30

verify that the formula is what you want and click AutoSum again.

00:00:36

AutoSum adds the row or column automatically,

00:00:39

it's really handy.

00:00:41

When I double-click inside a cell where I'm using AutoSum,

00:00:45

I see it's a formula with a SUM function.

00:00:48

A function is a predefined formula, it helps save you time.

00:00:54

The syntax or grammar of the SUM function is SUM,

00:00:58

open parenthesis, arguments, like numbers or cell references

00:01:03

separated by commas and finally close parenthesis.

00:01:09

This formula is SUM A3:A6,

00:01:15

the colon indicates a range of cells.

00:01:18

This formula adds the cells from A3 through A6.

00:01:23

When adding a few cells, the sum function saves you time;

00:01:27

with larger ranges of cells, it's essential.

00:01:31

If you try to use AutoSum here, it only gets the adjacent cell

00:01:36

not the entire column.

00:01:39

Why? Because B5 is empty, if B5 wasn't empty,

00:01:44

B3 through B6 would be an adjacent range of cells

00:01:48

that AutoSum could recognise.

00:01:51

To add cells in ranges of cells that aren't adjacent,

00:01:56

SUM, hold down the Ctrl key,

00:02:02

click the desired cells and ranges of cells

00:02:08

and press Enter.

00:02:10

The formula uses the SUM function to add

00:02:12

the cells containing numbers and ignores the empty cells.

00:02:18

Subtraction is similar to addition but there isn't a subtraction function.

00:02:24

To subtract, you use a basic formula like

00:02:29

number or cell minus the next number or cell and so on.

00:02:37

You can sum numbers where some of them are negative.

00:02:41

The keyboard shortcut for AutoSum is Alt-=,

00:02:46

and then I press Enter.

00:02:49

This is equivalent to 6-3+4-5.

00:02:56

You can also subtract the results of one SUM function from another,

00:03:00

SUM, select a range of cells,

00:03:06

- SUM,

00:03:10

select another range of cells

00:03:14

and press Enter.

00:03:18

As I mentioned about adding earlier, to multiply cells

00:03:21

you could type out =A3*A4*A5 *A6

00:03:33

but that's tedious.

00:03:35

To speed things up use the product function to multiply numbers or cells.

00:03:41

Product is similar to the Sum function but instead of

00:03:44

adding numbers it multiplies them.

00:03:47

It's another example of how functions help save you time.

00:03:53

The syntax of the product function is PRODUCT,

00:03:56

open parenthesis, arguments like numbers or cell references,

00:04:01

separated by commas and finally close parenthesis.

00:04:07

To multiply cells in ranges of cells in one formula,

00:04:12

PRODUCT,

00:04:16

hold down the Ctrl key, select the desired cells and ranges of cells

00:04:27

and press Enter.

00:04:29

The formula uses the product function to

00:04:31

multiply cells containing numbers and ignores the empty cells.

00:04:39

Division is similar to multiplication but there isn't a divide function.

00:04:45

A basic formula would be number or cell, forward slash,

00:04:50

the next number or cell and so on.

00:04:54

You can divide the results of one function such as

00:04:57

PRODUCT by the results of another, such as SUM.

00:05:01

This formula divides the product of the cells in B3 through B6,

00:05:06

by the sum of the cells in C3 through C6.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2796 Views

Video Training Microsoft Excel - Discovery level

Discover and familiarize yourself with Excel: from creating and using your tables to enhancing your visuals and collaboration. Our introduct...

ALL COURSES