Advanced Excel Course

Advanced Excel Course teaches Excel Formulas and Functions and Excel PivotTables

進階 Excel 課程是針對本身巳有中級 Excel 程度的人仕。更可令學員使用 Excel 分析,計算,管理數據和準備管理報告等等。

該課程涵蓋了廣泛的工作表功能,包括進階搜索,合併多個工作表,數據庫功能,查找功能 (VLookup),模擬分析(What-if-analysis),方案分析(Scenario Manager)和數據保護。 利用這些功能,學員可以簡化重複的工作流程並節省大量工作時間。

本課程輔以許多真實的例子,採用小班制教學法可以進一步提高學員的整體學習體驗。

Advanced Excel Course teaches Excel Formulas and Functions and Excel PivotTables

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

SCHEDULES
 
AEX01011 - Eng 22 Oct enrol
 
AEX01117 - 廣東話 10 Nov enrol
 
AEX01112 - 廣東話 17 Nov enrol
 
AEX01114 - Eng 27 Nov enrol
 
AEX01115 - 廣東話 28 Nov enrol
RELATING COURSES
  Access
  Excel VBA
  Excel Dashboards and Reports
  Excel I
  Excel I + Excel Advanced
  Excel-Advanced
  Excel-formulas & functions
  Financial Accounting with Excel
  Mastering Excel Charts and Graphs
  Mastering Excel PivotTables and PivotCharts

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