- 21 May 2021 (Fri) 10:00 AM - 5:00 PM
- 26 May 2021 (Wed) - 2 Jun 2021 (Wed) 7:00 PM - 10:00 PM
- 16 Jun 2021 (Wed) 10:00 AM - 5:04 PM
(Course no. AEX10512 : Enrolling this course on or before 28th April can receive early bird discount of $100 (HK$1,380))
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
- 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
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
- One-Input table
- Two-Input table
- Application of exact match and approximate match
- Creating an order form using vlookup function
- Files protection
- Protecting cells/documents
- Unprotecting documents
- 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.
- Highlighting data using cell colours, font colours
- Highlighting data using icons
- 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
- 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