Share Description
In this course, you will :
- Begins with hints and keyboard shortcuts to help you work faster with formulas in one or more worksheets.
- covers logical tests with the IF, AND, OR, and NOT functions, searching and retrieving data with lookup functions (VLOOKUP, XLOOKUP, MATCH, and INDEX), analysing data with statistical functions, cleaning up worksheets with text functions, working with array formulas and functions, and mastering date and time calculations.
Syllabus :
1. Formula and Function Tools
- Write formulas using a hierarchy of operators
- Save time with AutoSum, AutoCalc, and extended features
- Absolute, Relative, and Mixed references
2. IF and Related Functions
- Use relational operators and IF logical tests
- Create and expand nested IF functions
- Create compound logical tests using AND and OR functions with IF
3. Lookup and Reference Functions
- Look up information with VLOOKUP, HLOOKUP, and XLOOKUP (365)
- Find approximate matches with XLOOKUP and VLOOKUP (365)
- Find exact matches with XLOOKUP and VLOOKUP (365)
- Extended uses of XLOOKUP (365)
- Retrieve information by location with INDEX
- Identify the presence of data with MATCH and XMATCH (365)
4. Statistical Functions
- Use MEDIAN for middle value, MODE for most frequent
- Tabulate blank cells with COUNTBLANK
- Use COUNT, COUNTA, and the status bar
- Tabulate with COUNTIF, SUMIF, and AVERAGEIF
- Tabulate with COUNTIFS, SUMIFS, and AVERAGEIFS
5. Math Functions
- Decimal rounding with ROUND, ROUNDUP, and ROUNDDOWN
- Other rounding with MROUND, CEILING, and FLOOR
- Generate random values with RAND, RANDBETWEEN, and RANDARRAY (365)
- Bypass errors and hidden data with AGGREGATE
6. Date and Time Functions
- Use dates and times in Excel formulas
- Use TODAY and NOW for dynamic date and time entry
- Identify the day of the week with WEEKDAY
- Working days with NETWORKDAYS and WORKDAY
- Tabulate date differences with DATEDIF
7. Text Functions
- Locate data with FIND and SEARCH
- Extract specific data with LEFT, RIGHT, and MID
- Remove extra spaces with TRIM
- Use ampersand (&), CONCAT, and TEXTJOIN to combine cell data
- Adjust case within cells using the PROPER, UPPER, and LOWER
