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
- 4901 views
-
Excel - Use slicers to filter data
- 1:25
- Viewed 4532 times
-
Excel - A closer look at the ribbon
- 3:55
- Viewed 5402 times
-
Excel - Create a PivotTable and analyze your data
- 1:35
- Viewed 4847 times
-
Excel - Microsoft Search
- 0:34
- Viewed 3954 times
-
Excel - Create a PivotTable report manually
- 4:59
- Viewed 4845 times
-
Excel - Sort, filter, summarize and calculate your PivoteTable data
- 3:49
- Viewed 5190 times
-
Excel - How to create a table
- 2:11
- Viewed 4201 times
-
Excel - Introduction to Excel
- 0:59
- Viewed 4593 times
-
Remove a watermark
- 2:20
- Viewed 39989 times
-
Change the default font for your emails
- 1:09
- Viewed 23621 times
-
Collapsible headings
- 3:03
- Viewed 20301 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 20259 times
-
Activate the features of Teams Premium
- 3:48
- Viewed 19713 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 16966 times
-
Create automatic reminders
- 4:10
- Viewed 13136 times
-
Protect a document shared by password
- 1:41
- Viewed 12057 times
-
Morph transition
- 0:43
- Viewed 11231 times
-
Add a sound effect to a transition
- 3:45
- Viewed 10732 times
-
Remove a watermark
- 2:20
- Viewed 39989 times
-
Change the default font for your emails
- 1:09
- Viewed 23621 times
-
Collapsible headings
- 3:03
- Viewed 20301 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 20259 times
-
Activate the features of Teams Premium
- 3:48
- Viewed 19713 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 16966 times
-
Create automatic reminders
- 4:10
- Viewed 13136 times
-
Protect a document shared by password
- 1:41
- Viewed 12057 times
-
Morph transition
- 0:43
- Viewed 11231 times
-
Add a sound effect to a transition
- 3:45
- Viewed 10732 times
-
Block the transfer of a Teams meeting
- 02:40
- Viewed 20 times
-
Control the start of recording and transcription in Teams
- 03:03
- Viewed 22 times
-
Manage access to recordings and transcripts in Teams
- 02:59
- Viewed 19 times
-
Enable voice isolation in Teams
- 02:14
- Viewed 32 times
-
Add a collaborative page to a Teams channel
- 03:06
- Viewed 18 times
-
Manage a channel’s files with the Shared tab in Teams
- 03:34
- Viewed 29 times
-
Track conversations and organize channels in Teams
- 03:26
- Viewed 20 times
-
Create a team and set up the first channel in Teams
- 03:02
- Viewed 21 times
-
Create a newsletter in Outlook
- 02:23
- Viewed 56 times
-
Create an issue in an Outlook newsletter
- 03:27
- Viewed 60 times
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.