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
