Programme Syllabus

• Overview of Excel
  
  • Excel as a database
  • Excel as a programming tool
  
  


• Cell References
  • Relative vs. Absolute referencing
  • Named Ranges
  
  


• Using Functions to Lookup and Reference to Database
  • Indirect
  • Hlookup / vlookup
  • Choose
  • Offset
  • Index
  
  


• Text Functions
  • Concatenate
  • Left
  • Right
  • Mid
  
  


• Using Functions to Manage Data
  • Logical functions - if
  • Mathematical functions – sumif
  
  


• Placing controls on a worksheet
  • Creating interactivity using combo box, spinner and scroll bar
  • Making choices using check box or option box on a worksheet
  • Linking a control to worksheet data
  
  


• Pivot Tables
  • Creating interactivity using combo box, spinner and scroll bar
  
  


• Work Example
  Using a comprehensive work example for financial reporting, the application of the various functions and its interrelationship would be illustrated. Participant will also learn how to replace "Hard-Wired" Formulas with "LIVE" Formula