About this course
Welcome to the Excel Intermediate to Advance Training program, where we're set to enhance your Excel skills. Throughout this course, we'll delve into essential topics, exploring practical aspects such as working effectively with text data and date/time data, diving into effective strategies for organizing and managing data across various sheets.
As we progress, we'll unravel the power of text manipulation with Working with Text
Formulas and venture into time-related calculations in Working with Date and Time Formulas.
The course will guide you through creating dynamic formulas with Conditional Formulas, ensuring data integrity with Data Validation, and finally, crafting formulas that efficiently lookup values. And finally, the topic sought after by everyone using Excel, the PivotTable!
By the end of this program, you'll not only master advanced Excel features but also gain the confidence to apply them in real-world scenarios. Join us on this journey toward Excel mastery!
Training objectives
The objectives of this 2-days training program are as follows:
- 1. Enhance participants' understanding of Formula Basics, ensuring they can confidently
construct and manipulate formulas for various calculations in Excel
- 2. Equip participants with the skills to effectively work with Multiple Worksheets, enabling
them to organize, manage, and analyze data seamlessly across different sheets
- 3. Empower participants with advanced techniques in Working with Text Formulas and
Working with Date and Time Formulas, enabling them to manipulate textual data and perform sophisticated calculations involving temporal information
- 4. Enable participants to dynamically adapt formulas with Conditional Formulas, visually
enhance data through Conditional Formatting, ensure data accuracy with Data
Validation, and efficiently retrieve information from various sources through formulas that lookup values
Requirements
- 1. Completed Excel Basic to Intermediate training course
- 2. Minimum Excel version is Microsoft Excel 2016
Module 1: Working with Text Formulas
Working with numbers stored as text.
Are your text data using consistent letter case {uppercase, lowercase, capitalize each word, or sentence case}?
Does your text data contain any extra spaces?
Module 2: Working with Dates and Times
What are the operations you can perform on dates and time?
What are the pre-requisites to perform time operations accurately?
Module 3: Working with Conditional Formulas
How can you teach Excel to make decisions?
How Excel understands logical data?
How to use conditional statement effectively?
Module 4: Creating Formulas That Look Up Values
How you can lookup data efficiently using normal Lookup functions?
What is a better way to search for your data other than the normal Lookup functions?
2 highly efficient methods to search for your data that people rarely know of
Module 5: Data Validation
How can you ensure correct data is entered into your Excel file?
How you can setup your own custom data validations?
What is dependent drop-down list?
How can you create a multi-level dependent drop-down list?
Module 6: Introduction to Power Query Editor
Installing Power Query for Excel 2010/2013 users
Module 7: Pivot Tables & Pivot Charts
What is a PivotTable?
Creating a basic PivotTable
Working with multilevel categorizations in PivotTable
Change how the values are represented in PivotTable
How to use the correct calculation function in PivotTable
Applying conditional formatting in PivotTable
Grouping and ungrouping data in PivotTable
Training Schedule
8:45 Participants registration
9:00 Session starts
10:30 Morning tea break
10:45 Session continues
12:30 Lunch break
1:30 Session continues
3:30 Evening tea break
3:45 Session continues
5:00 Session ends
Programme scope, delivery arrangements and suitability are confirmed during enquiry.