Certificate course in Microsoft Excel

Certificate Excel Training Course

The Certificate Course in Microsoft Excel is a comprehensive program that equips individuals with the skills needed to succeed in today's data-driven world. Participants will gain proficiency in essential Excel functions like lookup and what-if analysis, as well as advanced features such as advanced filtering, macro automation, and data processing. Through hands-on exercises and real-world examples, participants will learn to apply these techniques to solve business problems effectively.

This course is ideal for everyone seeking to enhance their analytical capabilities or aspiring data analysts looking to kickstart their careers. Upon completion of the Certificate Course in Excel, participants will have the skills and confidence to become Excel power users.

Certificate Excel Training Course

What you’ll learn

Excel Module

Understanding Excel's elements

  • Workbooks, worksheets, cells, formula bar etc.

Editing techniques

  • Cells selection
  • Data entry
  • To insert a row
  • To delete a row
  • Undo
  • copying a range of cells
  • Moving a range of cells
  • Using paste special
  • Clearing cell contents

Writing formulas

  • Constructing formulas
  • Using operators (+,-,x, ¸,etc...)
  • Using references
  • Tips on entering formulas

File operations

  • Saving workbooks
  • Save as...
  • Save
  • Closing workbooks
  • Deleting workbooks

Printing worksheets

  • Using print preview
  • Page setup
  • Header and footer settings
  • Margin settings
  • Turning the gridlines on or off
  • Turn the row/column heading on or off
  • Auto scaling printout
  • Setting manual page breaks
  • Setting row or column titles
  • Setting print area

CEX40756 - Eng 24 Jul enrol
CEX4085 - 廣東話 04 Aug enrol
CEX4086 - Eng 05 Aug enrol
CEX4087 - 廣東話 23 Aug enrol
CEX4084 - 廣東話 23 Aug enrol
  Excel VBA X ChatGPT/GPT
  Excel Dashboards and Reports
  Excel-formulas & functions
  Financial Accounting with Excel
  Mastering Excel PivotTables and PivotCharts + Advanced Excel

Useful functions

  • SUM( )
  • AVERAGE( )
  • MAX( )
  • MIN( )
  • IF()
  • Count()

Tips in editing a worksheets

  • Paste insert row/column
  • Drag and drop
  • Autofill
  • Fill down
  • Fill right

Understanding address notations

  • Relative address
  • Absolute address

Polishing worksheets

  • Adding border
  • Adjusting row heights and column widths manually
  • Adjusting row heights and column widths automatically
  • Setting text alignments ( horizontal and vertical )
  • Setting text orientations
  • Shading cell background
  • Font settings

Number format

  • Adding thousand separator
  • Adding currency sign number
  • Adding decimal points
  • Creating a master spreadsheet
  • Creating document templates
  • Using document templates

Exchange files with other applications

  • Understanding various Excel file formats
  • Saving workbook for backward compatibility
  • Exporting excel workbook for other software


  • Creating styles
  • Using styles
  • Editing styles
  • Merging styles from other workbooks

Managing Records

  • Sorting Records
  • Filtering records


  • Inserting shapes
  • Drawing lines, rectangles, ovals etc.


  • Inserting various kinds of chart – column, line, bar and pie chart
  • Adding and modifying chart’s elements
  • Chart title, Legend, Axis, Grids, Data table, Data label
  • Polishing charts
  • Adding special effects to chart elements
  • Filling charts with gradients or pictures

Using Layout tab

  • 3D Chart
  • Constructing 3D charts
  • Formatting 3D charts
  • Changing 3D view

Advanced Excel Module

Naming cells and ranges

  • Creating and defining names
  • Making a name list
  • Advanced technique of using names in formulas
  • Using Name Manager
  • Navigating spreadsheet with names


  • The database components
  • Using Excel Form feature
  • Inputting data
  • Deleting data
  • Finding records
  • Using menu commands to find records

Advanced data sorting and subtotal

  • Multi-level sorting
  • Restoring data to original order after performing sorting
  • Sort by icons
  • Sort by colours
  • Multi-level subtotal

Using database functions

  • DSum()
  • DMax()
  • DMin()
  • DAverage()
  • Dcount()

Managing multiple worksheets

  • Arrange All
  • New Window

Consolidation with several worksheets

  • Consolidating and combining several spreadsheets using the operation addition, subtraction
  • Synchronizing the consolidated tables with the source data

Data table

  • One-Input table
  • Two-Input table

Lookup table

  • Lookup()
  • Vlookup()
  • Hlookup()
  • Application of exact match and approximate match
  • Creating an order form using vlookup function

Document protection

  • Files protection
  • Protecting cells/documents
  • Unprotecting documents

File linking

  • Paste link

Filter and advanced filter

  • Defining single and multiple criteria
  • Combining search criteria
  • Deleting criteria
  • Extracting records

Building A Pivottable

  • Prepare Your Worksheet Data.
  • Create a Table for a PivotTable Report.
  • Build a PivotTable from an Excel Range.

Manipulating Your Pivottable

  • Turn the PivotTable Field List On and Off.
  • Customize the PivotTable Field List.
  • Remove a PivotTable Field.
  • Refresh PivotTable Data.
  • Add Multiple Fields to the Row or Column Area.
  • Add Multiple Fields to the Data Area.
  • Add Multiple Fields to the Page Area.
  • Delete a PivotTable.

Conditional format

  • Highlighting data using cell colours, font colours
  • Highlighting data using icons

Data validation

  • Define the data input type
  • Define the warning message
  • Define the error message
  • Circle invalid data
  • Creating a pull down box to facilitate the data entry process

What-If Analysis

  • Using Scenario Manager
  • Defining your own scenario
  • Preview the result of scenario
  • Editing a scenario
  • Using Goal Seek
  • Using Goal Seek to solve problems

Inserting a hyperlink to a workbook

  • Creating a hyperlink
  • Editing a hyperlink
  • Building a menu system with hyperlinks

Creating and using Macros