Master the essentials of managing extensive worksheets, building sophisticated financial models, and working proficiently with financial functions, date manipulation, and logical operations in Excel.
Intended Student Audience:
This course is designed specifically for professionals who are responsible for managing, developing, and creating large, complex worksheets and financial models in Microsoft Excel.
Required Prerequisites for Enrollment:
Completion of Excel Introduction or equivalent practical skills with spreadsheet fundamentals.
Comprehensive Course Outline and Topics:
Working with Workbooks and Sheet Management
- Viewing, renaming, and color-coding worksheets for organization
- Editing a group of sheets simultaneously
- Manipulating worksheets by moving, copying, and deleting
Working with Multiple Worksheets and Workbooks
- Creating linked formulas across different sheets and files
- Using 3-D references for cross-sheet calculations
- Consolidating information by position or by category
- Viewing multiple worksheets simultaneously in split windows
- Viewing worksheets located in different workbooks for comparison
- Using absolute cell references for fixed calculations
- Creating linked formulas between separate workbooks
Understanding Excel Functions and Their Applications
- Creating and using absolute named ranges for clarity and maintenance
- Creating a relative named range for flexible references
- Using custom zoom levels for detailed work
- Working with functions and their syntax
- Working with insert function dialog for formula creation
Working with Financial and Date Functions
- Using financial functions such as NPV, IRR, PMT, and LOAN calculations
- Using date functions for time-based calculations and analysis
- Working with auditing and error-checking tools for formula validation
Working with Logical Functions for Conditional Calculations
- Using the IF function for conditional statements
- Nesting functions to create complex logic
- Using multiple conditions with the IF function for sophisticated conditions
- Nesting IF functions for multi-level decision logic
Working with Lookup and Reference Functions
- Using HLOOKUP and VLOOKUP functions for data retrieval
- Using the MATCH function to locate values
- Using the INDEX function to return values from specific positions
- Nesting the INDEX and MATCH functions for flexible lookups
- Using INDEX MATCH MATCH for two-dimensional lookups
- Using INDEX MATCH and MATCH for advanced data queries
