MICROSOFT EXCEL – FOUNDATION

Duration: 1 Day

AUDIENCE

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

PREREQUISITES

REQUIRED PREREQUISITES:

Basic knowledge of Windows is essential with the follow pre-requisites:

• Able to maneuver with the mouse – Point, Click, Drag and Double Click
• Able to configure the desktop
• Able to use the Control Panel
• Able to move and re-size Application Windows
• Able to switch between task applications

METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

COURSE OBJECTIVES

• Give participant a systematic understanding of a spreadsheet ad its benefits.
• Equip participant with essential skills of effectively utilizing spreadsheet software in a day-to-day business environment.

Upon completion of this program, participants should be able to:

• Getting Started with Microsoft Excel 2019, Identify the Elements, and Interface of Excel.
• Performing Calculation, Basic Formula and Functions.
• Modify Worksheet & Formatting Worksheet.
• Printing and managing large workbook.

Module 1 – Getting Started with Microsoft Excel 2019

Topic A – Identify the Elements of the Excel Interface

• Microsoft Excel 2019
• What is Spreadsheet, Worksheet and Workbook
• What are Columns, Row, Cells, and Ranges
• The Excel Interface
• Navigation Options

Topic B – Creating a New Blank Workbook

• The Ribbon
• The Backstage View
• The Save and Save As Commands

Module 2 – Formatting a Worksheet

Topic A – Formatting Fonts

• Fonts
• The Font Group
• The Format Cells Dialog Box
• The Format Painter
• Live Preview and Formatting
• The Mini Toolbar
• The Clear Button

Topic B – Add orders and Colours to Cells

• Border Options
• Fill Options
• Sheet background

Topic C – Apply Number Formats

• Number Formats
• Dragging and Dropping Cells
• How to Cut, Copy and Paste cells
• How to Cut, Copy and Paste Multiple Cells
• Using the Clipboard
• Using Paste Special
• Number Formats in Excel
• Custom Number Format

Topic D – Align Cell Contents

• Alignment Options
• The Indent Commands
• The Wrap Text Command
• Orientation Options
• The Merge & Center Options

Topic E – Apply Cell Styles

• Cell Styles
• The Style Dialog Box

Module 3 – Modifying a Worksheet

Topic A – Manipulate Data

• The Undo and Redo Commands
• The Auto Fill Feature
• Auto Fill Options
• The Transpose Option
• Live Preview

Topic B – Insert, Manipulate and Delete Cells, Columns and Rows

• The Insert and Delete Options
• Column Width and Row Height Alternation Methods
• The Hide and Unhide Options

Topic C – Search for and Replace Data

• The Find Command
• The Replace Command
• The GO to Command

Topic D – Spell Check a Worksheet

• The Spelling Dialog Box

Module 4 – Performing Calculations

Topic A – Create Formulas in a Wroksheet

• Excel Formulas
• The Formula Bar
• Elements of an Excel Formula
• Common Mathematical Operators
• The Order of Operations
• Division Formula

Topic B – Insert Functions in a Worksheet

• Functions
• The Functions Library
• Common Functions in Excel (SUM, AVERAGE, COUNT, MIN & MAX)

Module 5 – Managing Large Workbooks

Topic A – Format Worksheet Tabs

• Renaming Worksheet Tabs
• Changing Tab Colour

Topic B – Manage Worksheet

• Repositioning Worksheet
• Inserting or Deleting Worksheets
• Hiding and Unhiding Worksheets
• Worksheet References in Formulas

Topic C – Manage the View of Worksheets and Workbooks

• Split Command
• Freeze Panes Options
• Arrange All Command
• Arrange Windows Dialog Box
• View Side by Side Command
• Switch Windows Command
• New Wndow Command

Module 6 – Printing Workbook Contents

Topic A – Define the Basic Page Layout for a Workbook

• The Print Options in Backstage View
• The Page Setup Dialog Box
• The Print Preview Options
• Headers and Footers
• Header and Footer Settings
• Page Margins
• Margins Tab Options
• Page Orientation

Topic B – Refine the Page Layout and Apply Print Options

• Zoom Options
• Page Breaks
• Page Break Options
• The Print Area
• Print Titles
• Scaling Options

MICROSOFT EXCEL – INTERMEDIATE

Duration: 1 Day

AUDIENCE

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

PREREQUISITES

