Excel - Functions and formulas Tutorial

In this video, you will learn about functions and formulas in Microsoft Excel.
The video covers how to use slicers to filter data, create drop-down lists, sort and filter data, use conditional formatting, create charts, and analyze data using PivotTables.
This will help you become proficient in using functions and formulas to manipulate and analyze data in Excel.

3:24
4672 views

Available in Microsoft365

Objectifs :

This video aims to demonstrate how to use structured references in Excel to perform various calculations, including totals, averages, and revenue calculations, while utilizing filtering and automatic formatting features.

Chapitres :

Introduction to Structured References
In this section, we explore the concept of structured references in Excel. Structured references allow users to refer to table columns by their names rather than traditional cell references, making formulas easier to read and manage.
Calculating Totals and Averages
To calculate totals, check the 'Total Row' box, which adds a total row at the bottom of the table. For example, when calculating the average sale price, click on the total cell under the 'Sale Price' column, select 'Average' from the dropdown list, and Excel will automatically compute the average price.
Counting Entries in a Column
To find out how many stores are listed under the 'Department Store' column, click on the total cell and select 'Count' from the dropdown list. Excel will display the total number of entries, showcasing its ability to manage data efficiently.
Using Filters to Recalculate Data
Excel provides filter buttons under the 'Format as Table' function. By clicking the filter button under the 'Department Store' column, you can uncheck the 'Select All' box, choose specific options, and press 'OK'. Excel will automatically recalculate the data, even if some values are hidden by the filter.
Calculating Sales Revenue Excluding Tax
To calculate sales revenue excluding tax, enter a title in cell J1 and press 'Enter' to format the column. Start the formula with an equal sign, click on cell H2, and use the structured reference for 'Sale Price'. Multiply it by the structured reference for 'Quantity' and press 'Enter'. Excel will copy the formula down the column automatically.
Calculating Sales Revenue Including Tax
For sales revenue including tax, enter a title in cell K1 and press 'Enter'. Begin the formula with an equal sign, click on cell J2 to reference the sales revenue excluding tax, and complete the formula. Press 'Enter' to have Excel copy the formula down the column.
Adding Rows to the Table
To add additional rows to the table, click on the last cell of the table and press the 'Tab' key. This action creates a new row where you can input reference information, sales price, and quantity. Once you press 'OK', Excel will automatically calculate the average sale price, quantities, and both sales revenues.
Conclusion
This video illustrates the power of structured references in Excel for performing various calculations efficiently. By utilizing features like automatic formatting, filtering, and formula copying, users can save time and enhance their data management capabilities.

FAQ :

What is a structured reference in Excel?

A structured reference in Excel allows users to refer to table data using the table and column names instead of traditional cell references, making formulas easier to read and understand.

How do I calculate the average in Excel?

To calculate the average in Excel, you can use the total row feature. Click on the total cell, select 'Average' from the drop-down list, and Excel will automatically calculate the average for the selected column.

What does the count function do in Excel?

The count function in Excel counts the number of cells in a specified range that contain numbers or meet certain criteria, providing a quick way to tally data.

How can I filter data in Excel?

To filter data in Excel, click on the filter button in the column header, uncheck 'Select All', choose the specific options you want to display, and press OK. Excel will then recalculate the data based on your selection.

What is the difference between sales revenue tax excluded and tax included?

Sales revenue tax excluded refers to the total revenue generated from sales before any taxes are applied, while sales revenue tax included refers to the total revenue after taxes have been added.

How do I add a new row to an Excel table?

To add a new row to an Excel table, click on the last cell of the table and press the Tab key on your keyboard. This will create an additional row where you can input new data.

Quelques cas d'usages :

Sales Data Analysis

Using structured references and the total row feature, sales analysts can quickly calculate average sale prices and total counts of stores, allowing for efficient data analysis and reporting.

Inventory Management

Retail managers can utilize Excel's filtering capabilities to analyze inventory levels by department, ensuring they maintain optimal stock levels and make informed purchasing decisions.

Financial Reporting

Accountants can apply the subtotal function to generate detailed financial reports, providing insights into revenue streams both with and without tax, which aids in compliance and strategic planning.

Budget Planning

Budget analysts can use Excel to calculate projected sales revenue tax excluded and included, helping organizations forecast their financial performance and allocate resources effectively.

Sales Forecasting

Sales teams can leverage Excel's average and count functions to analyze historical sales data, enabling them to make accurate forecasts and set realistic sales targets.

Glossaire :

Structured Reference

