Certificate course in Microsoft Excel

Certificate Excel Training Course

Microsoft Excel是現今企業使用得最多及最廣泛之軟件。企業均要求員工及應徵者其Excel之知識均要達一定水平,部份公司更在面試時對求職者作即場測試。本中心為迎合職場上之要求,故特開辦Excel証書課程,將兩個備受企業歡迎之課程編成一起,令學員能以較優惠價餞去裝備自己。

Certificate Excel Training Course

What you’ll learn

Excel Module

Understanding Excel's elements

  • Workbooks, worksheets, cells, formula bar etc.

Editing techniques

  • Cells selection
  • Data entry
  • To insert a row
  • To delete a row
  • Undo
  • copying a range of cells
  • Moving a range of cells
  • Using paste special
  • Clearing cell contents

Writing formulas

  • Constructing formulas
  • Using operators (+,-,x, ¸,etc...)
  • Using references
  • Tips on entering formulas

File operations

  • Saving workbooks
  • Save as...
  • Save
  • Closing workbooks
  • Deleting workbooks

Printing worksheets

  • Using print preview
  • Page setup
  • Header and footer settings
  • Margin settings
  • Turning the gridlines on or off
  • Turn the row/column heading on or off
  • Auto scaling printout
  • Setting manual page breaks
  • Setting row or column titles
  • Setting print area

SCHEDULES
 
CEX7122 - 廣東話 18 Dec enrol
 
CEX7121 - 廣東話 20 Dec enrol
 
CEX8011 - 廣東話 16 Jan enrol
RELATING COURSES
  Excel VBA
  Excel Dashboards and Reports
  Excel-formulas & functions
  Financial Accounting with Excel
  Mastering Excel PivotTables and PivotCharts + Advanced Excel

Useful functions

  • SUM( )
  • AVERAGE( )
  • MAX( )
  • MIN( )
  • IF()
  • Count()

Tips in editing a worksheets

  • Paste insert row/column
  • Drag and drop
  • Autofill
  • Fill down
  • Fill right

Understanding address notations

  • Relative address
  • Absolute address

Polishing worksheets

  • Adding border
  • Adjusting row heights and column widths manually
  • Adjusting row heights and column widths automatically
  • Setting text alignments ( horizontal and vertical )
  • Setting text orientations
  • Shading cell background
  • Font settings

Number format

  • Adding thousand separator
  • Adding currency sign number
  • Adding decimal points
  • Creating a master spreadsheet
  • Creating document templates
  • Using document templates

Exchange files with other applications

  • Understanding various Excel file formats
  • Saving workbook for backward compatibility
  • Exporting excel workbook for other software

Styles

  • Creating styles
  • Using styles
  • Editing styles
  • Merging styles from other workbooks

Managing Records

  • Sorting Records
  • Filtering records

Drawing

  • Inserting shapes
  • Drawing lines, rectangles, ovals etc.

Charting

  • Inserting various kinds of chart – column, line, bar and pie chart
  • Adding and modifying chart’s elements
  • Chart title, Legend, Axis, Grids, Data table, Data label
  • Polishing charts
  • Adding special effects to chart elements
  • Filling charts with gradients or pictures

Using Layout tab

  • 3D Chart
  • Constructing 3D charts
  • Formatting 3D charts
  • Changing 3D view

Advanced Excel Module

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 multiple worksheets

  • 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
  • Building a menu system with hyperlinks

Creating and using Macros