Project Description


Welkin Course Codes: SE0A, SX3A, SX6A

Course Features

This course is specially designed to help you master Microsoft Excel as a business tool to perform a variety of advanced tasks:

  • Advanced Excel functions to consolidate data to meet business criteria
  • Preparing reports using grouping and consolidation techniques
  • Advanced Data presentation using conditional formatting and charts
  • Allowing users to input correct data using validation rules
  • Protecting documents with passwords
Course topics include:
  • Logical Functions
  • Lookup Functions
  • Database Functions
  • Cell Formatting
  • Conditional Formatting
  • Pivot Tables/Pivot Charts
  • Sorting and Filtering Data
  • Tracking and Reviewing Changes
  • Data Validation
  • Auditing
  • Macros
  • Passwords & Security Issues
  • and more…

Learn from the Experts

Get Your Skills

Certify your Excel skills with Microsoft Office Specialist Excel certification. The certification is not a requirement for course completion but we highly recommend you to get your skills professionally recognized.
Remark: The course fee does not include the certification exam.

Further Study

After completing this course you may further develop your knowledge and skills with the course Data Analysis with Microsoft Excel.

Course Fee


Course Duration

6 Hours

Course Prerequisites

Course participants are expected to have foundation skills in Microsoft Excel.

Title of Award

Upon successful completion of the course, students will receive a Certificate of Achievement for the course.

Schedule & Online Enrolment

Sign up for your course by clicking the Enrol button and proceed to Checkout to finish course registration.

Microsoft Excel 2016 for Advanced Users
Course No. 課程編號Info. 語言/系統/地點Start 開課日End 完課日Day(s) 上課日Time 上課時段Duration 課程長度Enrol 報名
SX6A-20111KCantonese / PC / MongKok11/1111/11WED0930-1230;1400-17006 hours / 2 sessions20201111


SX6A-24C24KCantonese / PC / MongKok24/1126/11TUE,THU1900-22006 hours / 2 sessions20201124


SX6A-20121KCantonese / PC / MongKok10/1210/12THU0930-1230;1400-17006 hours / 2 sessions20201210


SX6A-6D12EEnglish / PC / MongKok12/1212/12SAT1000-1300;1400-17006 hours / 2 sessions20201212


SX6A-23C05KCantonese / PC / MongKok05/0107/01TUE,THU1900-22006 hours / 2 sessions20210105


SX6A-45C25KCantonese / PC / MongKok25/0226/02THU,FRI1900-22006 hours / 2 sessions20210225


SX6A-6D27KCantonese / PC / MongKok27/0327/03SAT1000-1300;1400-17006 hours / 2 sessions20210327


SX6A-24C11KCantonese / PC / MongKok11/0513/05TUE,THU1900-22006 hours / 2 sessions20210511


SX6A-23C22EEnglish / PC / MongKok22/0623/06TUE,WED1900-22006 hours / 2 sessions20210622


SX6A-24C03KCantonese / PC / MongKok03/0805/08TUE,THU1900-22006 hours / 2 sessions20210803


SX6A-5C17EEnglish / PC / MongKok17/0924/09FRI1900-22006 hours / 2 sessions20210917


SX6A-2C19KCantonese / PC / MongKok19/1026/10TUE1900-22006 hours / 2 sessions20211019


SX6A-35C17EEnglish / PC / MongKok17/1119/11WED,FRI1900-22006 hours / 2 sessions20211117


SX6A-6D04EEnglish / PC / MongKok04/1204/12SAT1000-1300;1400-17006 hours / 2 sessions20211204


Microsoft Excel 2013 for Advanced Users
Course No. 課程編號Info. 語言/系統/地點Start 開課日End 完課日Day(s) 上課日Time 上課時段Duration 課程長度Enrol 報名
Microsoft Excel 2010 for Advanced Users
Course No. 課程編號Info. 語言/系統/地點Start 開課日End 完課日Day(s) 上課日Time 上課時段Duration 課程長度Enrol 報名
SE0A-20121KCantonese / PC / MongKok18/1218/12FRI0930-1230;1400-17006 hours / 2 sessions20201218


SE0A-20111KCantonese / PC / MongKok24/1124/11TUE0930-1230;1400-17006 hours / 2 sessions20201124


Microsoft Excel for Advanced Users is a 6-hour course focusing on the advanced features of Excel such as data management, data analysis, macro, customization, data import/export, data protection, etc.