REQUIRED PREREQUISITES:

Basic knowledge of Microsoft Excel is essential with the follow pre-requisites:

• Have attended Microsoft Excel – Foundation Level; OR
• Able to switch between task applications
• Able to create a spreadsheet with simple formatting
• Able to create a basic chart
• Able to print a spreadsheet with headers and footers added
• Able to use Auto Filter command
• Able to apply Freeze Pane command
• Able to create basic formulas – Addiition, Subtraction, Multiplication and Division
• Able to use basic functions – AutoSum, Count, Max, Min and Average functions.

METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

COURSE OBJECTIVES

Upon completion of this program, participants should be able to:

• Create simple to complex formulas and functions, like:
  • IF Functions
  • COUNTA & COUNTIFS Function
  • AVERAGEA & AVERAGEIFS Function
  • SUMIF & SUMIFS Function
• Filter data using Auto & Advanced Filters
• Clean Duplicate Records
• Visualizing Data as a Graphic

COURSE OUTLINES

Module 1 – Referencing Calculations

Topic A – Formula Reference

• Relative References
• Absolute References
• Mixed References

Module 2 – Working with Functions Topic A – Data Summary with Function

• COUNTA
• AVERAGEA

Topic B – Conditional Data Summary 1

• COUNTIF
• AVERAGEIF
• SUMIF

Topic C – Conditional Data Extraction

• IFERROR
• IF

Module 3 – Organizing Dynamic Data Range with Tables

Topic A – Create and Modify Tables

• Create Table
• Styles and Quick Styles Sets
• Removing Duplicate Values

Topic B – Sort and Filter Data

• Sorting Data
• Filtering Data
• Advanced Filtering

Topic C – Use SubTotal to Calculate Data

• Total Row with SubTotal Functions

Module 4 – Data Visualization Topic A – Conditional Formatting

• Highlight Cell Rules
• Data Bars
• Icon Sets
• Creating New Rules with Formula

Topic B – Create Charts

• Chart Types
• Chart Insertion Methods
• Resizing and Moving the Chart
• Adding Additional Data
• Switching Between Rows and Columns

MICROSOFT EXCEL – ADVANCED

Duration: 1 Day

AUDIENCE

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

PREREQUISITES

REQUIRED PREREQUISITES:

Basic knowledge of Microsoft Excel is essential with the follow pre-requisites:

• Have attended Microsoft Excel – Foundation & Intermediate Level; OR
• Able to switch between task applications
• Able to create simple to complex formulas and functions.
• Able to validate data in Worksheet
• Able to apply Filter data using Auto and Advanced Filter
• Able to clean Duplicate Records

METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

COURSE OBJECTIVES

Upon completion of this program, participants should be able to:

• Analyze data Using Pivot Tables
• Use VLOOKUP function to extract data
• Create SubTotal using the SubTotal Function
• Combining & Consolidating Data
• Summarizing & Analyzing with Pivot Table and Pivot Charts

COURSE OUTLINES

Module 1 – Organize Summary and Details on Your Data

Topic A – Outlining and Grouping Data

• Using Automatic Outlining
• Displaying and Collapsing Levels
• Grouping Data Manually
• Creating Subtotals

Module 2 – Retrieve, Compare or Compiling Data with Functions

Topic A – Working with Array Formulas

• What are Array Formulas?
• Using Functions within Array Formulas

Topic B – Using the LOOKUP Functions

• Using VLOOKUP to Find Data
• Find Exact Match with VLOOKUP
• Find Closest Match with VLOOKUP
• Find with HLOOKUP

Topic C – Linking, Consolidating and Combining Data

• Linking Workbooks
• Consolidating Workbooks
• Combining Worksheets

Module 3 – Summarizing and Analyzing Data with PivotTable, Slicer and PivotCharts

Topic A – Create a PivotTable

• Create PivotTable Dialog Box
• PivotTable Fields Pane
• Summarize Data in a PivotTable
• Show Values as Functionality of a PivotTable
• Personalize the default PivotTable layout

Topic B – Filter Data by Using Slicer

• Insert Slicers Dialog Box

Topic C – Analyzing Data with PivotChart

• Creating PivotChart
• Editing a PivotChart

Module 4 – Prevent User Changes with Protection

Topic A – Protecting Worksheet and Workbook

• Protecting the Current Sheet
• Protecting an Entire Workbook
• File Protection