Platform Banner

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
4053 views

Available in Microsoft365

Description

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.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

E-learning Débutant

Vu 2945 fois

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 introductory Excel tutorials combine simplicity and efficiency!

ALL COURSES