MICROSOFT EXCEL FOR INTERMEDIATE

Course Synopsis

 In this course, students use Excel® 2016/2019 to manage and handle large amount of data in spreadsheet. They will learn to validate data ,apply visual elements and linking to a different worksheet and files.

Course Objectives

Upon successful completion of this course, students will be able to:

• Organize worksheet and table data using various techniques.
• Customize and enhance workbooks and the Microsoft office Excel environment.
• Linking and validating data input and sources

Pre-requisite: Usage of Excel – Basic Level

Method of Instruction: 80 % Hands on training and 20% theory

 

Language : Bahasa Melayu and English

Course Content

 Module 1: Database Management

• Using Form

o   Filtering Data

o   Introduction

• To filter data

o   To apply multiple filters

o   To clear a filter

o   Advanced filtering

o   To use advanced text filters

o   To use advanced number filters

• Sorting Data

o   Introduction

o   Types of sorting

o   To sort a sheet:

o   To sort a range:

o   Custom sorting

o   Sorting levels

• Groups and Subtotals

o   Introduction

o   Creating subtotals

o   To view groups by level:

o   To remove subtotals

 
Module 2: Conditional Formatting

• Understanding conditional formatting
• To create a conditional formatting rule
• Conditional formatting presets
• To use preset conditional formatting:
• Removing conditional formatting

 

Module 3: Link to data in other worksheets and workbooks

• To create a link to a cell or cell range on another worksheet
• To create a link to a cell or cell range in another workbook
• To create a link to cells in an Excel table
• To open the source of a linked value
• To fix a link that returns an error because it references the wrong workbook
• To break a link
• Consolidate Data

 

Module 4: Data Validation

• What is data validation?
• When is data validation useful?
• Data validation messages
• How to handle a data validation alert
• Tips for working with data validation
• Add data validation to a cell or range

  

Module 5: Freezing Panes and View Options

• To freeze rows
• To freeze columns
• To unfreeze panes
• Other view options
• To open a new window for the current workbook

To split a worksheet

Iskandar has been a trainer in the IT industry since 1993 but became fully active in training
since 2000. He had obtained a LCCI Diploma in data processing in 1998 and graduated in
Bachelor in Human Resource Development in 2005 and Master in Human Resource
Development in 2016 from University Putra Malaysia. He too has obtained a professional
certificate in human resource management from OUM in 2013. Since then has been doing
various training for corporate and individuals needs. He is also a certified A+ instructor,
Microsoft Office Associate Powerpoint 2019, Microsoft Office Associate Word 2016,
PowerPoint 2007 & 2010, Access 2007, Microsoft Office user specialist (MOUS) PowerPoint
2000 and full ICDL certified. His areas of expertise includes Office packages and
automations, Web design and Internet, computer maintenance and Google applications.

No. of Days:	1
Total Hours:	8