Highlight 01
Practical exercises and case studies
Fast replySignature workshopLast updated: Jul 5, 2024, 09:00
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.
No schedule is currently available for the public training course.
Need a private session or special arrangement? Contact us.
Why This Course
Highlight 02
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).
Highlight 03
If you are unable to attend the training session this time, you can reschedule your participation according to the training schedule.
Why This Course
Why this course is worth learning
A quick overview of the key reasons this course works well for learners who want practical, job-ready skills.
Benefit 01
Hands-on by design
The course moves from principles to practical execution, so learners can apply it immediately after class.
Benefit 02
Structured for working teams
Topics are sequenced to reduce cognitive overload and help teams identify the most important takeaways quickly.
Benefit 03
A clearer decision path
Consultation and registration are placed at the right moments, creating a more confident conversion path.
Learning Outcomes
OBJECTIVES
A clear summary of what you will understand and be able to apply after the course.
6 learning goals
- 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.
Fit & Preparation
Who should join, and what should they prepare beforehand
See at a glance who this course is for and what background is recommended before joining.
Curriculum
OUTLINE
The curriculum is organized into modules so you can scan the overall structure first, then open the details you care about.
9 modules
01Session 1.1: Advanced Formulas and Functions
2 topics inside this module
View details
01
Session 1.1: Advanced Formulas and Functions
2 topics inside this module
- Logical Functions (IF, AND, OR, NOT)
- Lookup and Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
02Session 1.2: Text and Date Functions
2 topics inside this module
View details
02
Session 1.2: Text and Date Functions
2 topics inside this module
- Text Functions (CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE)
- Date and Time Functions (TODAY, NOW, DATE, YEAR, MONTH, DAY)
03Session 1.3: Financial and Array Formulas
2 topics inside this module
View details
03
Session 1.3: Financial and Array Formulas
2 topics inside this module
- Financial Functions (PMT, FV, PV)
- Array Formulas (SUMPRODUCT, TRANSPOSE)
04Session 2.1: Data Validation and Conditional Formatting
3 topics inside this module
View details
04
Session 2.1: Data Validation and Conditional Formatting
3 topics inside this module
- Creating Drop-down Lists
- Applying Data Validation Rules
- Conditional Formatting (Highlight Cells Rules, Data Bars, Color Scales)
05Session 2.2: Pivot Tables and Pivot Charts
3 topics inside this module
View details
05
Session 2.2: Pivot Tables and Pivot Charts
3 topics inside this module
- Creating and Customizing Pivot Tables
- Using Slicers
- Creating Pivot Charts
06Session 3.1: Advanced Filtering, Sorting, and Data Management
4 topics inside this module
View details
06
Session 3.1: Advanced Filtering, Sorting, and Data Management
4 topics inside this module
- Custom Sorts
- Advanced Filter Options
- Using Excel Tables
- Data Consolidation
07Session 3.2: Importing Data and Automation with Macros
3 topics inside this module
View details
07
Session 3.2: Importing Data and Automation with Macros
3 topics inside this module
- Importing Data from Different Sources (CSV, Web)
- Introduction to Macros
- Recording and Running Macros
08Session 4.1: Advanced Data Analysis Tools
2 topics inside this module
View details
08
Session 4.1: Advanced Data Analysis Tools
2 topics inside this module
- What-If Analysis (Data Tables, Scenario Manager, Goal Seek)
- Solver Add-in (Solving Optimization Problems)
09Session 4.2: Power Query and Power Pivot
4 topics inside this module
View details
09
Session 4.2: Power Query and Power Pivot
4 topics inside this module
- Introduction to Power Query
- Connecting and Transforming Data
- Introduction to Power Pivot
- Creating Data Models
Schedule & Registration
Public Training Schedule
Review upcoming sessions, key logistics, and registration details in one place.
Available Sessions
No schedule is currently available for the public training course.
Need a private session or special arrangement? Contact us.
Documents