Excel - Advanced formulas and references Tutorial

In this video, you will learn about the intelligent search feature in Microsoft 365. The video covers the search tools in Microsoft 365, including how to search on your computer, in SharePoint, and in your emails.
It also demonstrates how to back up and save notes, run a search in the Office365 Portal, and search on your mobile phone.
This will help you find everything you need, including documents, images, videos, people, and conversations, quickly and efficiently.
Gain time and efficiency in your searches with Microsoft's intelligent search tool in Microsoft 365.

4:06
4529 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to write a basic formula in Excel, demonstrating key concepts such as cell referencing, calculation order, and the use of functions. By the end of the video, learners will understand how to create and manipulate formulas effectively in a worksheet.

Chapitres :

Introduction to Excel Formulas
In this section, we will explore the basics of writing a formula in Excel. The goal is to demonstrate important concepts that will help you manage data effectively. We start by adding a new worksheet to keep everything organized.
Setting Up the Worksheet
To begin, click the plus sign to add a new worksheet. Next, double-click the worksheet tabs to rename them appropriately. This helps in identifying the data we will be working with. After renaming, we can proceed to input the data needed for our formula.
Creating the Formula
The formula we will create will utilize amounts from two columns and a percentage. First, we need to designate a cell to hold the percentage amount. Choose a cell outside the data range and enter '1'. Then, format this cell as a percentage using the number formatting options. This percentage will act as a buffer amount in our formula.
Understanding Calculation Order
When entering the formula, it is crucial to understand the order of operations in Excel. By default, multiplication and division are calculated before addition and subtraction. To ensure that Excel adds the first two cells before multiplying, we will use parentheses in our formula.
Using Autofill and Relative References
After entering the formula, we can use the Autofill feature to apply it to other cells. However, we encounter an issue where the formula references change based on their location. This is due to Excel's use of relative references. To maintain a constant reference to a specific cell, we need to add dollar signs in front of the column letter and row number (e.g., $D$20). This ensures that the formula always refers to the same cell, regardless of where it is copied.
Calculating Different Buffer Amounts
With the formula set up correctly, we can now change the percentage value to calculate different buffer amounts. This flexibility allows for dynamic adjustments in our calculations.
Adding an Estimated Cost Column
Finally, we will add an Estimated Cost column using the AutoSum function. By selecting the cell and clicking AutoSum, Excel will automatically sum the three columns to the left and apply the formula to the other cells. This feature simplifies the process of calculating totals in your worksheet.
Conclusion
In this course, you have learned the fundamentals of creating your first Excel workbook, including how to write formulas, understand calculation order, and utilize functions effectively. These skills are essential for managing data and performing calculations in Excel.

FAQ :

How do I create a formula in Excel?

To create a formula in Excel, start by clicking on a cell where you want the result to appear. Type an equal sign (=) followed by the mathematical expression you want to calculate, using cell references as needed.

What is the difference between relative and absolute references?

Relative references change when you copy a formula to another cell, while absolute references remain fixed. You can create an absolute reference by adding dollar signs before the column letter and row number (e.g., $A$1).

How can I format a cell as a percentage in Excel?

To format a cell as a percentage, select the cell, go to the 'Number' group in the toolbar, and choose 'Percentage' from the formatting options.

What does the Autofill feature do?

The Autofill feature allows you to quickly fill a series of cells with data or formulas based on a pattern. You can drag the fill handle (a small square at the bottom-right corner of a selected cell) to extend the series.

Why is my formula returning zero?

If your formula is returning zero, it may be due to referencing an empty cell. Check your cell references to ensure they point to cells with data.

How do I use the Auto Sum function?

To use the Auto Sum function, select the cell where you want the total to appear, click on the Auto Sum button in the toolbar, and Excel will automatically suggest a range of cells to sum. Press Enter to confirm.

Quelques cas d'usages :

Budget Tracking

Use the skills learned in this video to create a budget tracking worksheet. Input your income and expenses in separate columns, and use formulas to calculate your total expenses and remaining budget. This can help you manage your finances effectively.

Project Cost Estimation

In project management, you can apply these Excel skills to estimate project costs. Create a worksheet that includes material and labor costs, and use formulas to calculate total costs based on varying percentage buffers for unexpected expenses.

Sales Analysis

Sales teams can utilize these Excel techniques to analyze sales data. By inputting sales figures and applying formulas to calculate commissions based on percentage rates, teams can quickly assess performance and adjust strategies.

Inventory Management

In inventory management, use Excel to track stock levels. Create a worksheet that calculates reorder levels based on current stock and sales trends, using formulas to automate the calculations and ensure timely restocking.

Financial Reporting

