Duration: 2 days
Time: 9am to 5pm
What Will Be Taught For This Excel Course?
In this 2 days instructor-led course, participants are empowered on the knowledge and skills on how to manipulate data for analysis reporting that assist in your decision making and evaluation. Participants will learn the process on how data is processed and summarized by incorporating advanced features and functions of Microsoft Excel.
Module 1. Using Advanced Functions
- Using the AVERAGE Function
- Using the MEDIAN Function
- Using the QUARTILE Function
- Using the IF Function
- Using Nested IF Functions
- Using an AND Condition with IF
- Using an OR Condition with IF
- Using the COUNTIF Function
- Using the SUMIF Function
- Using the SUMIFs Function
- Using the SUBTOTAL
- Using the VLOOKUP Function
- Using the IFERROR Function
- Using the ROUND Function
- Using Date Functions
- Day Of Week (DOW)
- Using DATEDIFF Function
- Using Text Functions
Module 2. Using Conditional Formatting
- Applying conditional formatting
- Setting custom criteria in conditional formatting
Module 3. Data cleaning
- Convert text to table
- Remove duplicate
- Fill up blanks cell quickly
- Use find and replace to clean data
- Using TEXT Function (LEFT, RIGHT, CONCATENATE, TRIM, UPPER, LOWER, PROPER)
Module 4. Advanced Filter
- Creating a Criteria Range
- Using a Criteria Range
- Showing All Records
- Using an Advanced AND Condition
- Using an Advanced OR Condition
- Copying Filtered Records
Module 5. Creating Pivot Tables for a General Ledger
- Concepts of PivotTable
- Check your source data for Pivot (remove merge cell, repeat row labels, remove extra columns, remove extra rows)
- Create your PivotTable
- Filter your PivotTable
- Insert Slicers
- Present your PivotTable
Pre-requisites
Learner must be equipped with the following skillset in Excel before attending the course:
- Understand the usage of cell referencing (i.e. $A$1, A$1, $A1)
- Apply basic formula and functions (i.e. SUM, AVERAGE, COUNT, COUNTA, TODAY, NOW)
w/o GST | w GST | |
Course Fee | $380 | $406.60 |
CANCELLATION/POSTPONEMENT/CHANGE of VENUE
- 2 to 4 weeks’ written notice from start date of training - 50% of course fee for cancellation
- Less than 2 weeks’ written notice from start date of training - 100% of course fees for postponement or cancellation
- ST Electronics (e-Services) Pte Ltd reserves the right to cancel or postpone any course or change the venue due to unforeseen circumstances.
Please click on the course date to enrol.
- CL: Classroom Learning
- VILT: Virtual Instructor-Led Training
- GTR: Guaranteed To Run
- Sat: Saturday
- Wkn: Weekend