EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

Data Preparation & Data Analysis with Microsoft Excel

Course outline

2 days
Discuss this programme →

Duration

2 days

OBJECTIVES

This programme seeks to help you:

  • Learn to model and manage source data into useful and valid Excel data
  • Use MS Excel features creatively and effectively for business reporting, and how to blend in different formula and features
  • See how Excel Table can ease your data analysis and dashboard planning
  • Understand why a valid dates table is vital in data modeling
  • Learn to use PivotTable to summarise reports efficiently and effectively
  • Exercise practical application on real business scenarios and reporting

WHO SHOULD ATTEND

Staff who are required to manage reporting and analysis using MS Excel, from:

  • Human Resource
  • Sales
  • Administration
  • Finance

PROGRAMME OUTLINE

Toolkit Package 1: Data Preparation from the Downloaded Source

  • Useful Short Cut keys
  • Practical Data Cleaning functions to edit the downloaded list effectively
  • Useful date formula
  • Applying the skills learned on multiple data scenarios

Toolkit Package 2: Advanced Filtering and Data Base Formula

  • Explore an hidden gem in your MS Excel table blend with database formula to filter the data in different dimensions
  • Case study: Summary HR data

Toolkit Package 3: Real Life Model: Manage Messy Excel List

  • Understand IF formula and its practical usage in data analysis and even in editing the list
  • Challenge 1: Sort out the Messy List

Toolkit Package 4: Powerful Excel Table

  • Quick in creating the table
  • Explore benefits in table than normal range
  • Exercise: Dashboard layout in data modelling

Toolkit Package 5: Powerful Baskets of Formula You Should Never Miss

  • Basket list to peek into:
  • Offset: a must in dynamic range, what else can it be used for?
  • Index Match: 2D lookup that works beyond the Vlookup or Hlookup on your desktop now
  • Indirect: Amazing in creating 3D lookup in data summary and analysis
  • Bonus learning: how to blend them to the reporting as desired
  • Case study: Analyse sales data in different dimensional tables

Toolkit Package 6: Uncover What You Still Do Not Know About PivotTable

  • Extract, amend, filter and sort group data using PivotTable
  • Integrate the PivotTables using slicers
  • Add new calculated formula in PivotTable

Programme scope, delivery arrangements and suitability are confirmed during enquiry.