Finance professionals can use these Excel skills to prepare financial reports. By creating formulas to summarize data from multiple sources, you can generate comprehensive reports that provide insights into financial performance.

Glossaire :

Formula

A mathematical expression that calculates a value based on the values in other cells. In Excel, formulas begin with an equal sign (=).

Worksheet

A single page in an Excel workbook where you can enter and manipulate data. Each workbook can contain multiple worksheets.

Cell

The intersection of a row and a column in a worksheet, identified by its cell reference (e.g., A1, B2).

Percentage

A way of expressing a number as a fraction of 100. In Excel, percentages are formatted with a percent sign (%).

Autofill

A feature in Excel that allows you to automatically fill a series of cells with data based on a pattern or formula.

Relative Reference

A cell reference that adjusts when the formula is copied to another cell. For example, if a formula in cell A1 refers to B1, when copied to A2, it will refer to B2.

Absolute Reference

A cell reference that remains constant, regardless of where the formula is copied. It is indicated by dollar signs (e.g., $D$20).

Auto Sum

A function in Excel that automatically adds up a range of cells. It can be accessed from the toolbar and is often used for quick calculations.

Calculation Order

The sequence in which Excel performs calculations in a formula. Multiplication and division are performed before addition and subtraction.

00:00:05

demonstrate a couple of important concepts.

00:00:08

First, to help us keep everything straight, let's click

00:00:10

this plus sign to add a new worksheet.

00:00:14

Then double click the worksheet tabs and type names.

00:00:24

Now we can add the data.

00:00:28

The formula we're going to write will go in this column,

00:00:31

and use the amounts in these two columns, and a percentage amount.

00:00:36

We could format the data as a table but let's leave it

00:00:38

as it is for this demonstration.

00:00:41

The first thing we need is a cell to hold the percentage amount.

00:00:44

Pick a cell outside the data and type .1

00:00:50

then come up here to the number group

00:00:52

and click the Percent button to format the cell as a percentage.

00:00:56

When we're finished, we'll be able to change this number if we want

00:00:59

to calculate a different cost buffer amount with our formula.

00:01:03

Now we can enter the formula.

00:01:07

The formula adds the material and labor cells,

00:01:12

and then multiplies that amount by the buffer percentage cell.

00:01:18

The asterisk is used to multiply.

00:01:21

Press Enter to see what happens.

00:01:23

Well it's pretty obvious this amount is not 10% of the first two columns,

00:01:27

and the reason it's not is because I didn't consider calculation order.

00:01:32

This this is the order in which Excel calculates the parts of a formula.

00:01:36

You don't need to remember all the details,

00:01:38

just keep in mind that multiplication and division are calculated

00:01:41

before addition and subtraction.,

00:01:44

And in our formula we wanted Excel to add the first cells then multiply.

00:01:49

To tell Excel to add first, we simply insert parentheses.

00:01:56

Now instead of multiplying then adding, Excel will add

00:01:59

the first two numbers, then multiply.

00:02:04

Now we can use Autofill to add the formula to the other cells.

00:02:09

But now we run into another problem:

00:02:11

in the first cell, the formula gets its buffer percentage from D20;

00:02:16

but in the next cell the formula refers to D21,

00:02:20

and since D21 is empty, the formula multiplies by zero, and returns a value of zero.

00:02:25

But why did Excel change this cell reference to D21?

00:02:29

Because when you use Autofill, or copy a formula,

00:02:32

Excel uses relative references by default.

00:02:35

Here's how that works:

00:02:37

in the first cell the formula refers to these cells in row 6 and D20;

00:02:42

when you use Autofill, Excel changes the cell reference based on

00:02:46

location to row 7 and D21.

00:02:50

In most cases this is what you want, but in this case

00:02:53

we want the formulas to always use the value in this particular cell.

00:02:57

To make that happen, we need to change the cell reference to an absolute reference:

00:03:02

in the formula click D20,

00:03:04

and then type dollar signs in front of the D, and the 20.

00:03:09

The dollar signs tell Excel that the formula must always

00:03:11

refer to the cell in column D in the 20th row.

00:03:15

Press Enter.

00:03:18

Now when you autofill the cell,

00:03:23

all the formulas point to D20.

00:03:30

Now we can change the percentage to calculate a different buffer amount.

00:03:37

The last thing we will do is add an Estimated cost column,

00:03:39

that uses a function.

00:03:44

With G6 selected, click Auto-sum.

00:03:49

Excel automatically assumes we want to add the three columns to the left,

00:03:53

so press Enter,

00:03:55

and then autofill the formula to the other cells.

00:04:00

In this course you've learned the basics of creating your first Excel workbook.

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