Advanced Excel training course outline by Computer Academy

The course targets intermediate Excel users who are using Excel to calculate, manage data or prepare management reports. The course covers a wide range of data manipulating features and spreadsheet features which are not only useful in complex calculations but also can streamline the repetitive tasks.

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

RELATING COURSES
  Excel-formulas & functions

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