A method in Excel that allows users to refer to table data by using the table and column names instead of traditional cell references. This makes formulas easier to read and understand.

Total Row

A feature in Excel tables that provides summary calculations (like sum, average, count) for each column in the last row of the table.

Drop Down List

A user interface element that allows users to select an option from a list of choices. In Excel, this is often used for selecting functions in the total row.

Average

A statistical measure that represents the central or typical value in a set of data, calculated by dividing the sum of all values by the number of values.

Count

A function in Excel that counts the number of cells in a range that contain numbers or meet a specified condition.

Filter

A feature in Excel that allows users to display only the rows that meet certain criteria, hiding the rest of the data.

Subtotal Function

A function in Excel that calculates subtotals for a range of data, allowing for more detailed analysis of grouped data.

Sales Revenue Tax Excluded

The total revenue generated from sales before any taxes are applied.

Sales Revenue Tax Included

The total revenue generated from sales after including applicable taxes.

Formula Bar

An area in Excel where users can enter or edit data and formulas for the selected cell.

00:00:06

used to calculate data in a great many ways.

00:00:08

when I check the total row box,

00:00:10

the total appears in the last column.

00:00:13

When I click on the total amount

00:00:15

263 a drop down list button appears

00:00:17

I can select any function. I want.

00:00:20

Instead of the expected cell

00:00:22

reference I-22 to 119 Excel displays.

00:00:25

The column header in the formula bar.

00:00:28

This is what is referred as

00:00:30

a structured reference.

00:00:32

To calculate the average sale price under

00:00:34

the sale price column and at the bottom.

00:00:36

I click on the total cell.

00:00:39

From the drop down list.

00:00:41

I select average,

00:00:42

the average prices then

00:00:44

automatically calculated.

00:00:45

Under the Department store column.

00:00:47

I want to know how many stores we have.

00:00:50

I click on the total cell then select

00:00:53

count from the drop down list.

00:00:55

Excel then shows the result.

00:00:57

Under the format as table function the

00:00:59

column header show the filter buttons

00:01:02

automatically under the Department store.

00:01:04

I click on the filter button and Uncheck.

00:01:07

The select all box.

00:01:09

I select an option and press OK and all

00:01:12

columns are automatically re calculated.

00:01:15

Despite some values being hidden

00:01:17

by the filter Excel.

00:01:18

Manage is to recalculate the

00:01:20

data via the subtotal function.

00:01:23

Code 103 refers to the counter function.

00:01:26

And code 109 relates to the sum function.

00:01:30

Under the help tab you can obtain the

00:01:32

list function numb reference codes.

00:01:34

Now I want to figure out the

00:01:36

sales revenue tax excluded.

00:01:38

I click on the J1 cell and

00:01:40

enter the column title.

00:01:42

I then press enter the column

00:01:45

is automatically formatted.

00:01:47

To calculate the sales revenue tax excluded.

00:01:49

I type in the equal sign and then I click

00:01:52

on the H2 cell under the formula bar.

00:01:55

The structured reference sale price

00:01:57

appears instead of the cell reference.

00:01:59

I type in the star sign and

00:02:01

click on the cell H2.

00:02:03

Here again the structured reference

00:02:06

quantity appears I then press enter.

00:02:09

Excel automatically copies over the

00:02:11

formula down to the columns last cell.

00:02:13

This is saving us a tremendous

00:02:15

amount of time.

00:02:17

Under column K I'm going to calculate my

00:02:20

sales revenue tax included I click on

00:02:23

the K1 cell and enter the column title.

00:02:27

And then press enter Excel

00:02:29

automatically formats,

00:02:30

the entire column.

00:02:31

I start the formula by typing in

00:02:33

the equal sign and then I click

00:02:36

on the J2 cell under the formula

00:02:38

bar the structured reference sales

00:02:41

revenue tax excluded appears instead

00:02:43

of the cell reference.

00:02:44

I finished my formula and press enter.

00:02:47

Excel automatically copies over the

00:02:50

formula down to the columns last cell.

00:02:53

To add 1 or more rose to the table.

00:02:55

I click on the last table cell and

00:02:58

press the tab key on my keyboard

00:03:00

and additional row was created.

00:03:02

I type in the reference and

00:03:04

other additional information.

00:03:06

When I input the sales price and quantity.

00:03:08

I then press OK.

00:03:10

Excel automatically calculates the average

00:03:12

sale price and ants the quantities.

00:03:15

The sales revenue tax excluded

00:03:17

and the sales revenue tax

00:03:18

included are automatically

00:03:20

calculated as well.

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