Excel Vanguard: Cutting-Edge Functions

The Excel Mastery: Data Analysis and Automation course provides participants with comprehensive training on advanced formulas and functions, data validation, data import, and automation using macros. Over two days, participants will learn data analysis, data management, and data modeling through a combination of lectures and hands-on workshops.

OBJECTIVES

• Enhancing Skills in Advanced Formulas and Functions: Participants will learn to use logical functions, lookup and reference functions, text and date functions, financial functions, and essential array formulas for effective data handling in Excel.
• Developing Data Management and Validation Skills: Utilizing data validation tools, creating drop-down lists, and applying conditional formatting to ensure data accuracy and reliability.
• Understanding Pivot Tables and Pivot Charts: Learning to create, customize, and use Pivot Tables and Pivot Charts for efficient data analysis and summarization.
• Improving Data Management Efficiency: Advanced sorting, filtering, and data consolidation techniques to manage large datasets more effectively.
• Enhancing Knowledge in Data Import and Automation: Learning to import data from various sources and using macros for automation tasks.
• Advanced Data Analysis and Data Modeling: Utilizing advanced data analysis tools such as What-If Analysis and Solver Add-in, and using Power Query and Power Pivot for complex data modeling.

WHO SHOULD ATTEND?

• Professionals with basic Excel knowledge looking to advance their skills
• Data analysts, financial analysts, and managers
• Anyone interested in leveraging Excel for complex data analysis and automation tasks

PREREQUISITES

• Basic knowledge of Excel functions and formulas
• Familiarity with Excel interface and basic data manipulation
• Install Excel on the machine ready to use for training.

ALL PARTICIPANTS WILL RECEIVE

• Training manual documents
• Practical exercises and case studies
• Quizzes and assessments
• Certificate of completion Excel Vanguard: Cutting-Edge Functions
• Excellent care and attention from instructors and staff.
• Unlimited attendance to the Excel Vanguard: Cutting-Edge Functions (limited to 5 repeat attendees per training session; if you wish to attend the workshop again, please contact our Facebook Page, Line, Email, or other channels to reserve your seat).
• If you are unable to attend the training session this time, you can reschedule your participation according to the training schedule.

OUTLINE

1. Session 1.1: Advanced Formulas and Functions
   • Logical Functions (IF, AND, OR, NOT)
   • Lookup and Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
2. Session 1.2: Text and Date Functions
   • Text Functions (CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE)
   • Date and Time Functions (TODAY, NOW, DATE, YEAR, MONTH, DAY)
3. Session 1.3: Financial and Array Formulas
   • Financial Functions (PMT, FV, PV)
   • Array Formulas (SUMPRODUCT, TRANSPOSE)
4. Session 2.1: Data Validation and Conditional Formatting
   • Creating Drop-down Lists
   • Applying Data Validation Rules
   • Conditional Formatting (Highlight Cells Rules, Data Bars, Color Scales)
5. Session 2.2: Pivot Tables and Pivot Charts
   • Creating and Customizing Pivot Tables
   • Using Slicers
   • Creating Pivot Charts
6. Session 3.1: Advanced Filtering, Sorting, and Data Management
   • Custom Sorts
   • Advanced Filter Options
   • Using Excel Tables
   • Data Consolidation
7. Session 3.2: Importing Data and Automation with Macros
   • Importing Data from Different Sources (CSV, Web)
   • Introduction to Macros
   • Recording and Running Macros
8. Session 4.1: Advanced Data Analysis Tools
   • What-If Analysis (Data Tables, Scenario Manager, Goal Seek)
   • Solver Add-in (Solving Optimization Problems)
9. Session 4.2: Power Query and Power Pivot
   • Introduction to Power Query
   • Connecting and Transforming Data
   • Introduction to Power Pivot
   • Creating Data Models