Excel Data Analysis Workshop

23-24 Jul, 2018, Kuala Lumpur

FOCUSING ON
  • Using Function & Formula Part 1
  • Naming cell/range
  • Linking data
  • Writing your first IF Statement
  • Using Function & Formula Part 2
  • Using Function & Formula Part 3
  • Applying Conditional Formatting
  • List Management
  • Advanced Charting technique
  • Creating a Combination Chart
OVERVIEW

We can help you understand Excel function easier and guide you how and when to apply them.

 

For the past 10 years, we have trained Directors, General Manager, Sales Managers, Area Sales Manager, HR Manager, Marketing Manager, Digital Marketing Manager, Production Manager, QA Manager, Finance Manager, executives, supervisors to use Microsoft Excel to solve their real business problems for a wide range of functions such as sales and marketing, administration, purchasing, Human Resource (HR), warehouse, R & D etc.

 

For example, using two specific Excel’s tools we can help a digital marketing consultant to highlight duplicate entries of Leads and remove it within less than 1 minute. Besides, using Excel Advanced functions you will know what item is going to expire. Lots of useful and practical example is given to help you understand Excel useful advanced function and formula fast.


OBJECTIVES
This course is best for Excel users who want to get more out of Microsoft Excel in their day-to-day work. Working with data faster and efficiently. Learning Excel by doing practical, industry-driven exercises, and applying over 20 Excel shortcut on the spot. The Q&A Session will give you the opportunity to ask your own Excel questions on the spot.

 

WHO SHOULD ATTEND

Participants should understand the basics of Windows. They should already have basic experience working with Microsoft Excel such as using SUM, viewing multiple sheets.

METHODOLOGY
  • Partner sharing
  • Class sharing
  • Games
  • Competition
  • Team-based exercise
  • Individual hand-on exercise
  • Practical computer-based exercise
  • Pre and Post assessment


WHAT YOUR COLLEAGUES ARE SAYING ABOUT THE COURSE….
  • “My efficiency level skyrocketed! From hours of repetitive works now turns into seconds of wonders!”
    Mr. Lim, Assistant Manager, RHB investment Bank Sdn Bhd
  • “I have learned how to operate excel easier in day to day work. For example, the excel shortcuts and vlookup functions are very useful to simplify our work.” Besides that, Course Leader, Mr Kent Lau is very helpful in ensuring we understand and apply the new knowledge learnt.”
    Kah Yen, Supply Planner
  • “The training was lively , fun , and easy to understand with the help of the Course Leader and good examples were provided.”
    Masfirah, Prudential BSN


DAY 1
9:00 PRE ASSESSMENT (10 MINUTES)
  USING FUNCTION & FORMULA PART 1
  • Mastering ?
  • Have you been upgrading your existing skills?
    The concept of Relative reference and Absolute reference
10.00 NAMING CELL/RANGE
  • Learn to name a cell
  • Name a range of cell
  • How and when to name a cell/range of cell
  • How to remove a named range
  • Using Named Cell in Function
10.45 LINKING DATA
  • Why we use Hyperlink?
  • Create Hyperlink
  • Edit Hyperlink
  • Delete Hyperlink
  • Linked Cell
12.00 WRITING YOUR FIRST IF STATEMENT
  • Checking condition with numeric value
  • Checking condition with text value
  • Apply different calculation on different business requirement
1:00 Lunch

2:00

USING FUNCTION & FORMULA PART 2

  • Calculating age using DATE related function
    TODAY(), NOW(), DAY, MONTH, YEAR, HOUR, MINUTE, SECOND

  • How to extract text value using TEXT FUNCTIONS such as LEFT. RIGHT. MID. TRIM. CONCATENATE.

  • Revisit of SUM, COUNT, COUNTA
    Introduction to SUMIF
    On same worksheet
    On different worksheet

    Introduction to COUNTIF
    On same worksheet
    On different worksheet
  • Q & A Session – for participants to address their challenges.
