In this intermediate Excel course, you will master advanced functions including LOOKUPS, Date and Time Functions, Text Functions, Statistical Functions, and Financial Functions. You will gain expertise in importing and exporting data using the Power Pivot Data model and performing sophisticated data analysis using What-If Analysis, Scenarios, and Goal Seeker. Additionally, you will learn to streamline processes using Macros and gather user information through Forms.
What's Included:
- Comprehensive Excel training manual
- Certificate of course completion
- Small class sizes ensure personalized attention
- FREE repeat valid for 6 months
DETAILED COURSE OUTLINE
Logical and Lookup Functions
- Mastering the IF Function
- Performing Conditional Calculations
- Utilizing SUMIF for Advanced Calculations
- Implementing VLOOKUPS and HLOOKUPS
- Working with Range LOOKUPS
Advanced Formulas
- Reviewing Formulas and Error Detection
- Setting Formula Parameters
- Using Array Functions
- Employing TRANSPOSE Functions
Special Functions
- Date and Time Functions
- Utilizing TODAY, NOW, and DATE Functions
- Performing Date Calculations
- Text Functions
- Using CONCATENATE and TRIM Functions
- Applying UPPER, LOWER, and PROPER Functions
- Extracting Text from Strings
- Statistical Functions
- Using MIN and MAX Functions
- Using COUNT, COUNTA, and COUNTBLANK Functions
- Financial Functions
- Using FV for Future Value Calculations
- Using PMT for Payment Calculations
Importing and Exporting
- Leveraging the Power Pivot Data Model
- Importing Data from Multiple Sources
- Using Power Pivot for Data Analysis
- Exporting Data to External Formats
Data Analysis
- Performing What-If Analysis
- Creating and Managing Scenarios
- Using Goal Seek for Reverse Calculations
- Applying the Analysis Toolpak
Macros and Forms
- Recording Macros for Workflow Automation
- Running Macros
- Running Macros when the Workbook is Opened or Closed
- Using Forms to Collect and Record User Input
