Microsoft excel course for beginner to advanced learners
MODULE 3 / 6 - EXCEL FORMULAS
Lesson 9 - Excel Formula Basics
This tutorial covers the following key topics:
The constituents of an Excel formula: Understand the basic structure of an Excel formula and the different elements that make it up.
- Operators in formulas: Learn about the different types of operators that can be used in Excel formulas and how they work.
- Entering a formula in Excel: Discover how to input formulas in Excel and how to use cell references in your formulas to calculate results based on the data set.
- Editing a formula: Learn how to modify existing formulas to reflect changes in your data set.
- Absolute and relative cell references: Understand the differences between absolute and relative cell references and how to use them in your formulas.
- Copying and pasting formulas in Excel: Learn how to copy and paste formulas to different parts of your worksheet, and how to use this feature to improve your efficiency.
- Formula auditing and debugging: Discover how to use Excel's formula auditing and debugging tools to troubleshoot issues in your formulas and ensure accurate results.
Lesson 10 - Logical Formulas in Excel
In this tutorial, we'll cover the following key topics:
- AND function: Discover how to use the AND function to evaluate multiple conditions and determine if all of them are true.
- OR function: Learn how to use the OR function to evaluate multiple conditions and determine if any of them are true.
- NOT function: Understand how the NOT function can be used to reverse the logical value of a condition.
- TRUE and FALSE functions: Learn about the TRUE and FALSE functions and how they can be used to generate logical values directly in a formula.
- IF function: Discover how to use the IF function to perform conditional tests and return different results based on the outcome.
- IFERROR function: Learn how to use the IFERROR function to handle errors that may occur in your formulas.
- IS function: Understand how to use the IS function to test the type of value in a cell or expression, such as whether it's a number, text, or error.
Lesson 11 - Math Formulas in Excel
In this tutorial, we'll cover the following key topics:
- INT function: Discover how to use the INT function to round a number down to the nearest integer.
- MOD function: Learn how to use the MOD function to find the remainder after dividing two numbers.
- RAND function: Understand how to use the RAND function to generate a random number between 0 and 1.
- RANDBETWEEN function: Discover how to use the RANDBETWEEN function to generate a random number between two specified values.
- ROUND function: Learn how to use the ROUND function to round a number to a specified number of decimal places.
- SUM function: Understand how to use the SUM function to add up a range of numbers.
- SUMIF function: Discover how to use the SUMIF function to add up a range of numbers based on a specified criteria.
- SUMIFS function: Learn how to use the SUMIFS function to add up a range of numbers based on multiple criteria.
- SUMPRODUCT function: Understand how to use the SUMPRODUCT function to multiply corresponding values in two or more arrays and then sum the results
Lesson 12 - Lookup and Reference Formulas in Excel
In this session, we'll cover the following topics:
- VLOOKUP function: Discover how to use the VLOOKUP function to find an exact or approximate match in a vertical table.
- HLOOKUP function: Learn how to use the HLOOKUP function to find an exact or approximate match in a horizontal table.
- INDEX function: Understand how to use the INDEX function to retrieve a value or reference from a specific cell in a range.
- MATCH function: Discover how to use the MATCH function to find the position of a value in a range.
- OFFSET function: Learn how to use the OFFSET function to return a reference to a range that is a specified number of rows and columns from a starting point.
- INDIRECT function: Understand how to use the INDIRECT function to return a reference to a cell or range of cells based on a text string.
- ROW function: Discover how to use the ROW function to return the row number of a specific cell.
- ROWS function: Learn how to use the ROWS function to count the number of rows in a range.
- COLUMN function: Understand how to use the COLUMN function to return the column number of a specific cell.
- COLUMNS function: Discover how to use the COLUMNS function to count the number of columns in a range
Lesson 13 - Stats Formulas in Excel
This Free Excel Training video will teach you about various statistical formulas, including:
- AVERAGE function
- AVERAGEIF function
- AVERAGEIFS function
- COUNT function
- COUNTA function
- COUNTBLANK function
- COUNTIF function
- COUNTIFS function
- LARGE function
- SMALL function
- RANK function
- MAX function
- MIN function
Lesson 14 - Text Formulas in Excel
This Free Excel Training video will teach you about various text formulas, including:
- LEFT function
- RIGHT function
- MID function
- LEN function
- LOWER function
- PROPER function
- UPPER function
- FIND function
- REPLACE function
- SUBSTITUTE function
- TEXT function
Lesson 15 - Date and Time Formulas in Excel
This Free Excel Training video will cover the following DATE and TIME formulas:
- DAY function: Returns the day of the month from a date
- HOUR function: Returns the hour value from a time
- MINUTE function: Returns the minute value from a time
- DATE function: Returns a date from year, month and day values
- DATEVALUE function: Converts a date text string into a date
- TODAY function: Returns the current date
- NOW function: Returns the current date and time
- WEEKDAY function: Returns the day of the week from a date
- NETWORKDAYS function: Returns the number of workdays between two dates
- NETWORKDAYS.INTL function: Returns the number of workdays between two dates with options for holidays and weekend days
- WORKDAY function: Returns the date for a given number of workdays after a specified date
- WORKDAY.INTL function: Returns the date for a given number of workdays after a specified date with options for holidays and weekend days.
Lesson 16 - Formulas Mix and Match (10 Examples)
This Free Excel Training video covers some advanced formula examples that use one or multiple functions to perform complex calculations. The topics covered in this video are:
- Comparing and getting unique lists
- Finding the closest match
- Getting the last value in a list
- Finding the last occurrence of an item in a list
- Performing 2-way and 3-way lookups using Index/Match
- Counting the number of words
- Extracting the username from an email ID
- Finding the first Monday of the month
- Extracting data using a drop-down list.