Introduction

This course is designed to enhance the skills of individuals with basic Excel knowledge, focusing on composing and applying formulas to efficiently manipulate and analyze data. In response to the growing need for advanced data management and decision-support capabilities in the workplace, this course provides practical training on auditing and correcting formulas, utilizing essential Excel functions—including date and time, text, statistical, and lookup functions and managing complex worksheets.

This course supports the development of data literacy and analytical proficiency, enabling participants to handle large datasets, import data from other software, analyze data tables, and create PivotTables for effective data summarization. This course enables participants to** harness the full potential of Microsoft Excel as a powerful analytical tool that aids decision-makers in deriving informed and strategic outcomes.

Learning Outcome

At the end of the program, participants will be able to:

• create formulas.
• correct formula errors.
• apply advanced Excel Functions such as Conditional Formatting.
• create effective spreadsheets – Work with many worksheets- and large worksheets.
• create more advanced charts and pivot table.
• use an electronic spreadsheet to make useful alternatives to support in making decisions.
• interpret raw data into useful data for decision-makers.

Key Topics

Key topics include:

1. Creating and Auditing Formulas
   • Constructing formulas in Excel
   • Understanding Excel’s data types
   • Auditing formulas in Excel
   • Understanding Excel errors and how to correct them
   • Working with relative reference and absolute reference formulas
2. Creating a Formula Using the Named Range
   • Naming a cell as an alternative to an absolute reference
   • Naming a range of cells
   • Using the names in the formula
   • Editing and deleting the names
   • Using named formulas as an alternative to pasting links
3. Time and Date Functions
   • The concept of date and DATESERIAL in Excel
   • The concept of time in Excel
   • The Date and Time data type
   • Solving problems relating to date and time
   • Calculating future and pasting dates
   • Important Date and Time Functions
4. Logical Functions
   • How and when to use logical functions
   • The IF functions
   • Using nested IF function to solve multiple criteria problems
   • Applying the AND and OR functions
   • Applying the IFS function
5. Statistical Functions
   • The various COUNT functions to detect data irregularity
   • The conditional statistics functions: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, and COUNTIFS
   • Functions to calculate central tendencies.
6. Lookup Functions
   • LOOKUP as an alternative to the IF logic
   • LOOKUP array form and vector form
   • Left lookup using LOOKUP
   • VLOOKUP and HLOOKUP
7. Formatting Data in Excel 
   • Using the preset formatting to format data
   • Creating your custom Number Formats
   • Creating formats for large numbers using prefixes such as “k”, “M” and “G”
   • Insert symbols in number formats
   • Conditional Formatting
8. Working with Many Worksheets
   • Creating multiple windows
   • Tilling the windows
   • Arranging the windows horizontal and vertical
   • Cascading the windows.
9. Working with Large Worksheets
   • Splitting window to show various parts of a worksheet
   • Freezing top rows
   • Freezing left columns
   • Freezing rows and columns.
10. Paste Special Options
    • Using Paste Special to Add, Subtract, Multiply & Divide
    • Using Paste Special ‘Values’
    • Using Paste Special Transpose Option.
11. Charts & Pivot Table
    • Create sparklines
    • Create charts and work with the various chart options
    • Standard charts will be covered: Column chart, Bar chart, pie chart, and line chart
    • Pivot Table

Duration

2 Days | 13 Hours

Target Participant

• Executive Services 2 (ES 2) – Bahagian II: B3 | B2
• Executive Services 3 (ES 3) – Bahagian III: C3 | C2 | C1

Pre-Requisite

Basic knowledge of Microsoft Excel

Language

Bahasa Melayu / English

Methodology

The course will be delivered using:

• Instructor-Led Training
• Hands-On Laboratory
• Demonstration
• Step-By-Step Guides

Assessment Methods

Pre-Test & Post-Test

Program Evaluation