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
Practise useful tips in Excel spreadsheet and create charts, works with formulas & functions, filtering, remove duplications, pivot table, logical and lookup function.
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.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.
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