Excel 2007: Advanced Formulas and Functions

Introduction

• Learning advanced formulas and functions using Excel
• Developing your own style with formulas and functions
• Challenges

1. Using Tables and Dynamic Arrays for Data Integrity and Consistency

• Tables
• Tables and absolute cell references
• Dynamic arrays introduction

2. The World of IF Statements and Conditions

• IF function
• SUMIFS and COUNTIFS
• MAXIFS, MINIFS, and AVERAGEIFS

3. Looking Up, Down, and All Around: Comparing and Combining with Lookups

• VLOOKUP
• XLOOKUP
• VLOOKUP and XLOOKUP comparison
• INDEX/MATCH
• The INDEX/MATCH vs. VLOOKUP controversy
• Two-way lookups
• Approximate and tiered matches
• INDIRECT

4. Formula Tips and Strategies

• Use Alt+Enter to make formulas more readable
• Formula vs. lookup table
• Formula vs. helper columns
• Build complex formulas in steps
• Writing formulas for "future you"
• Compatibility functions
• Writing 3D formulas
• Volatile functions
• LET function overview
• Error handling: IFNA and IFERROR

5. Mid-Term Challenges

• Challenge 1: Course completions
• Challenge 2: Two-way lookup
• Challenge 3: Guitars

6. Date and Time Functions

• Time, rounding, and converting to decimals
• EOMONTH
• YEARFRAC

7. Working with Text and Arrays

• LEFT, RIGHT, and MID
• UPPER, LOWER, and PROPER
• TEXTJOIN
• FILTER
• UNIQUE
• TOCOL
• TEXTBEFORE and TEXTAFTER
• RANDARRY

8. Statistical Functions

• LARGE and SMALL
• MEDIAN and MODE
• FACT
• COMBIN COMBINA

9. Math Functions

• Rounding
• MROUND, CEILING, and FLOOR
• MOD

10. Wild Cards

• Wildcards
• XLOOKUP with wildcards

11. New, Handy, and Fun Functions

• ROMAN and ARABIC
• IMAGE
• CHAR and CODE

12. Final Challenges

• Challenge 1: Towers
• Challenge 2: Donations
• Challenge 3: Assignment
• Challenge 4: Course order

Conclusion

• Take your Excel skills to the next level