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
- 5708 views
-
Excel - Do things quickly with Tell Me
- 1:07
- Viewed 4010 times
-
Excel - New Chart types
- 1:52
- Viewed 5639 times
-
Excel - One click Forecasting
- 1:01
- Viewed 5218 times
-
Excel - 3D Maps
- 1:41
- Viewed 9697 times
-
Excel - Drop-down lists
- 0:55
- Viewed 4228 times
-
Excel - Ink Equation
- 0:41
- Viewed 8353 times
-
Excel - Share documents
- 1:13
- Viewed 4388 times
-
Excel - Improved version history
- 0:51
- Viewed 5021 times
-
Remove a watermark
- 2:20
- Viewed 67685 times
-
Change the default font for your emails
- 1:09
- Viewed 62811 times
-
Collapsible headings
- 3:03
- Viewed 36631 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 30635 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 29759 times
-
Add headers and footers to slides
- 3:52
- Viewed 24808 times
-
Change footnote font, size, and formatting
- 2:48
- Viewed 24593 times
-
Edit headers and footers on the slide master
- 4:56
- Viewed 20931 times
-
Add a sound effect to a transition
- 3:45
- Viewed 20324 times
-
Add sound effects to an animation
- 4:29
- Viewed 19211 times
-
Remove a watermark
- 2:20
- Viewed 67685 times
-
Change the default font for your emails
- 1:09
- Viewed 62811 times
-
Collapsible headings
- 3:03
- Viewed 36631 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 30635 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 29759 times
-
Add headers and footers to slides
- 3:52
- Viewed 24808 times
-
Change footnote font, size, and formatting
- 2:48
- Viewed 24593 times
-
Edit headers and footers on the slide master
- 4:56
- Viewed 20931 times
-
Add a sound effect to a transition
- 3:45
- Viewed 20324 times
-
Add sound effects to an animation
- 4:29
- Viewed 19211 times
-
Preparing for the EU AI Act: Timeline and Obligations
- 01:54
- Viewed 115 times
-
Using AI in the Workplace: 3 Tips to Minimize Risks
- 03:10
- Viewed 116 times
-
Shadow AI: Identifying Uncontrolled AI Use
- 02:21
- Viewed 111 times
-
EU AI Act Risk Levels: From Minimal to Unacceptable
- 02:37
- Viewed 153 times
-
Understanding the EU AI Act: A Regulation Governing the Use of AI
- 02:24
- Viewed 120 times
-
Analyze the performance of a newsletter or an edition in Outlook
- 01:30
- Viewed 161 times
-
Collaborate on writing an edition in Outlook
- 01:35
- Viewed 142 times
-
Enhance an edition and create a template in a newsletter
- 06:14
- Viewed 133 times
-
Collaborate on a Clipchamp Project
- 01:21
- Viewed 437 times
-
Create a Video with AI in Clipchamp
- 03:26
- Viewed 387 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.
Cette formation pourrait intéresser votre entreprise ?
Mandarine Academy vous offre la possibilité d'obtenir des catalogues complets et actualisés, réalisés par nos formateurs experts dans différents domaines pour votre entreprise