New user? Register as member

Hidden Secrets of Data Analysis in Excel

In this new digital economy, we are experiencing a data explosion. It is no longer enough to know some simple Excel like auto-filter and sort to get your job done. It is now a pre-requisite to know how to effectively and efficiently analyze data. In this course, you will learn all the functions and formulas to summarize your raw data into useful reports.

Objective

At the end of this course, you will be equipped with the skills to

• Process raw data using Excel formulas and functions
• Prepare and analyze professional-looking reports
• Set up reports and charts that are easy to maintain

Pre-requisite

• Participants must have experience in using
• Simple worksheets functions
• Simple Pivot Table
• Works with multiple worksheets of data

Outline

Module 1 – From manual calculation to automation

Module 2 – Introduction to Pivot Table, the solution for instant analysis and reporting

Module 3 – Merging 2 data sources into one for Pivot Table using VLOOKUP

Module 4 – Grouping data to find patterns

Module 5 – Extract, clean and beautify descriptions for reporting using TEXT functions

Module 6– Master Dates and cut down 50% of your Excel reporting time

Module 7 – Create Pivot Charts and expand your choices of charts for your presentation and dashboard

Module 8 – Selecting cells with precision and speed

Module 9 – Intermediate Pivot Table reports

Module 10 – Create Pivot Table using multiple sources

Topics covered in the course

Data Processing

√ MONTH, YEAR, DAY, DATE, DATEDIF formula,
√ Absolute and relative reference
√ IF and NESTED IF
√ GOTO function
√ TEXT functions
√ VLOOKUP formula

Analysis and Reporting

√ PIVOT TABLE
√ Tables
√ Multiple Data Sources Pivot Table
√ Presenting with Pivot Charts

Special Bonus

The trainer provides free consultation during the course so that participants can get a head start and apply what they learned immediately to their jobs. Participants need to provide a copy of their sanitized raw data for this.

Who should attend

Managers and Executives who have to manage, analyse and prepare reports using raw data downloaded from systems. They must have some basic to intermediate knowledge of Excel i.e. navigate around a worksheet, format cells, auto-filter, sort, create a simple template and enter simple formulas (e.g. SUM) into the worksheet.

Methodology

Real-life excel problems are used in case studies. An interactive method of teaching

2 days hands-on practice course
Face to Face workshop

Testimonials

"I had learned advanced techniques on how to extract data in a more efficient way using Pivot table, knew quite a lot of features and functionality of Pivot table. In the past, I just use a few commands like vlookup, Mid, left, and right to analyse data. After this course, I had benefited with additional commands and also learn how to tackle exported raw data from SAP where the date is in text format. Thanks!"
Yap Seow Kian - Manager - BSIC - BPMO, Defence Science Technology Agency

I have always been clueless about the power of Excel, doing many things the manual way like counting amounts in cells and doing manual layouts. To me Excel has always been just another program I lay out my reports with. But after attending this Excel course, I realised that there is so much more power stored in Excel to help enhance and make efficient daily work reports that I have to do especially with ensuring accurate details in figures in big amounts. I'm sure as I continue to explore what has been taught, I will be able to save a lot more time and stop pulling my hair out while I face my reports again.
Rachelle Low (Medical), Johnson & Johnson Pte Ltd

This course is extremely useful for intermediate users of Excel. Through this course, I learned to use MS Query and Pivot tables for consolidating a huge amount of crucial data. It will definitely save me a lot of precious time to focus on other job roles, I would recommend it to anyone who would like to save time and still be able to be productive when processing and analyzing data.
Melanie Low (Sales and Marketing), 3M Asia Pacific

Profile of Jason Khoo

Jason Khoo is a Microsoft Certified Trainer (MCT), ACTA certified trainer of 20 years. He is a Microsoft Certified Power BI Data Analyst Associate and a data analytics practitioner of 30 years. During his time of employment, he has worked in Tibs (currently SMRT), MobileOne Ltd, 3M and Virgin Mobile.

During his tenure, he has worked with multiple databases, extracted and downloaded information from systems and run numerous analysis. These analyses spanned across many departments such as Finance, Sales, Marketing, Human Resources, Payroll, etc.

After his employment, he worked as a data analytics consultant for many companies, including Discovery Asia, 3M, Tanah Merah Country Club, Johnson and Johnson Vision Care, National Environmental Agency, Timberland, etc.
His data analytics skills combined with Excel allowed him to deliver many reports to the clients without them having to incur software cost. These reports include Dashboards, Top 10 charts, Risk Analysis, Business Models, KPIs, etc.

As a trainer, he is passionate about transferring his knowledge and imparting his skills to his participants. To day, he has trained thousands in his face to face workshops and had conducted talks for thousands as well.

Trans-Island Bus Services Ltd (Tibs) –Operations and Revenue Analytics, Passengers Travelling Behaviour Analytics
• Daily tracking of revenue,
• Prepare KPI for LTA,
• Fare revision analysis

Mobile One Asia Ltd (M1) – Financial analytics, Customer Call Usage Analytics
• Full company budgeting and business planning,
• Monthly Analysis,
• KPI reporting

3M – Sales, Cost and Inventory analytics
• Daily and Monthly Sales Forecasting,
• Analysis of Marketing Budget,
• Analysis of Inventory and shipments.
• Cost Analysis for new products (BOM analysis)

Virgin Mobile – Retail, Marketing, Payroll, Admin, IT, Finance Analytics
• Daily KPI reporting,
• Monthly Analysis for Finance, Payroll,
• Developed Fully Automated Business Model driven by KPI