(THIS IS A 3 DAYs / 21 HOURS COURSE)

Note: Client may select either the entire programme or specific 1 day or 2 days, based on skills requirement

Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualisation and analysis tool. It is a powerful tool that can help you organise, calculate, analyse, revise, update, and present your data much easier and in less time. To achieve these skills, the courses is divided into three levels which is for beginners, intermediate and advanced.

You will learn both the the software and the online version(Microsoft 365) of Microsoft Excel.

OBJECTIVES

1. Practise useful tips in Excel spreadsheet and create charts, works with formulas & functions, filtering, remove duplications, pivot table, logical and lookup function. 

2. Understand what is dashboard so that the participants can conduct analysis using historical data,
   clean data and complete analysis report in shorter time as well as to build fast statistical and interactive report. 

3. To create and use Pivot table incorporating slicers to filter report. Aside that the participant
   can update data source, group, filter & sort data within Pivot table.
   
   

WHO SHOULD ATTEND THIS COURSE

Anyone who is new or have some basic knowledge of Microsoft Excel or interested in learning and using advanced functions of Microsoft Excel in their daily work.

METHODOLOGY

This is a hands-on, practical-based course. Trainees will be required to follow along with immersive practical training and exercises during the class.

COURSE OUTLINE

COURSE DELIVERY TIMES WILL BE BASED ON THE BELOW (UNLESS STATED OTHERWISE):

Time: 9.00 am- 5.00 pm  (Break: 10.30am-10.45am/ 3.30pm-3.45pm Lunch: 12.30pm-1.30pm)

BEGINNER LEVEL

Duration : 1 Day 

MODULE 1 - GETTING STARTED

Lesson 1.1 - About Workbooks

Creating a New Workbook

Opening a Workbook

Saving a Workbook

About Excel File Types

Closing a Workbook

Lesson 1.2 - Exploring your Workbook

Using Worksheets 

The Active Cell 

Selecting Cells 

Exploring a Worksheet

Using Zoom

MODULE 2 - THE EXCEL INTERFACE

Lesson 2.1 - The Quick Access Toolbar and File Menu 

The Default QAT Commands

Adding Commands

Removing Commands

Customising the Toolbar

Using the File (Backstage) Menu 

MODULE 3 - EXCEL BASICS

Lesson 3.1 - Working with Excel

Columns, Rows, Cells, and Ranges

Creating Worksheet Labels

Entering and Deleting Data

Lesson 3.2 - Basic Excel Features

AutoFill

AutoSum

AutoComplete

Working with Basic Formula

Lesson 3.3 - Moving your Data

Dragging and Dropping Cells

How to Cut, Copy, and Paste Cells

How to Cut, Copy, and Paste Multiple Cells

Using Paste Special

Inserting and Deleting Cells, Rows, and Columns

Using Undo, Redo, and Repeat

MODULE 4 - EDITING YOUR WORKBOOK

Lesson 4.1 - Modifying Cells and Data

Changing the Size of Rows or Columns

Adjusting Cell Alignment

Rotating Text

Creating Custom Number and Date Formats

Lesson 4.2 - Cell Formatting 

The Format Painter

Cell Merging and AutoFit

Lesson 4.3 - Enhancing a Worksheet’s Appearance

Adding Patterns and Colours

Adding Borders

Working with Styles

Working with Themes

Lesson 4.4 - Working with Charts, Part 1

Creating a Chart

Styling Charts with the Design Tab

Modifying Charts with the Layout Tab

Manipulating a Chart

MODULE 5 - PRINTING AND VIEWING YOUR WORKBOOK

Lesson 5.1 - Using the View Tab

Using Normal View

Using Full Screen View

Using Page Layout View

Page Break Preview

Lesson 5.2 - Printing your Workbook

Print Commands

Print Preview

Using Basic Print Options

Other Print Options

INTERMEDIATE LEVEL 

Duration: 1 Day 

MODULE 1 - WORKING WITH FUNCTIONS AND FORMULAS

Lesson 1.1 - Using Formulas in Excel 