5:00 End of Day 1
DAY 2
9:00 USING FUNCTION & FORMULA PART 3
  • Most widely use LOOKUP function
  • VLOOKUP
  • HLOOKUP
  • MATCH
    #N/A
  • How to combine VLOOKUP and MATCH function to speed up data analysis
  • Automate task of lookup data faster
  • On same sheet
  • On different sheet
10.45 APPLYING CONDITIONAL FORMATTING
  • Formatting with one condition
  • Editing Conditional Formatting
  • Removing Conditional Formatting
  • Essential skill to highlight items that match certain business requirement
  • Conditional Formatting using Icon Set
12.00 LIST MANAGEMENT
  • Using Subtotal
  • How to remove Subtotal
  • Learn how to group multiples categories easily
  • Subtotal vs Pivot Table
1:00 Lunch

2:00

ADVANCED CHARTING TECHNIQUE

  • Revisit of creating a basic chart :
    Car Chart
    Pie Chart
    XY Chart/Scatter Chart
3:45 CREATING A COMBINATION CHART
  • Creating your first dynamic chart using IF Statement
  • Creating a dropdown list
  • Creating a Map Chart based on any map you choose.
  • The new tool Excel provided.
  • When & why not to use VLOOKUP
  • Q & A Sessions – for participants to address their challenges.
  • Post Assessment (10 minutes)
  • Bonus:
    Learn to use more than 20 Excel shortcuts
    Entering a Date and time fast
    Copy worksheets faster
    Saving a Workbook faster
    Using QAT toolbar
5:00 End of Course

 

LEARN FROM THE BEST

Mr. Kent Lau Chee Yong has over 10 years of training experience for Malaysian’ corporate and MNC focusing on improving productivity using Excel. He is not only capable of conducting training effectively but also developing Excel-based tool for corporate using Excel Macro/VBA.

 

Just by writing a few lines of VBA code, he helped an audit executive to reduce his work from 3 days to few minutes. In another case, he wrote VBA codes to help a business analyst to reduce her monthly reporting task from half day to few minutes.

 

He has helped many Executives reducing their time spent to analyze and produce reports.

 

He has over 300 corporate clients such as LB Aluminium Berhad, SGS Malaysia Sdn Bhd, Tunku Abdul Rahman University College, Shell Malaysia, SapuraKencana Petroleum Berhad and etc.

 

Whenever he discover a new application of Excel, new combinations of Excel function to achieve an amazing time-saving result, a new way to solve a reporting problem, he will document it and share as a case studies with his participants.

 

By the end of his course, his participants will be able to experience a significant increase in their capability to use Excel to create report such as by merging from different worksheet, various workbooks. He enjoys seeing participants becoming the star of their companies.

 

Over 3000 of his participants have experienced the power of Excel like never before. Are you the next?
Sample of works:
http://www.ugrowit.net/excel-dynamic-chart-and-dashboard-kent-lau-chee-yong/
http://www.ugrowit.net/extracting-date-of-birth-dob-from-malaysia-identity-card-ic-number/
https://www.youtube.com/watch?v=F_NohX4gz3A
https://www.youtube.com/watch?v=u2MbDmxctuQ&t=93s
https://www.youtube.com/watch?v=fZHttdO5cPk

 

VENUE DETAILS

Vistana Kuala Lumpur
9 Jalan Lumut Off Jalan Ipoh
50400 Kuala Lumpur

Phone :03 4042 8000

 

IPA TRAINING IS REGISTERED WITH

 

CONTACT US

Juliany, 
O - 03 2283 6109,
M - 019 3134436
juliany@ipa.com.my 

Michelle, 
O - 03 2283 6100, 
M - 019 363 7822
michelle@ipa.com.my 

For Customised In-House Training
Yvonne,
O - 03 2283 6101, 
M - 012 2011247,

F - 03 2283 6108
 
yvonne@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