Excel - Add formulas and references Tutorial

In this video, you will learn about adding formulas and references in Microsoft 365. The video covers how to use formulas and references in Office apps like Word, Excel, and PowerPoint to enhance your work and collaboration.
You can easily create formulas to perform calculations and use references to link data between different files.
This will help you improve your productivity and efficiency when working with Microsoft 365.

2:52
3896 views

Available in Microsoft365

Objectifs :

Understand how to create and use formulas and functions in Excel to manage budgets effectively.

Chapitres :

Introduction to Budget Tracking in Excel
In this section, we will explore how to effectively track our budget using Excel. We will focus on adding an 'over/under budget' column to our worksheet, which will help us monitor actual costs as they come in.
Creating a Formula
To make the 'over/under budget' column functional, we need to add a formula. Start by selecting the first cell in the column. To indicate that we are entering a formula, type an equal sign (=). There are various ways to write a formula; one method is to type the values in each column with a minus sign between them. However, if the numbers change, we would need to rewrite the formula. To avoid this, we can use cell references instead of typing the values directly.
Using Cell References
When using cell references, simply type the cell addresses. Excel will highlight the referenced cells with a colored outline, helping you keep track of them. After entering the formula, press Enter, and Excel will calculate the result and display it in the cell. Remember, even though the cell shows the result, the actual content is still the formula. To check if a cell contains a formula, click on the cell and look in the formula bar.
Utilizing Autofill
Excel offers a convenient feature called Autofill. To use it, click the small green handle in the cell and drag it down. Excel will automatically fill the formula into the other cells, updating the cell references accordingly. This feature saves time and ensures accuracy in your calculations.
Introduction to Functions
For an even simpler way to enter formulas, we can use functions. Functions automate the formula writing process. For instance, you can add a function to the Total row. If you select 'Sum', Excel will add the SUBTOTAL function. Functions can be added to any cell; simply select a cell, go to the Formulas tab on the ribbon, and access a library of functions.
Exploring More Functions
To add a function, click on AutoSum, select Sum, and the function will be added to the cell. You can also select the cells you want to add. For a comprehensive view of all available functions, click on 'More Functions'. This will allow you to explore various functions that can enhance your budgeting capabilities in Excel.
Conclusion
In this video, we learned how to create and use formulas and functions in Excel to track our budget effectively. By utilizing cell references, Autofill, and functions, we can streamline our budgeting process and ensure accurate calculations. In the next session, we will delve deeper into more advanced formulas and functions.

FAQ :

How do I create a formula in Excel?

To create a formula in Excel, select a cell, type an equal sign (=), and then enter your formula using cell references and operators. Press Enter to calculate the result.

What is the difference between a formula and a function in Excel?

A formula is a custom calculation created by the user, while a function is a predefined calculation provided by Excel. Functions simplify the process of writing formulas.

How can I use Autofill in Excel?

To use Autofill, select the cell with the formula or data you want to copy, then click and drag the small green handle at the bottom-right corner of the cell to fill adjacent cells.

What does the SUBTOTAL function do?

The SUBTOTAL function calculates a subtotal for a specified range of data, allowing you to choose different types of calculations while ignoring hidden rows.

How can I check if a cell contains a formula?

To check if a cell contains a formula, click on the cell and look at the formula bar. If it shows an expression starting with '=', then it contains a formula.

Quelques cas d'usages :

Budget Tracking

Use Excel formulas to track your budget by calculating the difference between your budgeted and actual expenses. This can help you identify areas where you are over or under budget.

Sales Reporting

In a sales report, use functions like SUM to calculate total sales for different products or regions. This can streamline reporting and provide insights into sales performance.

Data Analysis

Utilize Excel's functions to analyze large datasets, such as calculating averages or totals for specific categories. This can improve decision-making based on data insights.

Inventory Management

Apply formulas to track inventory levels, calculate reorder points, and manage stock efficiently. This can help prevent stockouts and overstock situations.

Project Management

Use Excel to create project timelines and budgets, applying formulas to calculate project costs and timelines. This can enhance project planning and execution.

Glossaire :

Formula

A mathematical expression used in Excel to perform calculations. Formulas can include operators (like +, -, *, /) and cell references.

Cell Reference

The address of a cell in Excel, typically represented by a letter and a number (e.g., A1). Cell references allow formulas to dynamically use the values in those cells.

Autofill

A feature in Excel that allows users to automatically fill a series of cells with data or formulas by dragging the fill handle (a small square at the bottom-right corner of a selected cell).

Function

A predefined formula in Excel that performs a specific calculation using the values provided as arguments. For example, the SUM function adds a range of numbers.

SUBTOTAL

A function in Excel that calculates a subtotal for a range of data, allowing for different types of calculations (like SUM, AVERAGE) while ignoring hidden rows.

Formula Bar

The area in Excel where you can view and edit the contents of the currently selected cell, including formulas.

Total Row

A row in an Excel table that summarizes data, often using functions to calculate totals or averages for specific columns.

00:00:06

help us track our budget as the actual costs begin to come in.

00:00:10

But to make that column work we need to add a formula.

00:00:13

In case you hadn't noticed, Excel has already added some

00:00:16

formulas for us down here in the Total row.

00:00:19

Tables can be very useful when it comes to formulas,

00:00:22

but to really understand how they work let's try creating one on our own.

00:00:27

Select the first cell in the column.

00:00:29

To let Excel know we're entering a formula, type an equal sign.

00:00:34

Now there are a number of ways to write a formula:

00:00:37

we could type the values in each column with a minus sign between them,

00:00:41

and get the correct result,

00:00:44

but then what would happen if one of the numbers changed?

00:00:48

We'd have to rewrite the formula.

00:00:51

To solve that problem, we can use cell references:

00:00:55

instead of typing the value of each cell, we simply type the cell addresses.

00:01:03

To help you keep track of cell references in the formula,

00:01:06

Excel puts a colored highlight on the cell.

00:01:09

Press Enter, and Excel calculates the formula

00:01:13

and displays the result in the cell.

00:01:15

Now because we used cell references, we can change the value,

00:01:20

and Excel automatically updates the total.

00:01:27

Keep in mind that even though the cell shows the result,

00:01:30

the real content of the cell is still the formula.

00:01:33

If you wonder if a cell contains a formula,

00:01:36

click the cell and look in the formula bar.

00:01:40

Also we can use Autofill.

00:01:42

Just click this little green handle in the cell, and drag it down.

00:01:48

And Excel automatically fills the formula to the other cells.

00:01:51

It even updates the cell references to point to the right cells.

00:01:56

Note that negative values are displayed in parentheses.

00:02:01

But for an even easier way to enter formulas, we can use functions.

00:02:05

A function does all the formula writing for you.

00:02:08

As you saw earlier, you can add a function to the Total row

00:02:11

by simply clicking this arrow.

00:02:14

If you select Sum, Excel adds the SUBTOTAL function.

00:02:19

But you can add a function to any cell you want:

00:02:21

select a cell, then go to the Formulas tab on the ribbon;

00:02:26

here you have a whole library of functions.

00:02:29

Click AutoSum, click Sum, and the function is added to the cell.

00:02:35

Now select the cells you want to add

00:02:39

and press Enter.

00:02:43

Click More functions to see all the functions available in Excel.

00:02:48

Up next will do more with formulas and functions.

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