Microsoft Excel
Effective Use of Excel for Reporting

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)

Terms & Conditions

• All prices stated above are in Singapore Dollars (SGD).
• ST Electronics (e-Services) reserves the right to change the date or venue without prior notice.
• ST Electronics (e-Services) reserves the right to cancel or reschedule the course due to class size or unforeseen circumstances.