Comprehensive Excel Formula Guide - MS 365 Excel Basics - Lesson 7

Master essential Excel formula concepts in this comprehensive 86-minute video tutorial that transforms beginners into proficient spreadsheet users. Learn fundamental skills including worksheet formulas, keyboard shortcuts, number formatting, and Excel's Golden Rule for calculations. Explore cell references (relative, absolute, and mixed), operators, and order of operations while practicing with the Evaluate Formula Tool. Dive into various formula types covering text, numbers, and logical operations, with special focus on functions like TEXTJOIN, TEXTAFTER, TEXTBEFORE, and XLOOKUP. Gain practical experience building budgets using Dynamic Spilled Array Formulas, working with Excel Tables, and implementing date/time formatting. Master logical tests using SUMIFS, COUNTIFS, IF, AND, OR, and ISNUMBER functions, while understanding error handling and lookup functions. Includes downloadable practice files, detailed PDF notes, and homework exercises for hands-on learning.

Syllabus

1. Introduction
2. Topics
3. Starting a worksheet formula
4. What formula do
5. Keyboard shortcuts are fast
6. How default alignment helps
7. Number Formatting
8. Formulas & Number Formatting
9. Excel's Golden Rule
10. Formula elements
11. Cell References
12. Formula to “List products that each employee sold last week” Relative and Absolute Cell References
13. TEXTJOIN function
14. Conditionally format a row with a logical formula with Mixed Cell References
15. What is a Mixed Cell Reference?
16. Operators and order of operations & Formulas to Illustrate!
17. Evaluate Formula Tool
18. Types of Formulas: Data Type: Text, Number, Logical
19. TEXTAFTER and TEXTBEFORE functions
20. What happens to XLOOKUP when it uses a Text Number to lookup a Number? And how to fix it.
21. Convert text numbers to numbers with Math Operator
22. Types of Formulas: Calculation Type: Single Input-Output, Dynamic Spilled Array, Excel Table Formulas, Scalar Array. WHEN AND WHY TO USE EACH TYPE!!
23. Excel Tables and Excel Table Formulas
24. Build a Budget with Dynamic Spilled Array Formulas
25. Rounding numbers with ROUND, MROUND. CEILING.MATH, and FLOOR.MATH
26. Date and Time Number Formatting and Formulas
27. Modes Of Cell Editing
28. Logical Tests in Formulas: SUMIFS, COUNTIFS, IF, AND, OR, ISNUMBER functions and many more logical formulas, including the three types of formulas where comparative operators are used.
29. Lookup Functions
30. Errors
31. Summary
32. Homework – Practice Problems for you!
33. Closing