Computer Academy

ADVANCED EXCEL COURSE

Enquire Now

Course Information

Schedules
  • 29 Apr 2021 (Thu) 10:00 AM - 5:00 PM
  • 3 Jun 2021 (Thu) 10:00 AM - 5:00 PM
  • 9 Jul 2021 (Fri) 10:00 AM - 5:00 PM
Registration period
15 Apr 2021 (Thu) - 18 Jun 2021 (Fri)
Price
HKD 1,680
Course Level
Study Mode
Duration
6 Hour(s)
Language
English
Location
Room 603 Dominion Centre, 43-59 Queen's Road East Wanchai, Hong Kong
20 views

Course Overview

The advanced Excel training course targets intermediate Excel users they want to use Excel to analyze , calculate , manage data and prepare management reports.

The course covers a wide range of spreadsheet features including advanced search, consolidating multiple worksheets, database functions, lookup functions, what-if analysis, scenario analysis and data protection. With all these features, students can streamline the repetitive workflow and save a lot of time in their work.

The course is supplemented with lots of real world examples and the adoption of small class size teaching approach can further enhance the overall learning experience of our students.

 

What you'll learn

What you’ll learn
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

Database

  • 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 documents with workbooks

  • 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
  • Creating a menu system using hyperlink

Creating and using Macros



We use cookies to enhance your experience on our website. Please read and confirm your agreement to our Privacy Policy and Terms and Conditions before continue to browse our website.

Read and Agreed