Excel for Mac: Advanced Formulas and Functions (365/2019)

Course details

Looking to master all the formulas and functions available in Excel for Mac? In this comprehensive course, Dennis Taylor presents numerous Excel formulas and functions and shows how to use them efficiently. Dennis begins with tips and keyboard shortcuts to accelerate the way you work with formulas within one or multiple worksheets. He then covers how to perform logical tests with the IF, AND, OR, and NOT functions, search and retrieve data with lookup functions (VLOOKUP, XLOOKUP, MATCH, and INDEX), analyze data with statistical functions, use text functions to clean up worksheets, work with array formulas and functions, and master date and time calculations. If you’re looking to up your formulas and functions skills in Excel, join Dennis as he uses practical examples that transition effortlessly to real-world scenarios.

Concepts

0. Introduction

01 - Master the formulas and functions of Excel for Mac

1. Formula and Function Tools

02 - Write formulas using a hierarchy of operators
03 - Save time with AutoSum, AutoCalc, and extended features
04 - Absolute, Relative, and Mixed references

2. IF and Related Functions

05 - Use relational operators and IF logical tests
06 - Create and expand nested IF functions
07 - Create compound logical tests using AND and OR functions with IF

3. Lookup and Reference Functions

08 - Look up information with VLOOKUP, HLOOKUP, and XLOOKUP (365)
09 - Find approximate matches with XLOOKUP and VLOOKUP (365)
10 - Find exact matches with XLOOKUP and VLOOKUP (365)
11 - Extended uses of XLOOKUP (365)
12 - Retrieve information by location with INDEX
13 - Identify the presence of data with MATCH and XMATCH (365)

4. Statistical Functions

14 - Use MEDIAN for middle value, MODE for most frequent
15 - Tabulate blank cells with COUNTBLANK
16 - Use COUNT, COUNTA, and the status bar
17 - Tabulate with COUNTIF, SUMIF, and AVERAGEIF
18 - Tabulate with COUNTIFS, SUMIFS, and AVERAGEIFS

5. Math Functions

19 - Decimal rounding with ROUND, ROUNDUP, and ROUNDDOWN
20 - Other rounding with MROUND, CEILING, and FLOOR
21 - Generate random values with RAND, RANDBETWEEN, and RANDARRAY (365)
22 - Bypass errors and hidden data with AGGREGATE

6. Date and Time Functions

23 - Use dates and times in Excel formulas
24 - Use TODAY and NOW for dynamic date and time entry
25 - Identify the day of the week with WEEKDAY
26 - Working days with NETWORKDAYS and WORKDAY
27 - Tabulate date differences with DATEDIF

7. Text Functions

28 - Locate data with FIND and SEARCH
29 - Extract specific data with LEFT, RIGHT, and MID
30 - Remove extra spaces with TRIM
31 - Use ampersand (&), CONCAT, and TEXTJOIN to combine cell data
32 - Adjust case within cells using the PROPER, UPPER, and LOWER

Conclusion

33 - Continue your Excel journey