Writing Excel Functions & Formulas

Course Description

In this comprehensive 6-hour course, delve into the world of Excel formulas and functions, focusing on understanding the fundamentals of creating powerful formulas. You'll explore more than 20 most used functions in Excel, including VLOOKUP, INDEX/MATCH, SUMIF, COUNTIF, and many more. Through hands-on exercises and real-world applications, you'll gain proficiency in crafting efficient and accurate formulas to solve complex problems and streamline your work. By the end of this intensive program, you'll be equipped with the skills to tackle even the most challenging spreadsheet tasks with confidence and precision.

Participant Outcomes

Upon successful completion of this course, you will be able to:

• Understand the fundamental concepts of Excel formulas and functions
• Identify the 20 most-commonly used functions in Excel
• Create complex formulas using nested functions and logical operators
• Utilize lookup functions (VLOOKUP, INDEX/MATCH) to retrieve data efficiently
• Summarize data using various aggregation functions (SUMIF/S, COUNTIF/S, AVERAGEIF/S)
• Perform sample array formulas and dynamic ranges

Course Content

I. Fundamentals of Excel Formulas

• Excel Order of Operations (BEDMAS)
• Understanding precedence of operations
• Evaluating expressions step-by-step
• Common mistakes in formula creation
• Cell References
• Types of cell references: absolute, mixed, relative
• Understanding reference scope
• Practical applications of different reference types

II. Data Manipulation Functions

• Date and Time Functions
• DATE(), NOW(), DATEDIF()
• NETWORKDAYS.INTL() function
• Working with dates in formulas
• Text Functions
• CONCATENATE, RIGHT(), LEFT(), LEN()
• LOWER(), UPPER(), PROPER()
• Text manipulation techniques
• Number Functions
• RANK(), MIN(), MAX(), AVERAGE()
• SUM(), CEILING(), CONVERT()
• Working with numbers in Excel formulas

III. Conditional Statements

• IF Functions
• Basic IF statement
• Multiple conditions with IF statements
• Nested IF statements
• Error Handling and Data Validation
• ISERROR, IFERROR, ISNUMBER, ISBLANK AND ISNONTEXT
• Checking cell contents/types

IV. Conditional Counting and Summing

• Count Functions
• COUNTIF, COUNTIFS
• SUMIF, SUMIFS
• AVERAGEIF, AVERAGEIFS

V. Lookup Functions

• Lookup Functions
• VLOOKUP, HLOOKUP
• INDEX/MATCH combination
• IFERROR for lookup functions