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)

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