Understanding Relative and Absolute Cell References

Understanding Basic Mathematical Operators 

Lesson 1.2 - Exploring Excel Functions

What are Functions?

Finding the Right Functions

Inserting Functions

Some Useful and Simple Functions

Lesson 1.3 - Using Functions in Excel

Working with Nested Functions

Using the IF Function

Nested IF Functions

Text, date & time functions

MODULE 2 - MANAGING TABLES 

Lesson 2.1 - Working with Tables

What is a Table?

Creating Tables

Modifying Tables

Using the Table Tools – Design Tab

Adding a Total Row 

Lesson 2.2 - Working with Tables and Filters

Sorting Data in a Table

What is an AutoFilter?

Creating a Custom AutoFilter

Using an Advanced Filter

Using an Advanced Filter with Logical Statements

Copying Filtered Records

MODULE 3 - SHOWING DATA AS A GRAPHIC

Lesson 3.1 - Adding Sparklines 

What are Sparklines?

Adding Sparklines

About the Sparkline Tools Tab

Editing Sparkline Data

ADVANCED LEVEL 

Duration: 1 Day 

MODULE 1 - GETTING THE MOST FROM YOUR DATA

Lesson 1.1 - Exploring Scenarios 

What is a Scenario?

Creating a Scenario

Saving Multiple Scenarios

Creating a Scenario Summary Report

Lesson 1.2 - Using Data Analysis Tools

Using a One or Two Input Data Table

Using Goal Seek 

Lesson 1.3 - Using Solver

Enabling Data Analysis Tools

Understanding Solver

Generating Reports and Scenarios with Solver

Changing Solver Values

Managing Solver Constraints

Choosing a Solving Method

Using Solver as a Goal Seek Tool

Lesson 1.4 - Excel and Hyperlinks

What is a Hyperlink?

Inserting Hyperlinks

Editing Hyperlinks

Formatting Hyperlinks

Using Hyperlinks in Excel 

MODULE 2 - PIVOTING DATA

Lesson 2.1 - Getting Started with PivotTables

What is a PivotTable?

Creating a PivotTable

Using the PivotTable Tools Tabs

Adding and Removing Data with the Field List

Changing the Field List Layout

Pivoting Data

Lesson 2.2 - Working with PivotTable Data 

Expanding and Collapsing Data

Filtering Data

Sorting Data

Grouping Data

Refreshing Data

Editing the Data Source 

Lesson 2.3 - Formatting a PivotTable

Modifying Fields and Labels

Modifying Values

Using the Layout Group on the Design Tab

Applying a Style to a PivotTable

Changing PivotTable Style Options

Manually Formatting a PivotTable

Using the PivotTable Options Dialog

Lesson 2.4 - Advanced PivotTable Tasks

Creating a Slicer

Using the Slicer Tools Tab

MODULE 3 - ADVANCED EXCEL TASKS

Lesson 3.1 - Using the VLOOKUP Function

Understanding VLOOKUP and HLOOKUP

Using VLOOKUP to Find Data

How to Find an Exact Match with VLOOKUP

Finding an Approximate Match with VLOOKUP

Using VLOOKUP as an Array Formula

Lesson 3.2 - Linking, Consolidating, and Combining Data

Linking Workbooks

Consolidating Workbooks

Combining Worksheets

Pivoting Consolidated Data

MODULE 4 - WORKING WITH MACROS AND CONTROLS

Lessons 4.1: Creating Macros in Excel

What Is a Macro?

Creating a Macro

Playing a Macro

Assigning A Shortcut Key to Macro

Macros, Buttons And Toolbars

YOUR TRAINER

Dr. Khairul Anuar is a well experienced trainer and has 17 years of work experience as a trainer/lecturer/senior researcher and project engineer. He attained his PhD in Engineering from Universiti Science Malaysia. He has used Microsoft Excel entensively including in specific engineering field of Design of Experiments (DOE). 

A HRDC Certified Trainer, he has been a lead trainer with Marc & Zed SPACES since July 2022.