You will learn:

  • Advanced Excel functions to consolidate data to meet business criteria
  • Co-authoring documents by enabling document sharing and track changes
  • Searching and analyzing data with advanced criteria
  • Preparing reports by grouping and consolidation techniques
  • Advanced data presentation by using conditional formatting and charts
  • Exchanging data between software by using plain text files
  • Allowing users to input correct data by applying validation rules
  • Protecting documents with passwords
  • An overview of task automation by using Macros
  1. Using Conditional Formatting
    • Format all Cells by using Data Bars
    • Format all Cells by using an Icon Set
    • Format only Cells that contain Text, Number, or Date or Time Values
    • Format only Top or Bottom Ranked Values
    • Format only Values that are Above or Below Average
    • Format only Unique or Duplicate Values
    • Use a Formula to determine which Cells to Format
    • Apply Conditional Formatting by using Quick Analysis
    • Clear Conditional Formats
  2. Creating a Formula by using a Function
    • Creating a Formula by using a Function
    • Logical Function
    • Lookup Function
    • Database Functions
  3. Data Management
    • Analyze your Data instantly
    • Sort Data in an Excel Worksheet
    • Sort Data using a Custom List
    • Filter by using Advanced Criteria
    • Remove a Filter
    • Remove Duplicate Values
    • What is Subtotals
    • Insert Subtotals in a List of Data in a Worksheet
  4. Connect External Data to your Workbook
    • Importing Text Files
    • Using Text to Columns
  5. Working with PivotTables
    • Create a Recommended PivotTable
    • Create a PivotTable manually
    • Change the Data Layout of a PivotTable
    • Sorting Data in a PivotTable
    • Filter data in a PivotTable
    • Group or Ungroup Data in a PivotTable Report
    • Use the Field List to arrange Fields in a PivotTable
    • Update (Refresh) Data in a PivotTable
    • Create a PivotChart
  1. Consolidate Data from Multiple Worksheets in a Single Worksheet
    • Consolidate Data by Position
    • Consolidate Data by Category
    • Use a Formula to Consolidate Data
  2. Using a Shared Workbook to Collaborate
    • Sharing a Workbook
    • Merge Changes
    • Resolve Conflicting Changes in a Shared Workbook
    • Edit a Shared Workbook
    • Remove a User from a Shared Workbook
    • Stop Sharing a Workbook
    • Features a Shared Workbook doesn’t Support
  3. Track changes in a Shared Workbook
    • Overview of Change Tracking
    • Turn on Change Tracking for a Workbook
    • Highlight Changes as you work
    • View Tracked Changes
    • Accept and Reject Changes
    • View the History Worksheet
    • Changes that Excel does not Track or Highlight
    • Turn off Change Tracking for a Workbook
  4. Protecting Workbooks and Worksheets
    • Protect a Worksheet with or without a Password
    • Lock Cells to Protect them
    • Unlock Protected Cells
    • Hide and Protect Formulas
    • Protect a Workbook
    • Remove a Password from a Worksheet or Workbook
    • Change the Password for my Workbook or Worksheet
  5. Create or Delete a Macros
    • Record a Macro
    • Run an Excel Macro
    • Delete a Macro
  1. Applying Conditional Formatting
    • Adding, changing, conditional formats
    • Clearing conditional formats4
    • Apply conditional formatting to quickly analyse data4
  2. Inserting subtotals in a list of data in a worksheet
    • Inserting subtotals
    • Removing subtotals
  3. Filter by using advanced criteria
    • Advanced Filter
    • Comparison operators
    • Advanced Filter – Criteria Examples
  4. Using PivotTables / PivotChart to analyze & present your data
    • Creating a PivotTable or PivotChart
    • Changing the format of your PivotTable report
    • Showing PivotTable details
    • Calculating PivotTable values
    • Changing or updating PivotTable data
  5. Creating a formula by using a function
    • About the syntax of functions
    • Creating a formula by using a function
    • Logical function
    • Lookup function
    • Database functions
  1. Importing Data
    • Importing a text file
    • Connecting external data to your workbook – From Web
  2. Data Validation
    • Overview of data validation
    • Adding data validation to a cell or range
    • Display an optional input message
    • Specify an optional alert or error message when invalid data is entered.
  3. Using a shared workbook to collaborate
    • Sharing a workbook
    • Stop sharing a workbook
    • Track changes in a shared workbook
  4. Password protect worksheet or workbook elements
    • Protecting your with passwords, permission, and other restrictions
    • Protecting worksheet elements
    • Removing protection from a workbook/ worksheet
  5. Macro
    • Recording a macro
    • Running a macro
    • Adding a macro button to the Quick Access Toolbar
    • Deleting a macro
  1. Logical Functions
    • IF
    • AND
    • OR
  2. Lookup Functions
  3. Database Functions
    • DSUM
    • DMIN
    • DMAX
    • DCOUNT
  4. Cell Formatting
    • Applying styles to a range
    • Conditional formatting
  5. Pivot Tables.
    • Creating and using a pivot table
    • Filtering and sorting data within a pivot table
    • Automatically grouping data in a pivot table and renaming groups
    • Manually grouping data in a pivot table and renaming groups
  6. Input Tables
    • One-input data tables
    • Two-input data tables
  1. Importing Text Files
    • What is a delimited text file?
    • Importing a delimited text file
  2. Sorting and Filtering Data.
    • Sorting data by multiple columns at the same time
    • Applying a pre-installed custom sort
    • Creating a customized list and performing a custom sort
    • Using AutoFilter
    • Removing all AutoFilters from a worksheet
    • Advanced Filter Criteria
    • Sub-totalling
    • Removing subtotals
  3. Tracking and Reviewing Changes.
    • Enabling or disabling the ‘track changes’ feature
    • Sharing, comparing and merging worksheets
  4. Validating
    • Data validation – Whole number
    • Data validation – List
    • Data validation – Date
    • Customising a validation input message and error alert
    • Removing data validation
  5. Auditing
    • Tracing precedent cells
    • Tracing dependent cells
    • Showing all formulas in a worksheet, rather than the resulting values
    • Inserting and viewing comments
    • Editing and deleting comments
    • Showing and hiding comments
  6. Macros
    • Macro to change the page set-up
    • Macro to apply a custom number format
    • Macro to format a cell range
    • Macro to insert fields into the header or footer
    • Assigning a macro to a button on the Quick Access toolbar
    • Deleting macros
  7. Passwords & Security Issues
    • Adding ‘open’ password protection to a workbook.
    • Adding ‘modify’ password protection to a workbook.
    • Removing an ‘open’ password from a workbook.
    • Removing a ‘modify’ password from a workbook.
    • Password protecting cells and worksheets.
    • Hiding formulas.
    • Un-hiding formulas.
  1. You can enrol this course by our online system, or call (852) 3605 3322 for reservation.

You may also be interested in