Excel Training: Unlock Your Formula Superpowers
Are you ready to supercharge your Excel skills with top-notch Excel training? Dive into the world of ‘Useful Formulas & Functions’ and discover how to make Excel work for you like never before. Whether you’re an Excel novice or a seasoned pro, this course is designed to take your spreadsheet game to the next level.
What You'll Learn
In this engaging course, you’ll master the art of Excel formulas and functions. From the basics of cell references to advanced functions like VLOOKUP and HLOOKUP, you’ll gain the skills to wrangle data effortlessly. With real-world examples and practical exercises, you’ll learn how to perform statistical calculations, manipulate text, and even work with dates and times like a pro. Our expert instructors will guide you through the world of array and database functions, giving you the tools to tackle complex data with ease.
Why Choose Us
At Info Trek, we’ve been delivering top-notch corporate technology training for over two decades. We’re passionate about learning, innovation, and staying ahead in the fast-paced corporate world. Our Excel training isn’t just about formulas; it’s about empowering you to excel in your professional journey. Join us, and together, we’ll embrace new technology, discover efficient methods, and arm you with the knowledge to excel in the modern workplace.
Unleash the full potential of Excel with ‘Useful Formulas & Functions’—your gateway to becoming an Excel superhero. Sign up today and take the first step towards mastering the art of Excel.
Course Details
Course Duration: 2 Days; Instructor-led
Audience
The target audience for this course is Managers, Assistant Managers, Supervisors, Team Leaders, Executives and Staff who wish to gain the skills and understand the formulas and functions syntax. Learn how do they apply in a nested formulas environment.
Prerequisites
Knowledge of Microsoft Excel (Foundation) is essential with the follow pre-requisites:
- Able to switch between task applications
- Able to create a spreadsheet with simple formatting
- Able to insert and delete Row & Column
- Print a spreadsheet with headers and footers applied
- Insert, Delete and Rename a worksheet
Methodology
This program will be conducted with interactive lectures, PowerPoint presentations, discussions and practical exercises
Course Objectives
After completing this course, participants will be able to:
- Use COUNTIF, SUMIF function
- Use VLOOKUP & HLOOKUP to search for data
- Use the Change Case function
- Calculate a Period of Time Between Two Dates
- Use Array Formulas
- Use Database functions
- Use Auditing Tools to Check for Errors
Outlines
Module 1: Formulas and Functions Basic
Topic A: Formula Referencing
- Using Relative vs Absolute Reference
- Using Mixed Reference
Topic B: Managing Formulas
- Display all formulas for checking
- Trace dependents and Precedents
- Validate Report with Error Checking
- Freeze the value in Formula with Paste Values
Module 2: Conditional Data Summary & Extraction
Topic A: Logical Functions
- Conditional calculation using IF
- Multiple Conditional calculation using IFS
- Compare a value against list using SWITCH
- Multiple Conditional using Nested IF
- Replace Formula error using IFERROR
Topic B: Statistical Functions
- Total Up with SUMIF/ SUMIFS
- No of data with COUNTIF / COUNTIFS
- Averageup with AVERAGEIF / AVERAGEIFS
- Getting Highest value with MAXIFS
- Getting Lowest value with MINIFS
Topic C: Database Function
- DSUM vs SUMIFS
- DCOUNT vs COUNTIFS
- DAVERAGE vs AVERAGEIFS
- DMAX vs MAXIFS
- DMIN vs MINIFS
Module 3: Retrieve, Compare & Compiling Data
Topic A: Lookup Function
- Finding Exact Match with XLOOKUP
- Finding Closest Match with XLOOKUP
- Joining VLOOKUP and MATCH
- Horizontal search with HLOOKUP
- Using XMATCH
Topic B: Multiple Lookup Function
- Vertical and Horizontal lookup using INDEX & MATCH
- Multiple lookups using INDEX & MATCH
Topic C: Organise Data
- Copy an array by single column with SORT
- Copy an array by multiple columns with SORTBY
- Copy an array with FILTER
- Copy an array with UNIQUE
Module 4: Shaping and Clean-up Data
Topic A: Clean-up Data
- Change case with UPPER, LOWER and PROPER
- Convert Text Format into Numbers with VALUE
- Remove unwanted space with TRIM
- Replace data with SUBSTITUTE
Topic B: Shaping Data
- Join Data with CONCAT and TEXT
- Join Data with TEXTJOIN
- Extract required Data with LEFT, RIGHT & MID
Module 5: Setup Duration and Due Date
Topic A: Automated Date and Time
- Setup current date with TODAY
- Setup current date and time with NOW
- Fixed current date using shortcut key
- Fixed current time using shortcut key
Topic B: Duration between dates
- DATEDIF – duration between dates
- INTL – duration to exclude weekend and holidays
Topic C: End Date with Start Date and Duration
- DATE – calculate end date
- INTL – calculate end date to exclude weekend and holidays
