Master advanced Excel functions, including VLOOKUP and PivotTables, learn complex formulas, and develop professional-level techniques from experienced instructors. Progress from beginner to skilled professional by summarizing data with PivotTables, writing advanced formulas including VLOOKUP and XLOOKUP, and mastering techniques that accelerate your workflow. Learn to split and join text, create dropdown menus, and sort and filter data effectively. Apply statistical formulas such as COUNTIFS and SUMIFS to gain deeper insights into your data. Upon completing this class, you'll command powerful techniques that significantly speed up task completion.
Prerequisite:
You must have beginner-level Excel skills equivalent to our Excel Fundamentals course, including basic functions and formulas, printing, formatting, basic charts, and tables.
What You'll Learn at a Glance:
- Excel navigation tricks and keyboard shortcuts
- Summarizing and analyzing data with PivotTables
- VLOOKUP and XLOOKUP lookup functions
- Splitting and joining text data
- Creating dropdown menus for data selection
- Sorting and filtering data efficiently
- Workflow acceleration techniques
- Logical functions: IF, AND, OR operators
Course Syllabus:
- Worksheet Management
- Efficient worksheet navigation
- Formula review and concepts
- Working with Text
- Splitting text into separate columns
- Joining text from multiple cells
- Cell Ranges and Functions
- Using Paste Special options
- Pasting special values only
- Creating and using named ranges
- Database Functions
- VLOOKUP and XLOOKUP applications
- Sorting data effectively
- Filtering data for analysis
- PivotTables
- Creating PivotTables from data
- Grouping data in PivotTables
- Using multiple PivotTables
- Logical Functions
- IF conditional statements
- AND and OR logical operators
- Math Functions
- SUBTOTAL function applications
- Statistical Functions
- SUMIFS for conditional summing
- COUNTIFS for conditional counting
- Improving Data Quality
- Data validation rules
- Removing duplicate records
- Advanced Charts
- End of Class Project
- Comprehensive capstone project
Learn more about Intermediate Excel for Business at NYIM Training.
