EXCEL Intermediate - Techniques To Manage-Manipulate-Analyse Complex Data

25-26 Apr, 2018, VISTANA HOTEL KUALA LUMPUR

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

KEN CHOO is a US trained engineer with more than 15 years experience in managing business, consultancy and training. He has been an advanced user of many application software and has over 15 years of practical experience in Microsoft applications. He has developed many Excel models for various applications such as  production planning, sales management, vendors management for manufacturing and service industries.

He has strong track record in managing performance and improvement by using Excel as a monitoring and analysis tool. Ken is also experienced in implementing management system such as ISO 22K Information Security System and he is also a PSMB registered trainer.

Venue Details

VISTANA HOTEL KUALA LUMPUR
9, Jalan Lumut, Off Jalan Ipoh, 50400 Kuala Lumpur - 50400
Phone : (603) 4042 8000

Click For Hotel Location

Contact us

Juliany,
03 2283 6109
juliany@ipa.com.my

Phoebe,
03 2283 6100
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Jane,
03 2283 6101
Jane@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my


  • This course aims to take you along the path to mastering EXCEL and importantly to see how using the program can improve your efficiency at work. We show how the program works using real work examples.
  • This Excel intermediate course will build on your basic Excel skills and show you more complex formulae, how to build formulae across worksheets, linking data and charting your data. We also cover protecting your sheets and workbooks.
COURSE OBJECTIVES: You will be able to:
  • Use Logical Functions, Financial and Statistical Functions
  • Nest Functions
  • Multiple Worksheet Management and Formulas
  • Create Charts
  • Protect Worksheets & Workbook

     
METHODOLOGY
  • Guided Learning, group-paced, hands-on classroom training with real life examples for business applications
  • Challenging exercises are set based on practical applications for typical organizational functions such as HR, Admin, Production, Sales etc.
  • Useful class manual are provided for each participant.
DAY 1
9:00 RECAP BASICS
  • Excel Interface, Entering data, Formatting
  RECAP LINKING
  • Inserting and deleting sheets
  • Changing the default number of workbook sheets
  NAMING CELL RANGES
  • Concept And Purpose
  • Naming Individual Cells
  • Naming Range Of Cells
  • Deleting And Amending Named Ranges
  • Using Name Ranges in Formulae/Functions
  • Named Ranges As Navigation Aid
  • Manipulating text and numbers
  Cont. LINKING SHEETS AND FILES
  • Viewing Different Files On One Screen
  • Saving a workspace
  • Window Split
  • Freeze Panes
  • Viewing Different Parts Of The Sheet On One Screen
  • Watch Window
1:00 Lunch

2:00

USING FORMULAS & FUNCTIONS

  • Changing the default number of workbook sheets
  • Linking sheets in the same file
  • Relative /Absolute Cell Reference
  • Using Text/number Functions
  • Understanding Arrays
  • Calculating using Date Function Excel Functions
  • Benefits And Purpose
  • IF Statements
  • Nested If
  • SUMIF And SUMIFS
  • AVERAGEIF And AVERAGEIFS
  • COUNTIF and COUNTIFS
  • ISERROR And IFERROR
  • Nesting IF WITH AND, OR
5:00 End of Day 1
DAY 2
9:00 EXCEL FUNCTIONS RECAP
 
  BASIC LOOKUP FUNCTIONS
  • Vertical Lookup (Vlookup)
  • Horizontal Lookup (Hlookup)
  APPLYING AND MANAGING CONDITIONAL FORMATTING
  • Data Bars
  • Colour Scales
  • Icon Sets
  • Top/Bottom
  ESSENTIAL LIST MANAGEMENT
  • Sorting Data (By Values, By Cell Colour, By Font Colour, By Cell Icons)
  • Multi Column Sort
  • Filter (By Values, By Cell Colour, By Font Colour, By Cell Icons)
  • Multi Column Filter
  • Advanced Filter
  • Adding Subtotals
  • Group and Outline
  • Data Form
  • Format As Table Feature
  • Data Consolidation
1:00 Lunch

2:00 CHARTS/GRAPHS TECHNIQUES
  • Creating Chart Using Shortcut Keys
  • Saving Custom Chart as Templates
  • Setting Chart as Default
  • Applying Trendlines
  • Formatting and Editing Series, Plot Area, Data Points Chart Area, legends, etc...
  • Using Sparklines (Line, Column, Win/Loss)
5:00 End of Course