Platform Banner

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

Available in Microsoft365

Description

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.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

Vu 2907 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