Data Analysing & Reporting

27-28 Nov, 2023, Melia Kuala Lumpur

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

Theresa Morgis

Microsoft Application Trainer

 

Theresa Morgis is certified Microsoft Trainer and a certified HRDF trainer. She has more than 20 years of training experience in Microsoft products. Theresa is well versed in Microsoft Application products: MS Excel, Word, and PowerPoint (Basic to Advance Level).

 

Her years of experience in this field has also encouraged her to develop and customize her own training materials and she occasionally customise specific course manuals upon requests.

 

Theresa has conducted training for CEO’s, vice presidents, directors, accountants, managers, secretaries, production operators and clerical staffs.

 

Her Clients include Shell Malaysia, ASTRO, Bolton Bhd, SMBC Bank, Pos Malaysia, TNB, Petronas, Honda, Ingram Micro, Maybank Bhd, Bank Simpanan Nasional, Telekom Malaysia, Nippon Oil, RHB Bank, Public Bank Bhd, CIMB, Hilton Hotel, Touch N Go, PLUS, Ansat Broadcast, Sunway University, Monash University, UNITAR, Ambank, Zuellig Pharma, HERO and more

Venue Details

Melia Kuala Lumpur
16 Jalan Imbi,
Phone : 0 3 2785 2828

https://www.melia.com/en/hotels /malaysia/kuala-lumpur/melia-kuala-lumpur

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

FOCUSING ON
  • MODULE 1: CREATE & MODIFY TABLES
  • MODULE 2: FUNCTIONS & FORMULAS
  • MODULE 3: LOOKUP FUNCTIONS
  • MODULE 4: LINKING & CONSOLIDATION
  • MODULE 5: NAME RANGE
  • MODULE 6: DATA VISUALISATION
  • MODULE 7: WHAT-IF ANALYSIS
  • MODULE 8: PIVOT TABLE & PIVOT CHARTS
  • MODULE 9: INTRODUCTION TO POWER QUERY (2016 AND ABOVE)
  • MODULE 10: MANAGE & PROTECT FILE/SHEET
  • MODULE 11: SPARKLINE
  • MODULE 12: RECORDING AND RUNNING MACRO
INTRODUCTION

This advanced Excel training course is designed specifically for spreadsheet users who are
already proficient and looking to take their skills to an advanced level.

Participants will use advanced techniques necessary to generate report using pivot tables, Advance Functions, analyze worksheet data and utilize data tools.

AFTER ATTENDING THIS COURSE YOU WILL RETURN TO YOUR JOB…
  1. Converting and cleaning data to build impressive presentation in Excel.
  2. Summarizing large data into dynamic reports using Query in Excel
  3. Generating interactive reports using Pivot & Slicers
  4. Being able to learn on data visualising techniques that will provide impactful and simplified summary of information.
  5. Knowing about dynamic formulas to present your data.
WHO SHOULD ATTEND
Personnel who use Microsoft Excel to analyse and prepare management reports, especially Administrative, Operational and Middle Management Staff. To ensure success, each student is expected to have above average skills with Microsoft Excel.
METHODOLOGY
This training is Instructor Led Training – interactive lectures which includes discussions and
practical exercises
DAY 1
9:00

MODULE 1: CREATE & MODIFY TABLES

  • Format Tables
  • Automatic calculation in tables
  • Sort or Filter Worksheet or Table Data
  • Advance filtering
  • Use Functions to Calculate Data in a Table or Worksheet 


 

MODULE 2: FUNCTIONS & FORMULAS

  • Sumif, Countif, Sumifs, Countifs
  • Maxifs & Miniifs (Office 365 only)
  • Using AND & OR conditions
  • Logical If & Nested If
  • Ifs (2019 and above only)

 

10.45

MODULE 3: : LOOKUP FUNCTIONS

  • Vlookup
  • Hlookup
  • Vlookup Vs Index & Match
  • Xlookup (only for 2019 and above)

 

1:00 Lunch
2:00

MODULE 4: : LINKING & CONSOLIDATION

  • Consolidating Data within Worksheet
  • Consolidating with external workbook
  • Managing Data Link files
  • 3D formula 

 

 

MODULE 5: NAME RANGE

  • Understanding Named Ranges
  • Name Range Rules
  • Creating Named Ranges
  • Using Named Ranges in Formulas

 

3:45

MODULE 6: DATA VISUALISATION

  • Sparklines with Quick Analysis
  • Conditional Formatting
  • Data Validation

 

5:00 End of Day 1
DAY 2
9:00

MODULE 7: WHAT-IF ANALYSIS

  • Goal Seek
  • Data Tables
  • Scenarios


 

MODULE 8: PIVOT TABLE & PIVOT CHARTS

  • Creating Pivot Tables
  • Formatting Pivot Tables
  • Calculation in Pivot Table
  • Pivot Chart
  • Slicers & Timeline

 

10.45

MODULE 9: INTRODUCTION TO POWER QUERY (2016 AND ABOVE)

  • Getting Data from other Excel files
  • Getting data from CSV Files
  • Getting Data from Text files & other sources
  • Manage the query file

 

1:00 Lunch
2:00

MODULE 10: MANAGE & PROTECT FILE/ SHEET

  • Worksheet Protection
  • Workbook Worksheet
  • Range Protection
  • File Protection

 

 

MODULE 11: SPARKLINE

  • Overview of Sparklines
  • Column Sparklines
  • Line Sparklines
  • Win/Loss Sparkline

 

3:45

MODULE 12: RECORDING AND RUNNING MACRO

  • Activate Developer
  • Record Macro
  • Assign Macro to shortcut key/buttons
  • Save Macros

 

5:00 End of Course