Microsoft Excel Beginner, Intermediate & Advanced Skills 

training programme

3 DAYS | 21 HOURS TRAINING PROGRAMME

ONLINE OR FACE-TO-FACE TRAINING

(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

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.

 FOR PRICING AND BOOKING THIS COURSE, PLEASE E-MAIL US AT janice@marcnzed.com 

OR CALL +6012 451 4977 (MALAYSIA) OR  +65 9052 3859 (SINGAPORE)


Certificate

Upon successful completion of the course, participants will be awarded a verified digital certificate by Marc & Zed Training Singapore in collaboration with Marc & Zed SPACES Malaysia