Effective Use of Excel for Reporting

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.