Certificate course in Microsoft Excel

Certificate Excel Training Course

Excel is a powerful tool used in most companies. Managements can formulate strategies and decisions on data after being processed and analyzed with Excel. It explains why most companies would like to recruit candidates with good knowledge in Excel. The course is packed with two hottest courses, Excel and Advanced Excel, that can match today's business needs.

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
 
CEX7091 - 廣東話 11 Sep enrol
 
CEX7101 - 廣東話 09 Oct enrol
 
CEX7111 - 廣東話 01 Nov enrol
 
CEX7121 - 廣東話 20 Dec 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